Finding SQL that caused an error

I have an error trigger that reports on an error the info from v$session. With the sid, serial and hash_value, etc., I can look up the SQL that caused the error (V$SESSION, V$PROCESS, V$SESS_IO, V$SQLTEXT_WITH_NEWLINES). Question: Do you know how I can find the module/package/procedure that the found SQL is part of?

    Requires Free Membership to View

    Login

If your application was developed correctly, it will use the DBMS_APPLICATION_INFO package to set the MODULE and ACTION columns in V$SESSION. This would help you correctly diagnose where the SQL statement originated from. Unfortunately, most applications do not make use of this package like they should.

If the code was from a stored procedure, trigger, etc. then you can query DBA_SOURCE similar to the following: 

SELECT owner,name FROM dba_source
WHERE UPPER(text) LIKE UPPER(%%);
If the SQL statement originated from the application, then you'll have to talk with the application developer or vendor.

This was first published in April 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top