How to Identify the PL/SQL in V$SQL being Executed in Current Session

 Hi All

I would like to prepare a stored procedure (named as Log_Error) to perform the error handling for any PL/SQL units in the execution.

It is expected the users to submit or execute the SQL and PL/SQL statements in the folloging format as:

begin

       ..........

exception

      when others then

               Log_Error;

end;

In the design, the procedure, Log_Error, will issue a SQL statement to try to retrieve the PL/SQL source code (as above) from V$SQL and V$SESSION views and then dump the log data onto a separate database table.

In the testing environment, I found that the V$SESSION view can provide two columns of information, i.e. SQL_ID and PREV_SQL_ID.