Tracing what application designer is doing with the PeopleSoft database can come in very handy. However, as far as I know, there isn't a way to trace application designer by setting SQL trace flags on the client or on application server. But I could be wrong - if you know of a way, please post to the forums.
How to use statement tracer with application designer
First, start application designer and statement tracer for Oracle. It really helps if you have two monitors here. Statement tracer for Oracle starts paused by default so it won't start tracing until you tell it to.
Second, like any other trace, you need to set up your trace scenario. In this example, I'm going to trace the open definition search functionality on the OPRID field. So in application designer, choose File > Open (CTRL + O). Set the definition to Field and type OPRID in the name. DO NOT press open yet.
Third, switch back to statement tracer and press the blue play button (or choose Options > Start Trace from the menu). You should see a message saying the log has started for application designer - note that if you have any other software that is connected to a database, that software will also be available to trace, so you might want to close that down.
This now means that whatever you do in application designer will be traced. So switch back to application designer and press the open button on the open definition dialog.
Statement tracer should have traced something - the SQL used to perform the search for the OPRID field. You should see a prepare and then an execute statement like this:
This tells you exactly what SQL is used to perform the search - as you can see it uses the PSDBFIELD record and performs a like on anything starting with OPRID.
Now, switch back to application designer, select the OPRID field and press the Open button. You may have to confirm you want to open the OPRID field because it is a definition you are not authorized to update.
Switch back to statement tracer and you should see more output (note that the SQL in the screenshot has been truncated a bit):
Finally you can also use the save functionality in statement tracer to save your log file for future reference. Some other nifty features in statement tracer include:
- Edit > Clear logs - To clear out your trace log
- Edit > Add Message - This lets you add your own message into the trace for reference.
- Options > Settings - This allows you to control what events are monitored and which columns are displayed in the output window.
Another useful feature of statement tracer is that it tracks execution time. So if you have something that is taking a long time, but can't figure out why - try using statement tracer to work out which SQL is causing the delay.