Q
Problem solve Get help with specific problems with your technologies, process and projects.

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?

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?

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.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close