OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Who call me (all versions from 11g to 19c)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Who call me [message #685092]

Fri, 22 October 2021 02:01

Michel Cadot
Messages: 68761
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:

Code: [Select all] [Show/ hide]

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: 8979
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?

Report message to a moderator

Re: Who call me [message #685094 is a reply to message #685093]

Fri, 22 October 2021 02:58

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

That's an idea, I can put the actual function inside an interface one and make the former only available from the latter.
But this is only possible for 12.2+ versions and unfortunately we have lower ones.

In addition, this is not only a single function but a family one and some of them are pipelined functions which may lead this solution to bad performances (although we have to test as this may be acceptable, the data to crunch are not so numerous).

Thanks for the idea.

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: 68761
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.

Report message to a moderator

Re: Who call me [message #685096 is a reply to message #685095]

Fri, 22 October 2021 11:07

John Watson
Messages: 8979
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,

Code: [Select all] [Show/ hide]

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>

Report message to a moderator

Re: Who call me [message #685097 is a reply to message #685096]

Sat, 23 October 2021 03:42

Michel Cadot
Messages: 68761
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 #685098 is a reply to message #685097]

Sat, 23 October 2021 03:52

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

To complete your example, doing it in a PL/SQL block:

Code: [Select all] [Show/ hide]

SQL> declare
  2    r pls_integer;
  3  begin
  4    select f1 into r from dual;
  5    dbms_output.put_line('select: '||r);
  6    for rec in (select f1 from dual) loop
  7      dbms_output.put_line('cursor loop: '||r);
  8    end loop;
  9  end;
 10  /
select: 1
cursor loop: 1

PL/SQL procedure successfully completed.

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: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Use utl_call_stack:

Code: [Select all] [Show/ hide]

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;
/

Now:

Code: [Select all] [Show/ hide]

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>

SY.

Report message to a moderator

Re: Who call me [message #685100 is a reply to message #685099]

Sat, 23 October 2021 05:52

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Ah, I missed "all versions from 11g to 19c". utl_call_stack was introduced in 12.1.

SY.

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: 68761
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:

Code: [Select all] [Show/ hide]

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:

Code: [Select all] [Show/ hide]

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.

This still seems correct.

Now calling the external procedure from SQL:

Code: [Select all] [Show/ hide]

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

There is no clues of the original SQL statement (which is consistent with the fact we detect this top SQL by the condition "depth=1" in the beginning).

Now summarizing what we found, we can get the new F function which works in almost all cases:

Code: [Select all] [Show/ hide]

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.

And now the case it hurts:

Code: [Select all] [Show/ hide]

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

The result always be "==> F: from SQL".

Assuming the \d+ stuff, we are close, thanks both for your help.

Report message to a moderator

Re: Who call me [message #685108 is a reply to message #685104]

Sat, 23 October 2021 16:31

Solomon Yakobson
Messages: 3305
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:

Code: [Select all] [Show/ hide]

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>

SY.

Report message to a moderator

Re: Who call me [message #685109 is a reply to message #685108]

Sun, 24 October 2021 03:26

Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Thanks Solomon but it has the same flaw than mine with deeper call:

Code: [Select all] [Show/ hide]

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.

The answer should be "from SQL" for all.

With your help, I think I found the one:

Code: [Select all] [Show/ hide]

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.

Report message to a moderator

Previous Topic:	Prefix data into an existing clob
Next Topic:	How to substract 2 varchar2 dates (YYYYMMDDHH24MMSS) in oracle?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jul 09 15:57:28 CDT 2025