Ok, so this is an issue with regards to catching an exception in a java client that was raised by the database (nothing at all to do with ref cursors...)

Thanks to the Piermarini brothers - Mark and Matt - for their help on this one. They figured it out. We don't have a good reason WHY, just that it is this way (as follows, there is a solution)


So, here are the PLSQL snippets we used:

CREATE OR REPLACE
PROCEDURE Test1 ( status OUT NUMBER, c_value OUT NUMBER)
IS
BEGIN
Select 1
Into c_value
From dual
where 1 = 1 ;

status := 0;

END Test1;
/

Test1 Never throws an exception

CREATE OR REPLACE
PROCEDURE Test2 ( status OUT NUMBER, c_value OUT NUMBER)
IS
BEGIN
Select 1
Into c_value
From dual
where 0 = 1 ;

status := 0;

END Test2;
/

Test2 always throws an exception

CREATE OR REPLACE
PROCEDURE Test3 ( status OUT NUMBER, c_value OUT NUMBER)
IS
BEGIN
Select 1
Into c_value
From dual
where 0 = 1 ;

status := 0;

exception
when NO_DATA_FOUND then
status := 1;
END Test3;
/
test3 always throws but also catches and handles the exception and returns a return code to the caller via status

Then the java we test drove this with was:

import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;

class test
{

public static void showTimeStamp( String msg )
{
System.out.println( (new
Timestamp(System.currentTimeMillis())).toString() +
":" + msg );
}

static CallableStatement cstmt_persistent;

public static void set_up( Connection conn, String p ) throws Exception
{
cstmt_persistent =
conn.prepareCall("{ call "+p+"( ?, ? ) }" );
cstmt_persistent.registerOutParameter
( 1, java.sql.Types.NUMERIC );
cstmt_persistent.registerOutParameter
( 2, java.sql.Types.NUMERIC );
}

public static void no_exception(Connection conn ) throws Exception
{
if ( cstmt_persistent == null ) set_up(conn,"test1");
cstmt_persistent.execute();
cstmt_persistent.getLong(1);
cstmt_persistent.getLong(2);
}


public static void with_exception(Connection conn ) throws Exception
{
if ( cstmt_persistent == null ) set_up(conn,"test2");
try
{
cstmt_persistent.execute();
}
catch (SQLException e)
{
//e.printStackTrace();
}
}

public static void with_exception2(Connection conn ) throws Exception
{
if ( cstmt_persistent == null ) set_up(conn,"test2");
try
{
cstmt_persistent.execute();
}
catch (SQLException e)
{
cstmt_persistent.clearParameters();
}
}

public static void no_exception_no_data_either(Connection conn )
throws Exception
{
if ( cstmt_persistent == null ) set_up(conn,"test3");
cstmt_persistent.execute();
cstmt_persistent.getLong(1);
cstmt_persistent.getLong(2);
}

public static void main(String args[])throws Exception
{
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());

Connection conn=DriverManager.getConnection
("jdbc:oracle:thin:@aria-dev:1521:ora816dev"
,"scott","tiger");

DatabaseMetaData meta=conn.getMetaData();
System.out.println
("JDBC driver version is "+meta.getDriverVersion());

conn.setAutoCommit(false);

// stmt.execute( "alter session set sql_trace=true" );

showTimeStamp( "Going to no_exception" );
for( int i = 0; i < 100; i++ ) no_exception(conn);

cstmt_persistent = null;
showTimeStamp( "Going to with_exception" );
for( int i = 0; i < 100; i++ ) with_exception(conn);

cstmt_persistent = null;
showTimeStamp( "Going to with_exception2" );
for( int i = 0; i < 100; i++ ) with_exception2(conn);



cstmt_persistent = null;
showTimeStamp( "Going to with_exception_no_data_either" );
for( int i = 0; i < 100; i++ ) no_exception_no_data_either(conn);

showTimeStamp( "done" );
}
}


Now, it'll run a procedure that gets no exceptions just to baseline.

Then, it runs with exceptions every time. this is noticably slower.

Then it runs with exceptions every time but used "clearParameters" in the exception handler. That Clears the current parameter values immediately. In general, parameter values remain in force for repeated use of a statement. Setting a parameter value automatically clears its previous value. However, in some cases it is useful to immediately release the resources used by the current parameter values; this can be done by calling the method clearParameters. this runs very fast.

Then, it calls a procedure that gets an exception but handles it and returns a return code instead.

The output for example was:

$ java test
JDBC driver version is 8.1.6.2.0
2001-03-06 16:38:53.362:Going to no_exception
2001-03-06 16:38:53.569:Going to with_exception
2001-03-06 16:38:58.598:Going to with_exception2
2001-03-06 16:38:58.684:Going to with_exception_no_data_either
2001-03-06 16:38:58.818:done


so you see the big time delay:

2001-03-06 16:38:53.569:Going to with_exception
2001-03-06 16:38:58.598:Going to with_exception2

5 seconds to call that. Same routine with clearParameters -- less then 1/10 of a second.

The other option is to catch and handle the exception in PLSQL.