Using a With Function in a Select, Toad will not allow to reference Parameters more than once. Errors: "ORA-03113: end-of-file on communication channel" or "Connection is not connected". (4308093)

Chat now with support

Get Live Help
Complete Registration

Sign In

Request Pricing

Contact Sales

Return

Did this article solve an issue for you?

[Select Rating]

Title

Using a With Function in a Select, Toad will not allow to reference Parameters more than once. Errors: "ORA-03113: end-of-file on communication channel" or "Connection is not connected".
Description

When using a With Function in a Select, Toad will not allow to reference Parameters more than once without failing with, "ORA-03113: end-of-file on communication channel", error. The same code runs fine in SQL Developer without error. SQL Plus however also gets the same issue.
Toad Tests:
Using F9 to run code, the error is:
"Connection is not connected".
Using F5 to run code, the error is:
"ORA-03113: end-of-file on communication channel".
=====
By simply having a With Function in a select (no need to call the function), the select will fail if referencing a :parameter more than once in the SQL code.
Oddly, if there are multiple With blocks, the problem goes away.
____ See: --dummy as (select 1 from dual) -- need this dummy with select to end 'with' Funcition to avoid ERROR - ORA-03113: end-of-file on communication channel
Even odder, if you add an or :Parameter = xxx, the problem goes away,
____ This code fails: and :a_parm = 1 and :a_parm = 1
____ This code works: and :a_parm = 1 or :a_parm = 2 and :a_parm = 1
Overall while all the code is valid if you remove the With Functiion.... things are behaving very inconsistently due to introducing the With Function to a simple select.

See below a simplified example of a Character Cleanse function that caused odd illegitimate syntax errors.
--==========
with
function cleanse(text_in varchar) return varchar -- *** Function to remove non standard Text Characters that Cause Issues in Excel - Illegal Char ***
is
text_out VARCHAR2(4000);
begin
-- Saving data with these Char's to Excel - tends to fail with an illegal character error, and it will not let you save the data.
-- Removes all 30 Standard ASCII control characters to avoid issues even though not all have been detected in Oracle Data.
text_out :=
REGEXP_REPLACE(
REPLACE(REPLACE(
text_in,
CHR(1), NULL /* OH (start of heading) */),
CHR(2), NULL /* STX (start of text) */),
'( ){2,}', ' ') ;
return text_out;
end;
--dummy as (select 1 from dual) -- need this dummy with select to end 'with' Function to avoid ERROR - ORA-03113: end-of-file on communication channel
-----------------------------------------------------------------------------------------------------------------------------------------
SELECT
'** Parms **',
:a_parm a_param, -- if you select the :a_parm twice, the code will fail
-- :a_parm a_param2,
'the end'
FROM dual
WHERE 1=1
and :a_parm = 1 -- works - only if you don't select :a_parm above
--and :a_parm = 1 and :a_parm = 1 -- FAILS
--and :a_parm = 1 or :a_parm = 2 and :a_parm = 1 -- works - as long as you don't select :a_parm above, else FAILS
--==========


SQL Plus Tests:

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 15 07:37:55 2019

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Fri Nov 15 2019 07:37:06 -06:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>
SQL> VARIABLE a_parm varchar2(30);
SQL>
SQL> begin
2 :a_parm := 1;
3 end;
4 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> with
2 function cleanse(text_in varchar) return varchar -- *** Function to remove non standard Text Characters that Cause Issues in Excel - Illegal Char ***
3 is
4 text_out VARCHAR2(4000);
5 begin
6 -- Saving data with these Char's to Excel - tends to fail with an illegal character error, and it will not let you save the data.
7 -- Removes all 30 Standard ASCII control characters to avoid issues even though not all have been detected in Oracle Data.
8 text_out :=
9 REGEXP_REPLACE(
10 REPLACE(REPLACE(
11 text_in,
12 CHR(1), NULL /* OH (start of heading) */),
13 CHR(2), NULL /* STX (start of text) */),
14 '( ){2,}', ' ') ;
15 return text_out;
16 end;
17 SELECT
18 '** Parms **',
19 :a_parm a_param, -- if you select the :a_parm twice, the code will fail
20 -- :a_parm a_param2,
21 'the end'
22 FROM dual
23 WHERE 1=1
24 and :a_parm = '1';
25 /
with
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 1392
Session ID: 59 Serial number: 32420

SQL>
Resolution

Toad Support will monitor the issue, however currently this would not be a Toad defect or a bug since the issue also happens in SQL Plus.

Did this article solve an issue for you?

[Select Rating]

Request a KB Article

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

Using a With Function in a Select, Toad will not allow to reference Parameters more than once. Errors: "ORA-03113: end-of-file on communication channel" or "Connection is not connected​". (4308093)

Title

Description

Resolution