Certain SQL queries will cause my connection to freeze and drop after about 20 seconds. This is happening on both an 11g database and a 12c database.
So far, the only thing in common with the offending queries is that they all have multiple groups of quotes, one of the groups has three quotes in a row, i.e. ''', and between the groups, there are pipes or hyphens (e.g. ||||, --).
Example output from sqlplus:
> exec dbms_output.put_line(q'[ '''--''' ]');
BEGIN dbms_output.put_line(q'[ '''--''' ]'); END;
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 143349
Session ID: 284 Serial number: 51992
Elapsed: 00:00:18.93
I am expecting to see '''--''', but the connection is lost instead.
I have replicated the bug in sqlplus 11.2.0.1.0, SQL Developer 4.1.3.20, and Toad 12.6.
This is causing problems when running in packages. A large number of packages have code like this: '''' || l_varchar2_variable || '''';. When these packages are being run in, the connection is lost.
Sample list of queries that cause the connection to be dropped:
exec dbms_output.put_line(q'['''--''']');
exec dbms_output.put_line(q'['''---''']');
exec dbms_output.put_line(q'['''----''']');
exec dbms_output.put_line(q'['''||||''']');
exec dbms_output.put_line(q'['''||||' ' ']');
exec dbms_output.put_line(q'['''||||' ' ']');
exec dbms_output.put_line(''''||''||'''');
execute immediate(''''||||'''');
execute immediate('''' |||| '''');
exec dbms_output.put_line(q'#'''|| ||'''#');
exec dbms_output.put_line(q'[ ||'''||--''' ]');
Sample list of queries that don't have any problems:
exec dbms_output.put_line(q'[ '''' '''' ]');
exec dbms_output.put_line(q'[ ''' || ''' ]');
exec dbms_output.put_line(q'[ ''' | | | | ''' ]');
exec dbms_output.put_line(q'[ ''' - - ''' ]');
exec dbms_output.put_line(q'[ ''' ||| ''' ]');
exec dbms_output.put_line(q'[ ' ' ' |||| ' ' ' ]');
exec dbms_output.put_line(q'[ ''' |||| ]');
exec dbms_output.put_line(q'[ |||| ''' ]');
select ''''||''||'''' from dual;
select '''''||||''''' from dual;
select ''''||'--'||'''' from dual;
Setting the following in sqlnet.ora
TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=c:\_temp
TRACE_FILE_CLIENT=client
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON
DIAG_ADR_ENABLED =OFF
This is the output from after I hit Return to execute the query:
[10-JUL-2017 13:58:33:476] nioqsn: entry
[10-JUL-2017 13:58:33:477] nioqsn: exit
[10-JUL-2017 13:58:33:477] nioqrc: entry
[10-JUL-2017 13:58:33:477] nsbasic_bsd: entry
[10-JUL-2017 13:58:33:477] nsbasic_bsd: tot=0, plen=287.
[10-JUL-2017 13:58:33:477] nttfpwr: entry
[10-JUL-2017 13:58:33:477] nttfpwr: socket 528 had bytes written=287
[10-JUL-2017 13:58:33:477] nttfpwr: exit
[10-JUL-2017 13:58:33:477] nsbasic_bsd: packet dump
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 01 1F 00 00 06 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 03 5E 45 21 00 04 |...^E!..|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF 93 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 FE FF FF FF FF FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF 0D 00 00 00 FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 01 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF FE FF FF |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: FF FF FF FF FF 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:477] nsbasic_bsd: 00 31 42 45 47 49 4E 20 |.1BEGIN.|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 64 62 6D 73 5F 6F 75 74 |dbms_out|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 70 75 74 2E 70 75 74 5F |put.put_|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 6C 69 6E 65 28 71 27 5B |line(q'[|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 27 27 27 2D 2D 27 27 27 |'''--'''|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 5D 27 29 3B 20 45 4E 44 |]');.END|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 3B 0A 00 01 00 00 00 01 |;.......|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 00 00 00 00 00 00 00 08 |........|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 00 00 00 00 00 00 00 00 |........|
[10-JUL-2017 13:58:33:478] nsbasic_bsd: 00 00 00 00 00 00 00 |....... |
[10-JUL-2017 13:58:33:478] nsbasic_bsd: exit (0)
[10-JUL-2017 13:58:33:478] nsbasic_brc: entry: oln/tot=0
[10-JUL-2017 13:58:33:478] nttfprd: entry
[10-JUL-2017 13:58:52:377] ntt2err: entry
[10-JUL-2017 13:58:52:377] ntt2err: exit
[10-JUL-2017 13:58:52:377] nttfprd: exit
[10-JUL-2017 13:58:52:377] nserror: entry
[10-JUL-2017 13:58:52:377] nserror: nsres: id=0, op=68, ns=12535, ns2=12560; nt[0]=505, nt[1]=60, nt[2]=0; ora[0]=0, ora[1]=0, ora[2]=0
[10-JUL-2017 13:58:52:377] nsbasic_brc: exit: oln=0, dln=0, tot=0, rc=-1
[10-JUL-2017 13:58:52:377] nioqrc: wanted 1 got 0, type 0
[10-JUL-2017 13:58:52:377] nioqper: error from nioqrc
[10-JUL-2017 13:58:52:377] nioqper: ns main err code: 12535
[10-JUL-2017 13:58:52:377] nioqper: ns (2) err code: 12560
[10-JUL-2017 13:58:52:377] nioqper: nt main err code: 505
[10-JUL-2017 13:58:52:377] nioqper: nt (2) err code: 60
[10-JUL-2017 13:58:52:377] nioqper: nt OS err code: 0
[10-JUL-2017 13:58:52:377] nioqer: entry
[10-JUL-2017 13:58:52:377] nioqer: incoming err = 12151
[10-JUL-2017 13:58:52:377] nttaddr2bnd: entry
[10-JUL-2017 13:58:52:377] snlinGetNameInfo: entry
[10-JUL-2017 13:58:52:377] snlinGetNameInfo: exit
Note the time change from 13:58:33 to 13:58:52 - this is where the connection is lost.
Thank you for your time!