In this article we will discuss some techniques of finding database tables and views that are hidden behind forms in Oracle e-Business Suite, an ERP application from Oracle.

OeBS is a complex environment which consists of a multi tier network application, with a lot of PL/SQL code stored in a database, even more is executed on the application server, libraries written in C language, usually many customisations, personalisations, complex data structures and APIs, with HTML and Java forms. It all sounds like an engineering nightmare, but with a little patience, knowledge and luck we will manage to trace the source of the data.

Oracle eBS provides many techniques of finding database structures buried deep behind this multi-layer structure. Not all methods work on every form. Much depends on the way the form was designed by its developers.

1. Record History

You can find very useful information for many forms in Help -> Record History menu. It doesn’t always work, but when it does it can be very useful.

Let’s have a look at the Segments summary form:

First, focus on the block containing the data. The column selection does not matter, as all columns usually share the same query. In our example I clicked on the first row of the Name column.

Now let’s navigate to the Help menu. Clicking Record History reveals some information about the current record:

We can learn about the creator and creation time, but it also shows the source view for the current block!

2. Last Query

In this approach we will try to find the last query executed by the Forms Server. Of course “last” doesn’t always mean “the one we are looking for”, but this method sometimes helps when the record history is not accessible.

This time we choose Diagnostics -> Examine option from the Help menu. Remember to set focus in the proper block:

Examine Field and Variable Values window pops up showing current value and name of focused element. Change the block to “SYSTEM” and the field to “LAST_QUERY” and viola! Forms Server shows us the last query that was executed. In the case of our test form, it reveals the same source view as the Record history technique:

A note: Some instances of Oracle eBS are configured to not show the Diagnostics menu. It can be configured by the system administrator using the system profile:

Go to System Administrator -> Profile -> System menu and change the values of the following profiles:

Utilities: Diagnostics should be set to “Y”

Hide Diagnostics menu entry should be set to “N”

You can set profile values for everyone (“Site”) or only for your user account (“User”)

Now just log out, log in and start to play!

3. Forms Tracing

Tracing is much more complicated than previous techniques, but usually gives better results. We will force the Forms server to record all the activity in our user session. This includes SQL queries, which we will hunt.

First we will enable tracing in Diagnostics -> Trace menu.

Forms will show us trace file name:

Forms Server starts recording a lot of debugging information in this file.

Now open the form you want to trace, load the data and disable trace.

To read collected data you can convert it to text format using the command:

java oracle.forms.diagnostics.Xlate datafile=tracefile.trc outputfile=textfile.txt outputclass=WriteOutTEXT

You can also open the trace file using the text editor directly and try to understand its contents.

4. Source Code Analysis

Now we enter the “expert” level. Sometimes forms are so complicated that traditional brute force reverse engineering seems the only option possible. Try to get the form source code and analyse it using Oracle Form Builder.

You can see the current form name in Diagnostics -> Examine menu (see Last query above).

Change the block to “SYSTEM” and the field to “CURRENT_FORM”. Forms usually have short code names, like “OKEKAUWB”.

A common location for source forms files is Forms Server working directory: /au/\<version>/forms/\<language>, for example /apps/oraappl/11appl/au/11.5.0/forms/US Source files have .FMB extension.

Understanding Form Builder and PL/SQL code requires a bit of knowledge and a lot of patience, but I will try to show some general rules here.

How are the Forms Built?

Users of Forms Server can run complex applications over the Internet. It is accomplished by either serving html forms or downloading Java applet (Forms Client) into the end user’s web browser. In this article we focus on this second solution.

Java applet connects using http or https protocols to Forms Server which provides the application logic and PL/SQL engine. All user interface functionality is redirected back to the client. Forms definitions are loaded and interpreted by Forms Server, which also maintains database connections.

Blocks

Each form can consist of many blocks showing data on different granularity levels. Below you can see the Requisitions form showing requisition headers and lines. Clicking the Distributions button will open the Distributions block. And yes, it is a different window, but it belongs to the same form!

LOVs, Record Groups

Usually forms are built from three layers of components:

• User interface, for example a text box

• List of values, which represents a scrollable window with one or more columns of data

• Record group, where the SQL query is located

Form designers have a large degree of freedom developing their solutions. Sometimes they don’t use record groups, but instead prefer to build queries in PL/SQL code and move through results in this code.

Sometimes they hide queries in database procedures, which are executed from code. Due to the diversity of programming techniques not all the methods described below will always work.

Conclusion

As you can see there are many ways to find data in an Oracle database but it isn’t always easy. So document anything you learn. Try Dataedo for managing your data dictionary.