Getting the Oracle table name used in an Oracle Form

I have an Oracle Form (fmx file) Ver 4.5 connected to an Oracle database (9i). I need to know the Form's data comming from which database table. I remember there was a way to put a special character in any text field and press F8 to enter the query manually but I am not sure about it.
ehmfAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
sonicefuConnect With a Mentor Commented:
One way is to
1) run this form
2) enter value in required field about which you want to know, for example XYZAAA
3) execute it

4)
SQL> select sql_text from v$sql
where sql_text like '%XYZAAA%';
0
 
ehmfAuthor Commented:
hi socicefu

where I can put the select query ??
0
 
sonicefuCommented:
at SQL*Plus
-------------
go to command prompt and type
sqlplusw userName/password@connctString

following will appear
SQL>

then paste

select sql_text from v$sql
where sql_text like '%XYZAAA%';



0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ehmfAuthor Commented:
I used it but it shows only the queries written before in SQL*Plus and it does not contain the form's query
0
 
sonicefuCommented:
first execute form, after entering a value into required field
then execute this query at SQL*Plus
---------------------------------------------

suppose

you've an employee search form
enter employeeNumber in empNo field and then press search button or execute query button

suppose you entered employeeNumber 10011

now execute query as follows
select sql_text from v$sql
where sql_text like '%10011%';

Open in new window

0
 
schwertnerCommented:
You can open the .fmx file in a good text editor and copy most of your SQL.
Of course this will take time.
0
 
jwahlConnect With a Mentor Commented:
maybe you mean

F7 ("Enter Query")
&
F8 ("Execute Query")

enterning "&" in a field opens a dialog box for a user defined query.
but i don't know how to find the base table for the forms ...?
0
 
schwertnerCommented:
The "Enter Query" Mode will never show the SQL it uses and stands behind the form. Even more - one form may have many blocks and these blocks normally are based on different SQLs.
So try to "read" the .fmb and to gues the SQLs.
0
 
sventhanCommented:
do you have Toad? If you have it, It would be easier to figure it out.
0
 
Ritesh_GargCommented:
If you have fmb file, you can simply try to look at block property to see the table it is based on, but it seems this is not the case.

Another way would be that you want to fail an operation.  Example, try to insert a record such that there is a constraint violation, e.g. insert a record with with an existing primary key value, or try to insert a record violating the Unique Key, or try to query something like a character in a number field.

Once the record fails to insert or query, go to Help (ctl + F1) and look at the error.  You would see the error statement, and you can see the table name there.

If this does not work, try using Oracle Trace
1. Enable the trace on database
2. Run the form query
3 Run tkprof on the trace file
4. The output file has all the queries executed.
0
 
Jinesh KamdarCommented:
My 2 suggestions : -

1. Put ur cursor in the field on the Form and then select Help >> Record History.
This will show u the table / view from where the field data was populated.

2. Switch ur Diagnostics ON, then go to Help >> Diagnostics >> Examine ...
In the Block text-box, type SYSTEM and in the Field text-box, type LAST_QUERY.
Hit tab and then the Value text-box will show the last query executed by the form.

Hope this helps!
0
All Courses

From novice to tech pro — start learning today.