3

I am trying to get the SQL query to be returned from a database call, when the query fails to execute and the return cursor has a failure state. This query string will be used for logging purposes.

One way to do this is to hold a variable that contains the SQL query as a string, and then wrap each query in the PL/SQL block with the exception handler and if an exception arises, return the query string with the failure status to the UI component.

Is there a better way to do this? Does Oracle exception object or any other package support this feature?

This question seems like a duplicate for the thread: Obtain the Query/CommandText that caused a SQLException

However, I did not find a solution to the problem in that thread, and I would like to know if there are any new packages supported by Oracle that gives us the query string that caused the exception to occur.

Community
  • 1
  • 1
Shankar Raju
  • 4,356
  • 6
  • 33
  • 52
  • 1
    http://stackoverflow.com/a/493590/409172 may be helpful. Although if you're calling a procedure that runs SQL, it may log that procedure call instead of the SQL that really caused the failure. – Jon Heller May 04 '12 at 05:03
  • You're looking to do this _outside_ a PL/SQL block or an external block right? i.e. if you run `select * from duala;` in SQL*Plus it will get logged somewhere? – Ben May 04 '12 at 05:53
  • @jonearles, I will read through it and let you know if it works, thanks! – Shankar Raju May 04 '12 at 14:42
  • @Ben, I didn't understand your idea, could you please elaborate? – Shankar Raju May 04 '12 at 14:43
  • @Shankar, it wasn't an idea it was a question! Do you want every SQL query ran, no matter where from to be logged it if fails? Where are you calling the stored procedure / query from and which explicitly is it as you're contradicting yourself in your comments to the answer? – Ben May 04 '12 at 14:58
  • @Ben, lol I was thinking you were giving me some direction to look for an answer.. so well I am trying to invoke stored procedures from a JSP application and I need to log the incorrect queries on the Java side, so I need the query that failed to be sent back to the Java side.. – Shankar Raju May 04 '12 at 15:15

1 Answers1

1

When you are calling the database from an external language (Java, C#) you can probably do it, by implementing your own JDBC (or whatever) driver.

You would do so by delegating the real world to a normal driver, but whenever a statement is sent to the database you remember the statement and if causes an exception you create a new exception which also includes the sql statement (and possibly bind variables).

Note that this will only give you your statement. If you execute a stored procedure or cause a trigger to fire which in turn fails you only get the original statement not the one that actually failed.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348