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>
