7 Replies Latest reply: Feb 8, 2013 11:33 AM by rp0428 RSS

    Accessing Oracle PL/SQL stored procedure exception in java

    800839
      Hi,

      How to get the exception message thrown in Oracle PL/SQL stored procedure in java as there can be only one OUT parameter which is a CURSOR in this case?
      Hence how to set that exception message in OUT parameter to be passed to Java?

      Thanks
        • 1. Re: Accessing Oracle PL/SQL stored procedure exception in java
          odie_63
          Hence how to set that exception message in OUT parameter to be passed to Java?
          You don't have to do anything.
          Just let the PL/SQL exception propagate to the calling Java program.
          Most likely the exception will be wrapped in an SQLException class instance which you can then catch or let propagate in its turn, just like any other Exception raised in Java code.
          • 2. Re: Accessing Oracle PL/SQL stored procedure exception in java
            800839
            Thanks. When an exception is thrown in StoredProcedure automatically it will be propagated to the Java catch block where we will handle the SQLException. When we print sqlException.getStackTrace() we ill get the exception details thrown in StoredProcedure? Please clarify.

            try {


            //CallableStatement to invoke the StoredProcedure

            }catch(SQLException e){
            e.printStackTrace() // will print the exception details which is thrown in the StoredProcedure
            }
            • 3. Re: Accessing Oracle PL/SQL stored procedure exception in java
              odie_63
              When an exception is thrown in StoredProcedure automatically it will be propagated to the Java catch block where we will handle the SQLException. When we print sqlException.getStackTrace() we ill get the exception details thrown in StoredProcedure?
              Yes.

              Does your test show a different behaviour?
              • 4. Re: Accessing Oracle PL/SQL stored procedure exception in java
                800839
                Yes, I am unable to get the exception stack trace thrown in StoredProcedure in JAVA using the above code. Whether l we need to assign a variable in which we can set the exception stack trace of Oracle and then pass this variable as an OUT parameter? The problem is we can use only one OUT parameter which I am already using as a CURSOR?
                • 5. Re: Accessing Oracle PL/SQL stored procedure exception in java
                  odie_63
                  Whether l we need to assign a variable in which we can set the exception stack trace of Oracle and then pass this variable as an OUT parameter?
                  Again, no, you don't have to do that, you must not do that.
                  This is not how we deal with exceptions in modern languages.

                  Does the Oracle procedure have an exception handler that may catch the exception before it is passed to the caller?
                  If so, remove it entirely.

                  Post the procedure code and Java snippet that calls it.
                  • 6. Re: Accessing Oracle PL/SQL stored procedure exception in java
                    Billy~Verreynne
                    797836 wrote:
                    Yes, I am unable to get the exception stack trace thrown in StoredProcedure in JAVA using the above code.
                    Then there is no exception being thrown up the call stack. You likely have a exception when OTHERS handler in the PL/SQL proc that is to blame for this?
                    Whether l we need to assign a variable in which we can set the exception stack trace of Oracle and then pass this variable as an OUT parameter?
                    This approach (sending out parameter for error response) is wrong. In Java. And in PL/SQL.

                    If your PL/SQL code correctly handles and raises exceptions, your Java code will see it. If not, then the problem is with your code.
                    • 7. Re: Accessing Oracle PL/SQL stored procedure exception in java
                      rp0428
                      >
                      Yes, I am unable to get the exception stack trace thrown in StoredProcedure in JAVA using the above code. Whether l we need to assign a variable in which we can set the exception stack trace of Oracle and then pass this variable as an OUT parameter? The problem is we can use only one OUT parameter which I am already using as a CURSOR?
                      >
                      You may be confusing two different things.

                      There is no 'exception stack trace' thrown in a stored procedure.

                      An exception is thrown. Oracle DOES create an error stack but that error stack is NOT what is returned to a client.

                      Oracle provides functions in the DBMS_UTILITY package that can format and return the error stack or backtrace.

                      See FORMAT_ERROR_BACKTRACE and FORMAT_ERROR_STACK in the doc
                      http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_util.htm#i1003874

                      But unless your procedure calls one of those functions and returns the result your Java code won't see it.

                      Your Java code will only get the actual exception information and when you use 'printStackTrace' in Java that is the information about the Java execution chain of events and has nothing to do with the Oacle error stack.

                      As others have stated your Java code should just trap the exception to know that the error occured and then deal with your Java user appropriately.

                      The Oracle procedure code could be modified to call one of thoe utility functions to get the full Oracle stack trace but the procedure should not return that to Java as an OUT parameter. The procedure should log that information to a log table for the DBA to access if needed.


                      The