Note: Tested in Oracle 19.13
1. Plsql Test Setup
1.1 ORA-00600 and Session Disconnected
For the first case, we take the same test code from Blog: ORA-600 [4156] SAVEPOINT and PL/SQL Exception Handling
drop table test_tab_disconnet;
create table test_tab_disconnet(id number, label varchar2(10));
insert into test_tab_disconnet(id, label) values(1, 'label');
commit;
create or replace procedure test_ora_600_disconnet as
begin
savepoint sp;
update test_tab_disconnet set label = label where id = 1;
execute immediate '
begin
raise_application_error(-20000, ''error-sp'');
exception
when others then
rollback to savepoint sp;
update test_tab_disconnet set label = label where id = 1;
raise;
end;';
end;
/
-- Session disconnected when calling:
-- exec test_ora_600_disconnet;
-- ORA-00603: ORACLE server session terminated by fatal error
-- ORA-00600: internal error code, arguments: [4156], [], [], [], [], [], [], [], [], [], [], []
-- ORA-20000: error-sp
-- ORA-06512: at line 8
-- ORA-06512: at line 3
-- Process ID: 2496
-- Session ID: 193 Serial number: 5555
1.2 ORA-00600 and Session Not Disconnected
For the second case, we take the same test code from Blog: How volatile is ORA-00600 [qernsRowP] ?
drop type t_char100_varray50_test force;
create or replace noneditionable type t_char100_varray50_test as varray(50) of varchar2(100)
/
drop table test_tab_disconnet_no cascade constraints;
create table test_tab_disconnet_no as select level id, t_char100_varray50_test('a', 'b', 'c') vary
from dual connect by level <= 1e4;
alter table test_tab_disconnet_no add constraint test_tab_disconnet_no#p primary key (id);
create or replace procedure test_ora_600_disconnet_no as
begin
for c in (
select /*+ parallel(4) index(t test_tab_disconnet_no#p) */ t.id, count(*)
from test_tab_disconnet_no t, table(t.vary) v
where rownum <= 3000
group by t.id)
loop
null;
end loop;
end;
/
-- Session not disconnected when calling:
-- exec test_ora_600_disconnet_no;
-- ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []
1.3 Plsql Wait Helper
create or replace procedure test_wait_for_seconds (p_seconds number) as
begin
dbms_application_info.set_client_info('Plsql Waiting '||p_seconds||' seconds for you to check Connection');
dbms_session.sleep(p_seconds);
dbms_application_info.set_client_info('Plsql Waiting '||p_seconds||' ended. JDBC Connection still alive');
end;
/
2 JDBC Test Setup
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.Struct;
import java.sql.Array;
import java.sql.SQLException;
import java.util.Vector;
import java.time.LocalDateTime;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.OracleConnection;
// Ora600JDBCTestV2 // 1: test_ora_600_disconnet; 2: test_ora_600_disconnet_no
// Ora600JDBCTestV2 "jdbc:oracle:thin:k/s@testDB:1522:testDB" 1
// Ora600JDBCTestV2 "jdbc:oracle:thin:k/s@testDB:1522:testDB" 2
public class Ora600JDBCTestV2 {
static String TEST_PROC_DIS = "begin test_ora_600_disconnet; end;";
static String TEST_PROC_DIS_NO = "begin test_ora_600_disconnet_no; end;";
static String WAIT_PROC = "begin test_wait_for_seconds(30); end;";
public static void main(String[] args) {
String ret = ora600Call(args);
// return caught outtput to show that ORA-00600 can be caught and returned.
System.out.println("Ora600 JDBC calling return: " + ret);
}
static String ora600Call(String[] args) {
String jdbcURL = args[0];
int connCase = Integer.parseInt(args[1]);
CallableStatement cStmt;
String exceptMessage = "No Exception";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (ClassNotFoundException e) {
System.out.println("Where is your Oracle JDBC Driver ?");
e.printStackTrace();
return "ClassNotFoundException return";
}
System.out.println(java.time.LocalDateTime.now());
Connection conn = null;
try {
conn = DriverManager.getConnection(jdbcURL);
System.out.println("You Connected");
cStmt = conn.prepareCall(WAIT_PROC);
System.out.println("Waiting 30 seconds .... for you to check Connection");
cStmt.execute();
cStmt.close();
System.out.println(java.time.LocalDateTime.now());
if (connCase == 1) {
System.out.println("test_ora_600_disconnet starting ....");
cStmt = conn.prepareCall(TEST_PROC_DIS);
} else {
System.out.println("test_ora_600_disconnet_no starting ....");
cStmt = conn.prepareCall(TEST_PROC_DIS_NO);
}
cStmt.execute();
cStmt.close();
System.out.println("You made it, Test End");
return "Normal return";
} catch (Exception e) {
System.out.println(java.time.LocalDateTime.now());
exceptMessage = e.toString();
System.err.println("You have Exception: " + e.getMessage());
e.printStackTrace();
return "Exception return:" + exceptMessage;
} finally {
System.out.println(java.time.LocalDateTime.now());
// return Plsql Exception Message to JDBC caller for catching
System.out.println("Return Plsql Exception to JDBC caller: " + exceptMessage);
System.out.println("Waiting 30 seconds .... before FINALLY return");
try {
Thread.sleep(30*1000);
} catch (InterruptedException e) {
System.out.println(e);
}
System.out.println(java.time.LocalDateTime.now());
System.out.println("You FINALLY return.");
return "FINALLY return:" + exceptMessage;
}
}
}
3. Test Run
Compile JDBC code and run two tests.
From output, we can see that ORA-00600 can be caught and returned in both cases.
3.1 ORA-00600 and Session Disconnected
$ > Ora600JDBCTestV2 "jdbc:oracle:thin:k/s@testDB:1522:testDB" 1
2022-07-17T08:16:54.514
You Connected
Waiting 30 seconds .... for you to check Connection
2022-07-17T08:17:25.042
test_ora_600_disconnet starting ....
2022-07-17T08:17:38.935
You have Exception: ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4156], [], [], [], [], [], [], [], [], [], [], []
ORA-20000: error-sp
ORA-06512: at line 8
ORA-06512: at line 3
java.sql.SQLRecoverableException: ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4156], [], [], [], [], [], [], [], [], [], [], []
ORA-20000: error-sp
ORA-06512: at line 8
ORA-06512: at line 3
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:265)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:86)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:965)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1358)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4251)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)
at Ora600JDBCTestV2.ora600Call(Ora600JDBCTestV2.java:60)
at Ora600JDBCTestV2.main(Ora600JDBCTestV2.java:22)
Caused by: Error : 603, Position : 0, Sql = begin test_ora_600_disconnet; end;, OriginalSql = begin test_ora_600_disconnet; end;, Error Msg = ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4156], [], [], [], [], [], [], [], [], [], [], []
ORA-20000: error-sp
ORA-06512: at line 8
ORA-06512: at line 3
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
... 16 more
2022-07-17T08:17:38.936
Return Plsql Exception to JDBC caller: java.sql.SQLRecoverableException: ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4156], [], [], [], [], [], [], [], [], [], [], []
ORA-20000: error-sp
ORA-06512: at line 8
ORA-06512: at line 3
Waiting 30 seconds .... before FINALLY return
2022-07-17T08:18:08.937
You FINALLY return.
Ora600 JDBC calling return: FINALLY return:java.sql.SQLRecoverableException: ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4156], [], [], [], [], [], [], [], [], [], [], []
ORA-20000: error-sp
ORA-06512: at line 8
ORA-06512: at line 3
3.2 ORA-00600 and Session Not Disconnected
$ > Ora600JDBCTestV2 "jdbc:oracle:thin:k/s@testDB:1522:testDB" 2
2022-07-17T08:21:06.148
You Connected
Waiting 30 seconds .... for you to check Connection
2022-07-17T08:21:36.690
test_ora_600_disconnet_no starting ....
2022-07-17T08:21:37.750
You have Exception: ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []
java.sql.SQLException: ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:461)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1104)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:655)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:265)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:86)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:965)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1205)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3666)
at oracle.jdbc.driver.T4CCallableStatement.executeInternal(T4CCallableStatement.java:1358)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3778)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4251)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(OraclePreparedStatementWrapper.java:1081)
at Ora600JDBCTestV2.ora600Call(Ora600JDBCTestV2.java:60)
at Ora600JDBCTestV2.main(Ora600JDBCTestV2.java:22)
Caused by: Error : 600, Position : 0, Sql = begin test_ora_600_disconnet_no; end;, OriginalSql = begin test_ora_600_disconnet_no; end;, Error Msg = ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:513)
... 16 more
2022-07-17T08:21:37.751
Return Plsql Exception to JDBC caller: java.sql.SQLException: ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []
Waiting 30 seconds .... before FINALLY return
2022-07-17T08:22:07.752
You FINALLY return.
Ora600 JDBC calling return: FINALLY return:java.sql.SQLException: ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], [], [], [], [], []
