Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

In newer versions of JDBC 11 exception does not propagate back to java

Jakob RežunOct 16 2024

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
        -- this select statement will always trigger no_data_found
        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

Comments

Add EVENT_10842=15

You can also set this as an environment variable with the same name & value.

Sheeraz Majeed May 10 2024 — edited on May 10 2024

Hi, Thanks for reply.

But still no trace or log files to check used oci functions

Works for me.

cjones@cjones-mac:~$ rm -rf $HOME/instantclient/log/diag/clients && mkdir -p $HOME/instantclient/log/diag/clients
cjones@cjones-mac:~$ export EVENT_10842=15
cjones@cjones-mac:~$ sqlplus cj/cj@localhost/orclpdb1

...

SQL> select * from dual;

D
-
X

SQL> exit

And then a file like /Users/cjones/instantclient/log/diag/clients/user_cjones/host_nnnnnn_nn/trace/ora_nnn_nnnnn.trc will contain lines like:

# 2024-06-06 07:26:54.520 # Thread ID 8547126272 # Entry - OCIServerAttach(srvhp = 0x128030068, errhp = 0x13580a410, dblink_hash = H:0x9bf0f8e9ee161fdf, mode = OCI_DEFAULT(000000000), dblink = localhost/orclpdb1, dblink_len = 18 );
1 - 3

Post Details

Added on Oct 16 2024
0 comments
412 views