Home » SQL & PL/SQL » SQL & PL/SQL » Who call me (all versions from 11g to 19c)
 Who call me [message #685092] |
Fri, 22 October 2021 02:01  |
 |
Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #685095 is a reply to message #685093] |
Fri, 22 October 2021 10:39  |
|
Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #685097 is a reply to message #685096] |
Sat, 23 October 2021 03:42 |
|
Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #685104 is a reply to message #685100] |
Sat, 23 October 2021 12:06 |
|
Michel Cadot
Messages: 68763
Registered: March 2007
Location: Saint-Maur, France, https...
|
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.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Jul 24 07:45:53 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
