PL/SQL Enterprise Workbench

Raise Application Error

Example Description

This small example shows how an a PL/SQL thrown exception is handled as SQLException in Java code.
DON'T CATCH the exception inside the PL/SQL code. Catch the exception in Java, you will receive a complete Java and PL/SQL call stack.

Important Notes !

If you want to sent the message as user information from your application, you have to extract the value from the message string of the exception.
A better solution is to use the Oracle failure code beginning from -20000 to -20999 and assign the code to a senseful text message.

PL/SQL Procedure

The procedure 'RAISE_EXCEPTION' shows the complete stacktrace info.

PL/SQL Procedure :

RAISE_EXCEPTION

create or replace procedure raise_exception
/**
 * Demonstration of handling user and runtime PL/SQL exceptions.<br>
 * Just have a look at the stacktrace. The call hierarchy of the java methods and PL/SQL stored procedures 
 * are displayed with line numbers.
 */
is
begin
  raise_application_error(-20000, 'just throw an exception displaying this text information');
end raise_exception;

Factory API : Calling the PL/SQL package

Using the static factory to get the remote service.

Java Calling Class :

RaiseApplicationErrorFactoryApi.java

package plsql_workbench_examples.factoryapi;

import factory.ExamplesRPCFactory;
import service.RaiseExceptionService;

public class RaiseApplicationErrorFactoryApi {
  public static void main(String[] args)
  {
    try {
      // set database credentials and configuration parameters
      System.setProperty("dbw_examples.url", "jdbc:oracle:thin:@192.168.0.109:1521/orcl");
      System.setProperty("dbw_examples.username", "dbw_examples");
      System.setProperty("dbw_examples.password", "dbw_examples");

      // getting the service
      RaiseExceptionService service = ExamplesRPCFactory.getRaiseExceptionService();

      // calling the stored procedure
      service.call();

      System.err.println("NO - call has to throw a SQLException");
    }
    catch (Exception e) {
      System.out.println(">>> Message :\n" + e.getMessage());
      System.out.println(">>> Stacktrace :");
      e.printStackTrace(System.out);
    }
  }
}

Spring API : Calling the PL/SQL package

Using Spring annotation to inject the service and call the remote service.

Java Calling Class :

RaiseApplicationErrorSpringApi.java

package plsql_workbench_examples.springapi;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.support.GenericApplicationContext;
import org.springframework.stereotype.Component;

import service.RaiseExceptionService;

@Component
public class RaiseApplicationErrorSpringApi {
  @Autowired
  private RaiseExceptionService raiseExceptionService;

  public static void main(String[] args)
  {
    // set database credentials and configuration parameters
    System.setProperty("dbw_examples.url", "jdbc:oracle:thin:@192.168.0.109:1521/orcl");
    System.setProperty("dbw_examples.username", "dbw_examples");
    System.setProperty("dbw_examples.password", "dbw_examples");

    // Register Spring Beans, Spring Context and call demo method 
    try (GenericApplicationContext ctx = BaseSpringConfig.getCtx(RaiseApplicationErrorSpringApi.class)) {
      ctx.getBean(RaiseApplicationErrorSpringApi.class).runDemo();
    }
  }

  private void runDemo()
  {
    try {
      // calling the stored procedure
      raiseExceptionService.call();

      System.err.println("NO - call has to throw a SQLException");
    }
    catch (Exception e) {
      System.out.println(">>> Message :\n" + e.getMessage());
      System.out.println(">>> Stacktrace :");
      e.printStackTrace(System.out);
    }
  }
}

Message Output

ORA-20000: just throw an exception displaying this text information
ORA-06512: in "DBW_EXAMPLES.RAISE_EXCEPTION", Zeile 9
ORA-06512: in Zeile 1

Stacktrace Output

java.sql.SQLException: ORA-20000: just throw an exception displaying this text information
ORA-06512: in "DBW_EXAMPLES.RAISE_EXCEPTION", Zeile 9
ORA-06512: in Zeile 1

  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:399)
  at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1059)
  at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
  at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
  at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:587)
  at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:220)
  at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:48)
  at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:938)
  at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1150)
  at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:4798)
  at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4901)
  at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:5631)
  at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1385)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  at java.lang.reflect.Method.invoke(Method.java:498)
  at oracle.ucp.jdbc.proxy.StatementProxyFactory.invoke(StatementProxyFactory.java:353)
  at oracle.ucp.jdbc.proxy.PreparedStatementProxyFactory.invoke(PreparedStatementProxyFactory.java:178)
  at oracle.ucp.jdbc.proxy.CallableStatementProxyFactory.invoke(CallableStatementProxyFactory.java:136)
  at com.sun.proxy.$Proxy3.execute(Unknown Source)
  at impl.RaiseExceptionServiceImpl.call(RaiseExceptionServiceImpl.java:34)
  at plsql_workbench_examples.factoryapi.RaiseApplicationErrorFactoryApi.main(RaiseApplicationErrorFactoryApi.java:14)