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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ehmfAuthor Commented:
hi socicefu

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

following will appear

then paste

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

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
first execute form, after entering a value into required field
then execute this query at SQL*Plus


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

You can open the .fmx file in a good text editor and copy most of your SQL.
Of course this will take time.
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 ...?
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.
do you have Toad? If you have it, It would be easier to figure it out.
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.
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.