Some time ago I experienced a strange problem that after some research was caused by Oracle ignoring named parameter value assignment. We have registered this problem as a bug which has not yet been published by Oracle. Tests which I have done show that the problem occurs on database version 11.2.0.4 and as I will try to show below also on 12.2.0.1. Finally I have done my tests on Oracle Live SQL and confirmed the bug also occurs on the most recent Oracle Database version 18.1.0.0.0.
Let us start the tests with creating a simple PL/SQL function.
The function has two numeric parameters and depending on the value passed to the first parameter the function returns either 1 or 2. For debugging purposes I also log the value passed to each parameter.
Now I will create two views which use this function in the select list.
So in the first view (V1) I call function F1 using named parameter notation and on purpose in the reverse order (P2 first). The view has two columns, the first column is simply the called PL/SQL function and in the second column I add 1 to the called PL/SQL function. The second view V2 selects the first column from the first view V1 and also has a second column where I add 1 to the first column of view V1. Basically the result from both views should be the same: column "f1" should be 1 and column "f1_plus_1" should equal to 2.
So let us see if this is the case.
So they are NOT equal. That was rather unexpected. And it gets even stranger.
Instead of select *, selecting both columns from view V2 makes no difference. We still get the wrong result. But if we select both columns separately we get the correct result. So this is really strange, a query result depends on the combination of columns in the select list!
Let us have a look at the debug information I added to function F1.
From the debug information we can see what the actual parameter values for function F1 were. The values for parameters "p1" and "p2" are logged twice because both columns in both views call function F1. The first select from view V1 shows the expected values. The second select from view V2 shows the problem. For the first column "F1" the actual values for the parameter "p1" and "p2" are reversed. The parameter values for the second column are correct.
Selecting just the first "F1" column from view V2 shows that in this case the actual values for the function parameters are correct.
Now let us see if the parameter order is really the cause of this bug.
I have recreated view V1 and I only changed the order of the parameters in the function call to F1. So the actual values passed to each parameter are the same.
Now run the same queries on V1 and V2.
Now the results from both queries are equal.
Using positional notation.
Same correct result.
After this I have tested if parameter datatype makes a difference. So I have tested a combination of NUMBER and VARCHAR2 and both parameters VARCHAR2. Below are the results form these tests.
First the NUMBER VARCHAR2 combination.
So this makes no difference. The named parameter order is ignored which can be seen from the ORA-01722 error which occurs when in this case the value "a" is assigned to numeric parameter "p1".
The all VARCHAR2 combination
This makes no difference either. Both tests show that the bug occurs no matter what combination of parameter datatypes I use. Well it holds for at least the NUMBER VARCHAR2 combinations.
We can conclude that caution is advised when using a PL/SQL function in a query using named notation in a different parameter order from the order in which the PL/SQL function is defined.
Let us start the tests with creating a simple PL/SQL function.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
WHEN
-------------------
02-04-2018 14:51:37
create or replace function f1
(p1 in number
,p2 in number
)
return number
is
begin
dbms_output.put_line('p1: '||p1);
dbms_output.put_line('p2: '||p2);
--
return case p1
when 1 then 1
else 2
end
;
end;
/
Function F1 compiled
The function has two numeric parameters and depending on the value passed to the first parameter the function returns either 1 or 2. For debugging purposes I also log the value passed to each parameter.
Now I will create two views which use this function in the select list.
create or replace view v1 as select f1(p2 => 0, p1 => 1) f1 , 1 + f1(p2 => 0, p1 => 1) f1_plus_1 from dual ; View V1 created. create or replace view v2 as select f1 , 1 + f1 f1_plus_1 from v1 ; View V2 created.
So in the first view (V1) I call function F1 using named parameter notation and on purpose in the reverse order (P2 first). The view has two columns, the first column is simply the called PL/SQL function and in the second column I add 1 to the called PL/SQL function. The second view V2 selects the first column from the first view V1 and also has a second column where I add 1 to the first column of view V1. Basically the result from both views should be the same: column "f1" should be 1 and column "f1_plus_1" should equal to 2.
So let us see if this is the case.
select * from v1
;
F1 F1_PLUS_1
---------- ----------
1 2
select * from v2
;
F1 F1_PLUS_1
---------- ----------
2 2
So they are NOT equal. That was rather unexpected. And it gets even stranger.
select f1, f1_plus_1 from v2
;
F1 F1_PLUS_1
---------- ----------
2 2
select f1 from v2
;
F1
----------
1
select f1_plus_1 from v2
;
F1_PLUS_1
----------
2
Instead of select *, selecting both columns from view V2 makes no difference. We still get the wrong result. But if we select both columns separately we get the correct result. So this is really strange, a query result depends on the combination of columns in the select list!
Let us have a look at the debug information I added to function F1.
set serveroutput on size unl
select * from v1
;
F1 F1_PLUS_1
---------- ----------
1 2
p1: 1
p2: 0
p1: 1
p2: 0
select * from v2
;
F1 F1_PLUS_1
---------- ----------
2 2
p1: 0
p2: 1
p1: 1
p2: 0
From the debug information we can see what the actual parameter values for function F1 were. The values for parameters "p1" and "p2" are logged twice because both columns in both views call function F1. The first select from view V1 shows the expected values. The second select from view V2 shows the problem. For the first column "F1" the actual values for the parameter "p1" and "p2" are reversed. The parameter values for the second column are correct.
Selecting just the first "F1" column from view V2 shows that in this case the actual values for the function parameters are correct.
select f1 from v2
;
F1
----------
1
p1: 1
p2: 0
Now let us see if the parameter order is really the cause of this bug.
create or replace view v1 as select f1(p1 => 1, p2 => 0) f1 , 1 + f1(p1 => 1, p2 => 0) f1_plus_1 from dual ; View V1 created.
I have recreated view V1 and I only changed the order of the parameters in the function call to F1. So the actual values passed to each parameter are the same.
Now run the same queries on V1 and V2.
select * from v1
;
F1 F1_PLUS_1
---------- ----------
1 2
select * from v2
;
F1 F1_PLUS_1
---------- ----------
1 2
Now the results from both queries are equal.
Using positional notation.
create or replace view v1
as
select f1(1,0) f1
, 1 + f1(1,0) f1_plus_1
from dual
;
View V1 created.
select * from v1
;
F1 F1_PLUS_1
---------- ----------
1 2
select * from v2
;
F1 F1_PLUS_1
---------- ----------
1 2
Same correct result.
After this I have tested if parameter datatype makes a difference. So I have tested a combination of NUMBER and VARCHAR2 and both parameters VARCHAR2. Below are the results form these tests.
First the NUMBER VARCHAR2 combination.
create or replace function f1
(p1 in number
,p2 in varchar2
)
return number
is
begin
dbms_output.put_line('p1: '||p1);
dbms_output.put_line('p2: '||p2);
--
return case p1
when 1 then 1
else 2
end
;
end;
/
Function F1 compiled
create or replace view v1
as
select f1(p2 => 'a', p1 => 1) f1
, 1 + f1(p2 => 'a', p1 => 1) f1_plus_1
from dual
;
View V1 created.
create or replace view v2
as
select f1
, 1 + f1 f1_plus_1
from v1
;
View V2 created.
select * from v1
;
F1 F1_PLUS_1
---------- ----------
1 2
p1: 1
p2: a
p1: 1
p2: a
select * from v2
;
Error report -
ORA-01722: Ongeldig getal
select f1, f1_plus_1 from v2
;
Error report -
ORA-01722: Ongeldig getal
select f1 from v2
;
F1
----------
1
p1: 1
p2: a
select f1_plus_1 from v2
;
F1_PLUS_1
----------
2
p1: 1
p2: a
So this makes no difference. The named parameter order is ignored which can be seen from the ORA-01722 error which occurs when in this case the value "a" is assigned to numeric parameter "p1".
The all VARCHAR2 combination
create or replace function f1
(p1 in varchar2
,p2 in varchar2
)
return number
is
begin
dbms_output.put_line('p1: '||p1);
dbms_output.put_line('p2: '||p2);
--
return case p1
when 'b' then 1
else 2
end
;
end;
/
Function F1 compiled
create or replace view v1
as
select f1(p2 => 'a', p1 => 'b') f1
, 1 + f1(p2 => 'a', p1 => 'b') f1_plus_1
from dual
;
View V1 created.
create or replace view v2
as
select f1
, 1 + f1 f1_plus_1
from v1
;
View V2 created.
select * from v1
;
F1 F1_PLUS_1
---------- ----------
1 2
p1: b
p2: a
p1: b
p2: a
select * from v2
;
F1 F1_PLUS_1
---------- ----------
2 2
p1: a
p2: b
p1: b
p2: a
select f1, f1_plus_1 from v2
;
F1 F1_PLUS_1
---------- ----------
2 2
p1: a
p2: b
p1: b
p2: a
select f1 from v2
;
F1
----------
1
p1: b
p2: a
select f1_plus_1 from v2
;
F1_PLUS_1
----------
2
p1: b
p2: a
This makes no difference either. Both tests show that the bug occurs no matter what combination of parameter datatypes I use. Well it holds for at least the NUMBER VARCHAR2 combinations.
We can conclude that caution is advised when using a PL/SQL function in a query using named notation in a different parameter order from the order in which the PL/SQL function is defined.
Comments
Post a Comment