Global variables in Oracle...

[JScannell]

I have two functions that will return the most recent date and most recent price respevtively. The query that calls these functions used to be a group by on the unique ID for the function. So the functions were only called once per unique ID.

The query has had to change to a detail query and calling the functions once per detail row has some huge overhead. What I would like to do is have global variables in the function to save the Unique ID, most recent date, and most recent price. If the Unique ID changes, I do the lookup and return the value and save it into the global value so on the next record when the unique ID is the same, I can simply return the saved date and price.

The query is sorted by Unique ID.

Anybody know of a way to do this?

Thanks in advance,
Jerry

Jerry Scannell

 

[SantaMufasa]

Yes, Jerry, there are a couple of ways to achieve this behaviour:

1) If you are using SQL*Plus, you can define a "bind variable" whose definition and most recent value persists until you either change the value or you quit your session:

SQL> var x number
SQL> exec :x := 10

PL/SQL procedure successfully completed.

SQL> select :x from dual;

        :X
----------
        10

2) Any variables defined in the package header portion of a PL/SQL package persist throughout the life of your session, as well:

SQL> create or replace package jerry is
  2      current_id      number;
  3      latest_date     date;
  4      latest_price    number;
  5  end;
  6  /

Package created.

SQL> exec jerry.latest_date := sysdate;

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> exec dbms_output.put_line(jerry.latest_date)
05-SEP-07

PL/SQL procedure successfully completed.

So, let us know if you like either of these methods.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]