2

Is there any way to identify when a pl/sql function is executed in SQL Query and when is executed in a procedure or PL/SQL anonymous block? (I don't want to pass any parameter for manual identification)

The main reason I need that is when a function is executed in a SQL query I wouldn't like to raise an exception in case of failure, I would be satisfied just with a returned value NULL. But the same function when is executed in pl/sql script I want to raise exception.

Thank you in advance.

Konstantinos
  • 157
  • 1
  • 14

2 Answers2

1

Why don't you add a parameter to the function to indicate whether or not to throw an exception/return null? When you call the function you can choose the behaviour you need.

create or replace function do_something(p_parameter1      < some_type >
                                       ,p_raise_exception varchar2 default 'Y') return < sometype > is
begin
      --.. calculating .. .
      return result;
exception
   when others then
      if p_raise_exception is 'Y'
      then
         raise;
      else
         return null;
      end if;
end;

Alternatively owa_util seems to provide some functionality you can use.

create or replace function do_something(p_parameter1 < some_type >) return < sometype > is
   l_owner    varchar2(100);
   l_name     varchar2(100);
   l_lineno   number;
   l_caller_t varchar2(100);
begin


   --.. calculating .. .
      return result;
exception
   when others then
      owa_util.who_called_me(l_owner, l_name, l_lineno, l_caller_t)
      -- who called me result seems empty when called from sql.
      if l_owner is not null
      then
         raise;
      else
         return null;
      end if;
end;

Of course : Hiding all errors is bad practise

Rene
  • 10,391
  • 5
  • 33
  • 46
  • Thank you for your answer. But please read again my question is mentioned: (I don't want to pass any parameter for manual identification) – Konstantinos Feb 07 '19 at 09:08
  • 1
    Maybe you find your answer here: https://stackoverflow.com/questions/7273982/get-the-name-of-the-calling-procedure-or-function-in-oracle-pl-sql – Rene Feb 07 '19 at 09:12
  • Added to my answer. – Rene Feb 07 '19 at 09:21
  • 1
    Thank you again Rene! The 2nd part of your answer seems to cover the need for my Oracle version 12.1. As far as errors/exceptions are logged in any case and will be shown to a log management tool (plenty in market). – Konstantinos Feb 07 '19 at 09:40
  • Well, I have noticed that when you have SQL query (select into) in a procedure/function then you get FUNCTION as caller although it's query. Do I miss something? – Konstantinos Feb 07 '19 at 09:58
  • You seem to miss the distinction between calling SQL on it's own and calling it from inside a PLSQL procedure. Once called from inside a PLSQL procedure owa_util will give you that procedure. – Rene Feb 07 '19 at 10:22
1

Well, looking around I found that there is a hack available:

The exception NO_DATA_FOUND isn't propagated when you call PL/SQL in SQL. So you can use this to "return null" instead of get an exception when calling it from SQL:

    create or replace function f 
       return int as
    begin
       raise no_data_found;
       return 1;
    end f;
    /

    select f from dual;
    F     
        null;
    declare
        v integer;
    begin
        v := f;
    end;

Error report -
ORA-01403: no data found
Konstantinos
  • 157
  • 1
  • 14