Hello everyone.
We assume, that in the newer versions of ojdbc11 from ojdbc11-23.X onwards, exceptions thrown inside PL/SQL functions or procedures are not correctly propagated back to calling code in Java.
See the examples in the attached code below.
First we create test_pkg on database with a simple procedure, that always throws exception NO_DATA_FOUND.
create or replace package test_pkg as
procedure test_procedure;
end test_pkg;
/
create or replace package body test_pkg as
procedure test_procedure is
dummy_variable varchar2(1);
begin
select dummy into dummy_variable from dual where 1=0;
end test_procedure;
end test_pkg;
/
Then we create simple Java class, where we connect to database and call procedure from java.
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleCallableStatement;
public class TestPackageCallOracleStatement {
public static void main(String[] args) {
Connection connection = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection(
"jdbc:oracle:thin:@<hostname>:<port>:<sid>", "<username>", "<password>");
CallableStatement stmt = (CallableStatement)connection.prepareCall("{call test_pkg.test_procedure()}");
stmt.execute();
System.out.println("Procedure executed successfully.");
} catch (SQLException e) {
System.out.println(e.getMessage());
if (e.getErrorCode() == 1403) { // ORA-01403: no data found
System.out.println("No data found in PL/SQL procedure.");
} else {
// Handle other SQLExceptions
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
System.out.println("Oracle JDBC Driver not found. Please ensure it's in your classpath.");
e.printStackTrace();
} finally {
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
}
When compiling and running code with different versions of ojdbc-11, we can see, that the results differ.
/*
COMPILE
javac -cp .;ojdbc14.jar TestPackageCallOracleStatement.java
javac -cp .;ojdbc11-23.5.0.24.07.jar TestPackageCallOracleStatement.java
javac -cp .;ojdbc11-23.4.0.24.05.jar TestPackageCallOracleStatement.java
javac -cp .;ojdbc11-23.3.0.23.09.jar TestPackageCallOracleStatement.java
javac -cp .;ojdbc11-23.2.0.0.jar TestPackageCallOracleStatement.java
javac -cp .;ojdbc11-21.15.0.0.jar TestPackageCallOracleStatement.java
javac -cp .;ojdbc11-21.1.0.0.jar TestPackageCallOracleStatement.java
javac -cp .;ojdbc8-21.9.0.0.jar TestPackageCallOracleStatement.java
RUN
java -cp .;ojdbc14.jar TestPackageCallOracleStatement
java -cp .;ojdbc11-23.5.0.24.07.jar TestPackageCallOracleStatement DOES NOT WORK, exception message is null
java -cp .;ojdbc11-23.4.0.24.05.jar TestPackageCallOracleStatement DOES NOT WORK, exception message is null
java -cp .;ojdbc11-23.3.0.23.09.jar TestPackageCallOracleStatement DOES NOT WORK, NullPointerException is thrown
java -cp .;ojdbc11-23.3.0.23.09.jar TestPackageCallOracleStatement DOES NOT WORK, NullPointerException is thrown
java -cp .;ojdbc11-21.15.0.0.jar TestPackageCallOracleStatement WORKS AS EXPECTED
java -cp .;ojdbc11-21.1.0.0.jar TestPackageCallOracleStatement WORKS AS EXPECTED
java -cp .;ojdbc8-21.9.0.0.jar TestPackageCallOracleStatement
*/
For example:
When calling code with ojdbc version ojdbc11-21.15.0.0.jar
C:\temp\test-oracle>java -cp .;ojdbc11-21.15.0.0.jar TestPackageCallOracleStatement
ORA-01403: no data found
ORA-06512: at "RECOGEN5.TEST_PKG", line 6
ORA-06512: at line 1
No data found in PL/SQL procedure.
we get exception propagated into java code as expected.
If we execute code with ojdbc version ojdbc11-23.3.0.23.09.jar
C:\temp\test-oracle>java -cp .;ojdbc11-23.3.0.23.09.jar TestPackageCallOracleStatement
Exception in thread "main" java.lang.NullPointerException: Cannot invoke "String.contains(java.lang.CharSequence)" because "errorMessage" is null
at oracle.jdbc.driver.DatabaseError.addUrlToErrorMessage(DatabaseError.java:927)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:174)
at oracle.jdbc.driver.DatabaseError.createSqlException(DatabaseError.java:244)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:702)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:608)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1330)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:1102)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:456)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:498)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:229)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:1408)
at oracle.jdbc.driver.OracleStatement.executeSQLStatement(OracleStatement.java:1958)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1594)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3754)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:4202)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4206)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1015)
at TestPackageCallOracleStatement.main(TestPackageCallOracleStatement.java:41)
we get null pointer exception.
If we execute code with ojdbc version ojdbc11-23.5.0.24.07.jar
C:\temp\test-oracle>java -cp .;ojdbc11-23.5.0.24.07.jar TestPackageCallOracleStatement
null
No data found in PL/SQL procedure.
we get another version of the output.
Feel free to test the code on your system and please let me know if I am doing something wrong or is this a bug?
I am using OpenJDK Runtime Environment Temurin-21.0.2+13 (build 21.0.2+13-LTS)
Best regards
Jakob