Advanced Usage Techniques

Exception Handling in Java

OracleXMLSQLException class

The utility catches all exceptions that occur during processing and throws an oracle.xml.sql.OracleXMLSQLException which is a run time exception. The calling program thus does not have to catch this exception all the time. If the program can still catch this exception and do the appropriate action. The exception class provides functions to get the error message and also get the parent exception, if any. For example, the program shown below, catches the run time exception and then gets the parent exception.

OracleXMLNoRowsException class

This exception is generated when the setRaiseNoRowsException is set in the OracleXMLQuery class during generation. This is a subclass of the OracleXMLSQLException class and can be used as an indicator of the end of row processing during generation.

import java.sql.*;
import oracle.xml.sql.query.OracleXMLQuery;

public class testException
{
   public static void main(String argv[])
     throws SQLException
   {
      Connection conn = getConnection("scott","tiger");

      // wrong query this will generate an exception
      OracleXMLQuery qry = new OracleXMLQuery(conn, "select * from emp where sd
 = 322323");

      qry.setRaiseException(true); // ask it to raise exceptions..!

      try{
        String str = qry.getXMLString();
      }catch(oracle.xml.sql.OracleXMLSQLException e)
      {
        // Get the original exception
        Exception parent = e.getParentException();
        if (parent instanceof java.sql.SQLException)
        {
           // perform some other stuff. Here we simply print it out..
           System.out.println(" Caught SQL Exception:"+parent.getMessage());
        }
        else
          System.out.println(" Exception caught..!"+e.getMessage());
     }
   }
    // Get the connection given the user name and password..!
    private static Connection getConnection(String user, String passwd)
      throws SQLException
    {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      Connection conn =
          DriverManager.getConnection("jdbc:oracle:oci8:@",user,passwd);
     return conn;
   }

}

Exception Handling in PL/SQL

Here is a PL/SQL exception handling example:

declare
  queryCtx DBMS_XMLQuery.ctxType;
  result clob;
  errorNum NUMBER;
  errorMsg VARCHAR2(200);
begin

  queryCtx := DBMS_XMLQuery.newContext('select * from emp where df = dfdf');

  -- set the raise exception to true..
  DBMS_XMLQuery.setRaiseException(queryCtx, true);
  DBMS_XMLQuery.setRaiseNoRowsException(queryCtx, true);

  -- set propagate original exception to true to get the original exception..!
  DBMS_XMLQuery.propagateOriginalException(queryCtx,true);
  result := DBMS_XMLQuery.getXML(queryCtx);

  exception
    when others then
      -- get the original exception
      DBMS_XMLQuery.getExceptionContent(queryCtx,errorNum, errorMsg);
      dbms_output.put_line(' Exception caught ' || TO_CHAR(errorNum)
                   || errorMsg );
end; 
/