Re: count PL/SQL function calls

One way, is to turn your function into a package like this:-

create or replace package countit

is

  function my_func (i_p1 in varchar2)
    return integer;

  procedure display_it;

  procedure reset_it;

end countit;
/

create or replace package body countit

is

  v_call_count integer default 0;

  function my_func (i_p1 in varchar2)
    return integer
  is
  begin
    v_call_count := v_call_count + 1;

• do whatever it is you want todo

        return 1;
  end my_func;

  procedure display_it
  is
  begin
    dbms_output.put_line ('Has been called '||to_char(v_call_count));   end display_it;

  procedure reset_it
  is
  begin
    v_call_count := 0;
  end reset_it;

end countit;
/

  1 select * from
  2 (
  3 select 1 a
  4 from dual
  5 connect by level <= 5
  6 )
  7* where a = countit.my_func (1)
SQL> /          A

         1
         1
         1
         1
         1

SQL> set serveroutput on
SQL> exec countit.display_it;
Has been called 5

PL/SQL procedure successfully completed.

SQL> exec countit.reset_it;

PL/SQL procedure successfully completed.

SQL> exec countit.display_it;
Has been called 0

PL/SQL procedure successfully completed.

SQL> I am going to be bold and guess you are wanting to count the times the function has been called as you are trying to reduce the number of function calls to make things faster etc. If I change my query about slightly (to turn the subquery into a scalar sub query) then Oracle caches the function call results, which could be a good win for you:-

  1 select * from
  2 (
  3 select 1 a
  4 from dual
  5 connect by level <= 5
  6 )
  7* where a = (select countit.my_func (1) from dual) SQL> /          A

         1
         1
         1
         1
         1

SQL> exec countit.display_it
Has been called 1

PL/SQL procedure successfully completed. Received on Wed Aug 10 2005 - 06:46:16 CDT