Update: Although it is still relevant today, this was originally written in Aug 2012.
A lot of times, in your PL/SQL application, you might run into an error but do not know what statement caused the error. The program might lack any form of outputting (to UTL folder, log tables or otherwise).
The simplest way to find the exact problem is to run an Oracle trace on the session. By adding a couple of lines around the entry level procedure, you could easily locate the problem statement and what led to it. Tracing can also help you find a lot of additional information about performance etc.
The process is as below and the comments explain each statement
DECLARE v_Count NUMBER; BEGIN v_Count := 0; --The string provided here is appended to the trace file name generated by Oracle EXECUTE IMMEDIATE 'ALTER SESSION SET TRACEFILE_IDENTIFIER = ''Jana'' '; --The string provided here becomes part of v$session view for easy identification of custom trace sessions -- SELECT client_identifier FROM v$session DBMS_SESSION.SET_IDENTIFIER (client_id => 'Janas_Traced_Session'); --Turn on tracing DBMS_SESSION.SET_SQL_TRACE (sql_trace => true); --Do whatever you need to do that needs to be traced.. --i.e., your procedure call goes here SELECT COUNT(1) INTO v_Count FROM pods_Dataset; --Turn off tracing DBMS_SESSION.SET_SQL_TRACE (sql_trace => false); END;
Once the trace file is generated with a custom name, the DBA’s can easily locate it and send it to you and you can open it in SQL Developer to get a fantastic view loaded with lots of information. You can download and install SQL Developer for free
Once installed, simply launch SQL Developer and open the trace file using File -> Open.
(No more tkprof’ing unless you want to!!)
Here are some sample screen shots: