Jump from log immediate to the Oracle PL/SQL source

How many times did you find a log message from your Oracle application and would like to jump immediately to the PL/SQL source in the database? Well, you can with Oracle SQL Developer! This is how.

It needs some preparation. You need to register with each message that you log where that message was initiated. The simplest way to do this is to pass the so-called “Predefined Inquiry Directives” $$PLSQL_UNIT and the $$PLSQL_LINE  with each call to your (autonomous transaction) log method (see the PL/SQL Language reference).

A more sophisticated technique is by using the WHO_CALLED_ME procedure from the OWA_UTIL package (see the PL/SQL Packages documentation). If you call that in your log method before any other method, it will return you the database object owner, the database object name and the line number in that database object. So you won’t have to worry about saving those items with each call: it is done automatically once you have programmed it in your log method.

Once you have these object owner, name and the line in your log message, you can create a query (or report) in SQL Developer that contains a link to that source line.  You can find the way how to do this at Navigate to a specific line of PLSQL code by Jeff Smith. Or search the internet for “SQLDEV:LINK”. The link column looks something like this:

'SQLDEV:LINK:'
 ||owner
 ||':'||object_type
 ||':'||object_name
 ||':'||(calling_from_line + 1)
 ||':'||0
 ||':'||a_nice_description
 ||':oracle.dbtools.raptor.controls.grid.DefaultDrillLink' as Link2Object

Retrieve the object type from the ALL_OBJECTS view. You only need to query for the object types PACKAGE, PACKAGE BODY, FUNCTION, PROCEDURE and TRIGGER. (maybe TYPE BODY too).

A note on OWA_UTIL’s WHO_CALLED_ME procedure. I believe it originates from the famous Ask Tom pages ( the initial version? ), but is now included in the the OWA_UTIL package. Unfortunately with triggers there is something weird going on. The data returned from WHO_CALLED_ME is using the output of DBMS_UTILITY.FORMAT_CALL_STACK. But the format that is put on that call stack for triggers is rather different then for packages, functions and procedures. So you might construct your own “where_was_i_called_from” function that supplies you with a proper string or collection.

Happy debugging!

 

 

PS: you may refrain from using WHO_CALLED_ME since it has this flaw with triggers. Also the full parsing may be not needed at the time the log message is saved. You only need to save the first entry of the call stack. This is the statement to extract the first line:

trim(regexp_substr(dbms_utility.format_call_stack,’^0x[a-f0-9]{9}(.*)$’,1,1,’m’,1))

 

Comments are closed.

Herman Mol is powered by WordPress.  Staypressed theme by Themocracy