Skip to Main Content
  • Questions
  • How can I find out from within a stored function whether it was called from a query?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Alex.

Asked: November 25, 2020 - 5:32 pm UTC

Last updated: November 30, 2020 - 2:54 am UTC

Version: 12c

Viewed 100+ times

You Asked

Hi

In a stored function I would like to detect whether it was called from an sql query. If not then my function will perform DML. Otherwise it will save function parameters into a buffer - a package variable. The DML execution will be deferred until after the query. Please could you let me know what would be a good, fast, reliable way of determining whether my function was called from a query?

Please note that I would not like to run an autonomous transaction from within my query. This is because the decision whether to go ahead with the deferred DML cannot be made until after the query is completed. I would very much like finding the answer to the original puzzle rather than finding a workaround.

Kind Regards
Alex

and we said...

That strikes me as a very very risky proposition. You can never guarantee how many times we will opt to run a function in a SQL statement. For example

SQL> create table t as select * from dba_objects
  2  where object_id is not null;

Table created.

SQL>
SQL> create or replace
  2  function my_function(p_param int) return number as
  3    x int;
  4  begin
  5    x := 1/0;
  6    return p_param+1;
  7  end;
  8  /

Function created.


So obviously if I call this function, I will get an error because I'm dividing by zero, thus


SQL> select object_id, my_function(object_id)
  2  from t;
select object_id, my_function(object_id)
                  *
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "MCDONAC.MY_FUNCTION", line 4



But what about this one then?

SQL> select count(*)
  2  from
  3  ( select object_id, my_function(object_id)
  4    from t
  5  );

  COUNT(*)
----------
     87308


The database is free to re-arrange the SQL in any way it chooses as long as we observe the correctness of results.

You could go digging around into dbms_utility.format_call_stack, but I'd strongly suggest a rethink of your approach.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.