| Who call me [message #685092] |
Fri, 22 October 2021 02:01  |
 |
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Here's the background.
I have a PL/SQL function which returns a string.
The result is correct if the caller is PL/SQL (res := f()) and wrong if it is SQL (select f() from dual).
The root is known: it is a SQL engine bug (in all Oracle versions) which incorrectly passes the function parameter.
I can handle that raising an exception if the caller is SQL.
Now the question: how to know if the caller is SQL or PL/SQL?
I tried "dbms_utility.format_call_stack" but it does not give any clue about this:
SQL> create or replace function f (i pls_integer) return pls_integer
2 -- function mimicking the real function
3 is
4 begin
5 dbms_output.put_line(dbms_utility.format_call_stack);
6 return i;
7 end;
8 /
Function created.
SQL> select /* wrong result */ f(1) from dual;
F(1)
----------
1
1 row selected.
----- PL/SQL Call Stack -----
object line object
handle number name
2DDAE76C 5 function MICHEL.F
SQL> declare
2 r pls_integer;
3 begin
4 -- wrong result
5 dbms_output.put_line('Call via SQL:');
6 select f(2) into r from dual;
7 dbms_output.put_line('----------------------------------------');
8 -- correct result
9 dbms_output.put_line('Direct call from PL/SQL:');
10 r := f(3);
11 end;
12 /
Call via SQL:
----- PL/SQL Call Stack -----
object line object
handle number name
2DDAE76C 5 function MICHEL.F
2B6CFBA0 6 anonymous block
----------------------------------------
Direct call from PL/SQL:
----- PL/SQL Call Stack -----
object line object
handle number name
2DDAE76C 5 function MICHEL.F
2B6CFBA0 10 anonymous block
PL/SQL procedure successfully completed.
Does anyone know a way to know, from the function itself, what kind is the caller?
[Updated on: Fri, 22 October 2021 02:01] Report message to a moderator |
|
|
|
| Re: Who call me [message #685093 is a reply to message #685092] |
Fri, 22 October 2021 02:17  |
John Watson
Messages: 8927
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
|
Can you sneak up on the problem by making it impossible to call the function from SQL by creating it with an ACCESSIBLE BY clause?
|
|
|
|
|
|
| Re: Who call me [message #685095 is a reply to message #685093] |
Fri, 22 October 2021 10:39 |
|
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Unfortunately this option ("ACCESSIBLE BY" clause) has been refused.
The main reason is
- as this happens in few cases that will be clearly identified by tests
- as it will take time to update the applications to use the pure PL/SQL way to retrieve the data
- as update is not possible for some applications that have been externally developed and we have not the source code
- the requirement is to raise an exception explaining the reason of the error (instead of the obscure "insufficient privilege").
(* some applications are still working with a pre-12.2 db.)
To clarify the problem, the "wrong result" I mentioned is in the best cases (from the administration, application or db, point of view, not from the user one), in many cases we get ORA-00600 or ORA-07445 inside an Oracle standard procedure, I think some kind of buffer is too small and/or data are incorrectly move into it).
So we have to trap these specific cases before calling the offending Oracle procedures (there are more than one), we can't forbid things that worked till now and still work unless the new complex data come into play.
So the cases are the following ones:
- Original data, PL/SQL access -> let go
- Original data, SQL access -> let go
- New data, PL/SQL access -> let go
- New data, SQL access -> raise exception
The offending new data will be (and are already partly) determined, no problem there.
Remains the problem to determine SQL or PL/SQL access.
|
|
|
|
| Re: Who call me [message #685096 is a reply to message #685095] |
Fri, 22 October 2021 11:07 |
John Watson
Messages: 8927
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
If you can edit the function(s), how about forcing an error if calling it from SQL? Eg,orclz> create or replace function f1 return number as begin
2 update emp set sal=sal where rownum=1;
3 return 0;
4 exception when others then return 1;
5 end;
6 /
Function created.
orclz> select f1 from dual;
F1
---------------
1
orclz> var n number
orclz> exec :n:=f1
PL/SQL procedure successfully completed.
orclz> print n
N
---------------
0
orclz> |
|
|
|
| Re: Who call me [message #685097 is a reply to message #685096] |
Sat, 23 October 2021 03:42 |
|
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Sorry, I didn't see your post yesterday.
The caller may not be easily modified but the called functions (the ones that raise the error) is in our hands and so we can do what we want.
This is a pretty good answer indeed.
You can even change "rownum=1" to "rownum=0" and then make no rows updated and even start no transactions and allocate no rollback entries! It is completely transparent.
Some users have no tables or views to update but in all Oracle versions there are standard tables and views which can be updated by "public", we just have to find one that has no triggers to prevent from any side effect, SYSTEM.OL$ for instance.
You get me out of the dead end I was, I can now go forward developing the function using this solution for the moment, or definitively if we don't find a more direct way to answer the question.
Many thanks.
[Updated on: Sat, 23 October 2021 03:43] Report message to a moderator |
|
|
|
|
|
| Re: Who call me [message #685099 is a reply to message #685092] |
Sat, 23 October 2021 05:46 |
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Use utl_call_stack:
create or replace
function f(
p_i pls_integer
)
return pls_integer
-- function mimicking the real function
is
begin
if utl_call_stack.owner(2) is null
and
utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(2)) != '__anonymous_block'
then
dbms_output.put_line('Function is called from SQL.');
else
dbms_output.put_line('Function is called from PL/SQL.');
end if;
return p_i;
end;
/
declare
r pls_integer;
begin
-- wrong result
dbms_output.put_line('Call via SQL:');
select f(2) into r from dual;
dbms_output.put_line('----------------------------------------');
-- correct result
dbms_output.put_line('Direct call from PL/SQL:');
r := f(3);
end;
/
Call via SQL:
Function is called from SQL.
----------------------------------------
Direct call from PL/SQL:
Function is called from PL/SQL.
PL/SQL procedure successfully completed.
SQL>
|
|
|
|
|
|
| Re: Who call me [message #685104 is a reply to message #685100] |
Sat, 23 October 2021 12:06 |
|
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Thanks Solomon, a solution for 12c+ with John's workaround for 11g would be good for me.
I modified my function to display the stack using utl_call_stack and test it in different cases to know what it gives:
MIKJ3DB1> create or replace function f (p_title varchar2) return varchar2
2 is
3 d pls_integer;
4 begin
5 dbms_output.new_line;
6 dbms_output.put_line(p_title);
7 dbms_output.new_line;
8 $IF DBMS_DB_VERSION.VERSION < 12 $THEN
9 dbms_output.put_line(dbms_utility.format_call_stack);
10 $ELSE
11 d := utl_call_stack.dynamic_depth;
12 dbms_output.put_line('call depth: '||d);
13 dbms_output.put_line(' depth line object');
14 dbms_output.put_line(' number name');
15 for i in 1..d loop
16 dbms_output.put_line(
17 to_char(i,'99999')||' '
18 ||nvl(to_char(utl_call_stack.unit_line(i),'99999'),' ')||' '
19 ||case when utl_call_stack.owner(i) is not null then utl_call_stack.owner(i)||'.' end
20 ||utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(i))
21 );
22 end loop;
23 $END
24 return null;
25 end f;
26 /
Function created.
MIKJ3DB1> select /* wrong result */ f('from SQL:') r from dual;
R
------------------------------------------------------------------------------------------------------------------------
1 row selected.
from SQL:
call depth: 1
depth line object
number name
1 16 MICHEL.F
MIKJ3DB1> declare
2 r varchar2(100);
3 begin
4 -- wrong result
5 select f('Call via SQL:') into r from dual;
6 dbms_output.put_line(r);
7 dbms_output.put_line('----------------------------------------');
8 -- wrong result
9 for rec in (select f('Call via cursor loop:') r from dual) loop
10 dbms_output.put_line(rec.r);
11 end loop;
12 dbms_output.put_line('----------------------------------------');
13 -- correct result
14 r := f('Direct call from PL/SQL:');
15 dbms_output.put_line(r);
16 end;
17 /
Call via SQL:
call depth: 3
depth line object
number name
1 16 MICHEL.F
2 14649745530676321854
3 5 __anonymous_block
----------------------------------------
Call via cursor loop:
call depth: 3
depth line object
number name
1 16 MICHEL.F
2 14649745530676321854
3 9 __anonymous_block
----------------------------------------
Direct call from PL/SQL:
call depth: 2
depth line object
number name
1 16 MICHEL.F
2 14 __anonymous_block
PL/SQL procedure successfully completed.
OK, it seems that we have some SQL in the execution path if either depth is 1 or there is such object like '14649745530676321854' with no owners and no line numbers.
Let's try a step deeper calling F from procedures:
MIKJ3DB1> create or replace function f1 return pls_integer is
2 r varchar2(100);
3 procedure p2 is
4 r varchar2(100);
5 begin
6 select f('From P2 SQL:') into r from dual;
7 dbms_output.put_line(r);
8 r := f('From P2 PL/SQL:');
9 dbms_output.put_line(r);
10 end p2;
11 begin
12 select f('From F1 SQL:') into r from dual;
13 dbms_output.put_line(r);
14 r := f('From F1 PL/SQL:');
15 dbms_output.put_line(r);
16 p2;
17 return to_number(null);
18 end f1;
19 /
Function created.
MIKJ3DB1> var r number
MIKJ3DB1> exec :r := f1;
From F1 SQL:
call depth: 4
depth line object
number name
1 16 MICHEL.F
2 7404717450434460656
3 12 MICHEL.F1
4 1 __anonymous_block
From F1 PL/SQL:
call depth: 3
depth line object
number name
1 16 MICHEL.F
2 14 MICHEL.F1
3 1 __anonymous_block
From P2 SQL:
call depth: 5
depth line object
number name
1 16 MICHEL.F
2 7404717450434460656
3 6 MICHEL.F1.P2
4 16 MICHEL.F1
5 1 __anonymous_block
From P2 PL/SQL:
call depth: 4
depth line object
number name
1 16 MICHEL.F
2 8 MICHEL.F1.P2
3 16 MICHEL.F1
4 1 __anonymous_block
PL/SQL procedure successfully completed.
Now calling the external procedure from SQL:
MIKJ3DB1> select f1 from dual;
F1
----------
1 row selected.
From F1 SQL:
call depth: 3
depth line object
number name
1 16 MICHEL.F
2 10643177831707482362
3 12 MICHEL.F1
From F1 PL/SQL:
call depth: 2
depth line object
number name
1 16 MICHEL.F
2 14 MICHEL.F1
From P2 SQL:
call depth: 4
depth line object
number name
1 16 MICHEL.F
2 10643177831707482362
3 6 MICHEL.F1.P2
4 16 MICHEL.F1
From P2 PL/SQL:
call depth: 3
depth line object
number name
1 16 MICHEL.F
2 8 MICHEL.F1.P2
3 16 MICHEL.F1
Now summarizing what we found, we can get the new F function which works in almost all cases:
MIKJ3DB1> create or replace function f (p_title varchar2) return varchar2
2 is
3 d pls_integer;
4 begin
5 dbms_output.new_line;
6 dbms_output.put_line(p_title);
7 dbms_output.new_line;
8 d := utl_call_stack.dynamic_depth;
9 if d = 1 then return '==> F: from SQL';
10 else
11 for i in 1..d loop
12 if utl_call_stack.unit_line(i) is null
13 and utl_call_stack.owner(i) is null
14 and regexp_like(utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(i)),
15 '^\d+$')
16 then
17 return '==> F: from SQL';
18 end if;
19 end loop;
20 end if;
21 return '==> F: from PL/SQL';
22 end;
23 /
Function created.
MIKJ3DB1> select /* wrong result */ f('from SQL:') r from dual;
R
------------------------------------------------------------------------------------------------------------------------
==> F: from SQL
1 row selected.
from SQL:
MIKJ3DB1> declare
2 r varchar2(100);
3 begin
4 -- wrong result
5 select f('Call via SQL:') into r from dual;
6 dbms_output.put_line(r);
7 dbms_output.put_line('----------------------------------------');
8 -- wrong result
9 for rec in (select f('Call via cursor loop:') r from dual) loop
10 dbms_output.put_line(rec.r);
11 end loop;
12 dbms_output.put_line('----------------------------------------');
13 -- correct result
14 r := f('Direct call from PL/SQL:');
15 dbms_output.put_line(r);
16 end;
17 /
Call via SQL:
==> F: from SQL
----------------------------------------
Call via cursor loop:
==> F: from SQL
----------------------------------------
Direct call from PL/SQL:
==> F: from PL/SQL
PL/SQL procedure successfully completed.
MIKJ3DB1> exec :r := f1;
From F1 SQL:
==> F: from SQL
From F1 PL/SQL:
==> F: from PL/SQL
From P2 SQL:
==> F: from SQL
From P2 PL/SQL:
==> F: from PL/SQL
PL/SQL procedure successfully completed.
MIKJ3DB1> select f1 from dual;
F1
----------
1 row selected.
From F1 SQL:
==> F: from SQL
From F1 PL/SQL:
==> F: from PL/SQL
From P2 SQL:
==> F: from SQL
From P2 PL/SQL:
==> F: from PL/SQL
Assuming the \d+ stuff, we are close, thanks both for your help.
|
|
|
|
| Re: Who call me [message #685108 is a reply to message #685104] |
Sat, 23 October 2021 16:31 |
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
I didn't test when function is referenced from SQL statement that is called directly in SQL. In such case call stack depth is 1:
SQL> create or replace
2 function f(
3 p_i pls_integer
4 )
5 return pls_integer
6 -- function mimicking the real function
7 is
8 begin
9 if utl_call_stack.dynamic_depth = 1
10 then
11 dbms_output.put_line('Function is called from SQL.');
12 elsif utl_call_stack.owner(2) is null
13 and
14 utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(2)) != '__anonymous_block'
15 then
16 dbms_output.put_line('Function is called from SQL.');
17 else
18 dbms_output.put_line('Function is called from PL/SQL.');
19 end if;
20 return p_i;
21 end;
22 /
Function created.
SQL> declare
2 r pls_integer;
3 begin
4 -- wrong result
5 dbms_output.put_line('Call via SQL:');
6 select f(2) into r from dual;
7 dbms_output.put_line('----------------------------------------');
8 -- correct result
9 dbms_output.put_line('Direct call from PL/SQL:');
10 r := f(3);
11 end;
12 /
Call via SQL:
Function is called from SQL.
----------------------------------------
Direct call from PL/SQL:
Function is called from PL/SQL.
PL/SQL procedure successfully completed.
SQL> select f(5) from dual
2 /
F(5)
----------
5
Function is called from SQL.
SQL> with function ff(
2 p_i pls_integer
3 )
4 return pls_integer
5 -- function mimicking the real function
6 is
7 begin
8 if utl_call_stack.dynamic_depth = 1
9 then
10 dbms_output.put_line('Function is called from SQL.');
11 elsif utl_call_stack.owner(2) is null
12 and
13 utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(2)) != '__anonymous_block'
14 then
15 dbms_output.put_line('Function is called from SQL.');
16 else
17 dbms_output.put_line('Function is called from PL/SQL.');
18 end if;
19 return p_i;
20 end;
21 select ff(22) from dual
22 /
FF(22)
----------
22
Function is called from SQL.
SQL>
|
|
|
|
| Re: Who call me [message #685109 is a reply to message #685108] |
Sun, 24 October 2021 03:26 |
|
Michel Cadot
Messages: 68634
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Thanks Solomon but it has the same flaw than mine with deeper call:
MIKJ3DB1> create or replace
2 function f(
3 p_i pls_integer
4 )
5 return pls_integer
6 -- function mimicking the real function
7 is
8 begin
9 if utl_call_stack.dynamic_depth = 1
10 then
11 dbms_output.put_line('Function is called from SQL.');
12 elsif utl_call_stack.owner(2) is null
13 and
14 utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(2)) != '__anonymous_block'
15 then
16 dbms_output.put_line('Function is called from SQL.');
17 else
18 dbms_output.put_line('Function is called from PL/SQL.');
19 end if;
20 return p_i;
21 end;
22 /
Function created.
MIKJ3DB1> create or replace function f1 return pls_integer is
2 r varchar2(100);
3 procedure p2 is
4 r varchar2(100);
5 begin
6 dbms_output.put_line('From P2 SQL:');
7 select f(5) into r from dual;
8 dbms_output.put_line('From P2 PL/SQL:');
9 r := f(6);
10 end p2;
11 begin
12 dbms_output.put_line('From F1 SQL:');
13 select f(7) into r from dual;
14 dbms_output.put_line('From F1 PL/SQL:');
15 r := f(8);
16 p2;
17 return to_number(null);
18 end f1;
19 /
Function created.
MIKJ3DB1> select f1 from dual;
F1
----------
1 row selected.
From F1 SQL:
Function is called from SQL.
From F1 PL/SQL:
Function is called from PL/SQL.
From P2 SQL:
Function is called from SQL.
From P2 PL/SQL:
Function is called from PL/SQL.
With your help, I think I found the one:
MIKJ3DB1> create or replace function f (p_title varchar2) return varchar2
2 is
3 d pls_integer;
4 begin
5 dbms_output.new_line;
6 dbms_output.put_line(p_title);
7 dbms_output.new_line;
8 d := utl_call_stack.dynamic_depth;
9 if utl_call_stack.owner(d) is not null
10 or utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(d))
11 != '__anonymous_block'
12 then
13 return '==> F: from SQL';
14 else
15 for i in 2..d loop
16 if utl_call_stack.unit_line(i) is null
17 and -- A subprogram with no owner is a SQL call
18 utl_call_stack.owner(i) is null
19 and utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(i))
20 != '__anonymous_block'
21 then
22 return '==> F: from SQL';
23 end if;
24 end loop;
25 end if;
26 return '==> F: from PL/SQL';
27 end;
28 /
Function created.
MIKJ3DB1> select /* wrong result */ f('from SQL:') r from dual;
R
--------------------------------------------------------------
==> F: from SQL
1 row selected.
from SQL:
MIKJ3DB1> declare
2 r varchar2(100);
3 begin
4 -- wrong result
5 select f('Call via SQL:') into r from dual;
6 dbms_output.put_line(r);
7 dbms_output.put_line('----------------------------------------');
8 -- wrong result
9 for rec in (select f('Call via cursor loop:') r from dual) loop
10 dbms_output.put_line(rec.r);
11 end loop;
12 dbms_output.put_line('----------------------------------------');
13 -- correct result
14 r := f('Direct call from PL/SQL:');
15 dbms_output.put_line(r);
16 end;
17 /
Call via SQL:
==> F: from SQL
----------------------------------------
Call via cursor loop:
==> F: from SQL
----------------------------------------
Direct call from PL/SQL:
==> F: from PL/SQL
PL/SQL procedure successfully completed.
MIKJ3DB1> create or replace function f1 return pls_integer is
2 r varchar2(100);
3 procedure p2 is
4 r varchar2(100);
5 begin
6 select f('From P2 SQL:') into r from dual;
7 dbms_output.put_line(r);
8 for rec in (select f('From P2 cursor loop:') r from dual) loop
9 dbms_output.put_line(rec.r);
10 end loop;
11 r := f('From P2 PL/SQL:');
12 dbms_output.put_line(r);
13 end p2;
14 begin
15 select f('From F1 SQL:') into r from dual;
16 dbms_output.put_line(r);
17 for rec in (select f('From F1 cursor loop:') r from dual) loop
18 dbms_output.put_line(rec.r);
19 end loop;
20 r := f('From F1 PL/SQL:');
21 dbms_output.put_line(r);
22 p2;
23 return to_number(null);
24 end f1;
25 /
Function created.
MIKJ3DB1> exec :r := f1;
From F1 SQL:
==> F: from SQL
From F1 cursor loop:
==> F: from SQL
From F1 PL/SQL:
==> F: from PL/SQL
From P2 SQL:
==> F: from SQL
From P2 cursor loop:
==> F: from SQL
From P2 PL/SQL:
==> F: from PL/SQL
PL/SQL procedure successfully completed.
MIKJ3DB1> select f1 from dual;
F1
----------
1 row selected.
From F1 SQL:
==> F: from SQL
From F1 cursor loop:
==> F: from SQL
From F1 PL/SQL:
==> F: from SQL
From P2 SQL:
==> F: from SQL
From P2 cursor loop:
==> F: from SQL
From P2 PL/SQL:
==> F: from SQL
MIKJ3DB1> exec select f1 into :r from dual;
From F1 SQL:
==> F: from SQL
From F1 cursor loop:
==> F: from SQL
From F1 PL/SQL:
==> F: from SQL
From P2 SQL:
==> F: from SQL
From P2 cursor loop:
==> F: from SQL
From P2 PL/SQL:
==> F: from SQL
PL/SQL procedure successfully completed. |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
