ORA-1403 not being returned through SQLException when thrown by database

Today I observed that when an ORA-1403 was thrown from the database as a result of calling a stored procedure from JDBC it was not returned as a SQLException and was ignored by the JDBC driver. His what happened and how I got around it.

Create a table as follows:

create table t1 (c1 number(6));

insert into t1 values (1);

commit;

Create a stored procedure as follows:

create or replace procedure p(v IN INTEGER) as
r integer;
begin
 select c1 into r from t1 where c1 = v;
 dbms_output.put_line('r = '||r);

 -- Some stuff that would normally happen afterward...
  insert into t1 (c1) values (r+1);
end;
/

Now in a SQLPlus do the following to see that a ORA-1403 is thrown when the value 2 is passed to the procedure.

TARS@lnx102> exec p(2);
BEGIN p(2); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "TARS.P", line 4
ORA-06512: at line 1

TARS@lnx102>

From that your would expect the exact same call in a JDBC program to thrown the same exception but when called as follows it failed and silently ignored the ORA-1403.

String sql = "call p(?)";

However the problem can easily be avoided by using the following SQL from the JDBC program. Both these SQL calls will give the ORA-1403 as expected. You need to ensure the curly braces are added when using CALL, of course thats not required when using oracle style sytnax as shown below.

      String sql = "{call p(?)}";

    OR

    String sql = "begin p(?); end;";