Home » SQL & PL/SQL » SQL & PL/SQL » How to Determine First Function Call vs Subsequent Calls (Oracle 10g)
How to Determine First Function Call vs Subsequent Calls [message #292416] Tue, 08 January 2008 22:24 Go to next message
michael2007
Messages: 6
Registered: January 2008
Junior Member
I'm new to PL/SQL- I expect that this is an easy one.

I have a common function that needs to do an insert on only the first call of a session. How can I maintain an indicator in the function to indicate if it has been called before in this session?
I suppose that I could maintain an indicator in a table or I could pass a variable from the calling procedure, but this does not seem very straight forward. Surely this type logic is needed frequently and there is a best technique established.

Any help is greatly appreciated Smile
Re: How to Determine First Function Call vs Subsequent Calls [message #292467 is a reply to message #292416] Wed, 09 January 2008 00:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you would use a package, it would be really easy. A package contains a part that is run only once per session.
Apart from that, you should use packages anyway. It allows for better security and often results in better code.
Re: How to Determine First Function Call vs Subsequent Calls [message #292479 is a reply to message #292416] Wed, 09 January 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can use a variable in a package or in a context that is set at first function call.

Regards
Michel
Re: How to Determine First Function Call vs Subsequent Calls [message #292556 is a reply to message #292467] Wed, 09 January 2008 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First solution:
SQL> create or replace package my_variables is
  2    my_func_already_called boolean := false;
  3  end;
  4  /

Package created.

SQL> create or replace function my_func return integer is
  2  begin
  3    if not my_variables.my_func_already_called then
  4      my_variables.my_func_already_called := true;
  5      dbms_output.put_line ('>>>  my_func FIRST call  <<<');
  6    else
  7      dbms_output.put_line ('>>>  my_func SUBSEQUENT call  <<<');
  8    end if;
  9    return 0;
 10  end;
 11  /

Function created.

SQL> select my_func from dual;
   MY_FUNC
----------
         0

1 row selected.

>>>  my_func FIRST call  <<<

SQL> select my_func from dual;
   MY_FUNC
----------
         0

1 row selected.

>>>  my_func SUBSEQUENT call  <<<

SQL> select my_func from dual;
   MY_FUNC
----------
         0

1 row selected.

>>>  my_func SUBSEQUENT call  <<<

Second solution:
SQL> create or replace package my_pkg
  2  is 
  3    procedure set_my_func_ctx;
  4  end;
  5  /

Package created.

SQL> create or replace package body my_pkg
  2  is 
  3    procedure set_my_func_ctx is
  4    begin
  5      dbms_session.set_context ('MY_CTX','MY_FUNC','DONE');
  6    end;
  7  end;
  8  /

Package body created.

SQL> create or replace context my_ctx using my_pkg;

Context created.

SQL> create or replace function my_func return integer is
  2  begin
  3    if sys_context('MY_CTX','MY_FUNC') is null 
  4       or sys_context('MY_CTX','MY_FUNC') != 'DONE' then
  5      my_pkg.set_my_func_ctx;
  6      dbms_output.put_line ('>>>  my_func FIRST call  <<<');
  7    else
  8      dbms_output.put_line ('>>>  my_func SUBSEQUENT call  <<<');
  9    end if;
 10    return 0;
 11  end;
 12  /

Function created.

SQL> select my_func from dual;
   MY_FUNC
----------
         0

1 row selected.

>>>  my_func FIRST call  <<<

SQL> select my_func from dual;
   MY_FUNC
----------
         0

1 row selected.

>>>  my_func SUBSEQUENT call  <<<

SQL> select my_func from dual;
   MY_FUNC
----------
         0

1 row selected.

>>>  my_func SUBSEQUENT call  <<<

Regards
Michel
icon14.gif  Re: How to Determine First Function Call vs Subsequent Calls [message #293063 is a reply to message #292467] Thu, 10 January 2008 15:03 Go to previous messageGo to next message
michael2007
Messages: 6
Registered: January 2008
Junior Member
Thank You VERY MUCH !
This is a great advancement for my initial PL/SQL career
Re: How to Determine First Function Call vs Subsequent Calls [message #293064 is a reply to message #292556] Thu, 10 January 2008 15:05 Go to previous message
michael2007
Messages: 6
Registered: January 2008
Junior Member
Michel
Thank You for your very helpful examples- You have helped me take my first big step in PL/SQL here at work!

Michael
Previous Topic: Hierarchy, sort of
Next Topic: Find duplicate rows in a table
Goto Forum:
  


Current Time: Sat Dec 03 01:32:36 CST 2016

Total time taken to generate the page: 0.09619 seconds