PL/SQL Package Table

Question

I need to maintain state in a PL/SQL application. It needs to hold a small table during the session.

As I understand it, this is accomplished via a package variable, but I don't know how to create a table as a package variable.

Anyone explain how to do this or alternatives?

Expansion of Problem I have a WHERE IN condition that I must populate in a cursor at run time. Since to my knowledge I can only populate it with a hard coded literal or a SELECT I need to hold all the IN's that are selected by the user during the session.

Answer 1

If you need to maintain state in your application, you can do this in a global table just as easily as a package-level table. The difference would be that the package table will not be accessible outside of the package.

From O'Reilly, we see that a table defined in the PACKAGE specification is considered global, but a table declared in the PACKAGE BODY specification is a package-level var.

Answer 2

You define a variable with a table type in the package. If you want the state accessible from outside the package it is defined in the header - if you want it private then you define it in the body.

If you want to initialise the variable the first time the package is accessed then you use an initialisation block at the bottom of the package.

Some tips:

• Be careful with exception handling when using initialisation blocks. If an exception is raised you need to ensure you use clear error messages or log messages. A maintenance programmer troubleshooting an issue may jump straight to the called method to troubleshoot rather than examining the implicit initialisation block.
• Oracle can shuffle packages in and out of memory under various conditions at which point the package level variable is cleared. Ensure your state is required just for that session and that the session is fairly short lived (i.e not around for days). If you need more reliable persistence then use a physical table, not a package variable.
• I often find a problem that starts out as being suitable for a package-level table usually grows more complex over time. It might be better to use a real table from an extensibility point of view. It depends on whether you are looking for a short-term solution for a simple problem or a long-term solution for a mission-ritical problem or a problem that will evolve over time.

Example using a simple "name-value" mapping table:

create or replace package bob as
  procedure do_stuff;
end bob;

create or replace package body bob as
  type my_table is table of varchar2(100) index by varchar2(100);
  my_variable my_table;

procedure do_stuff
begin
  --do stuff to my_variable
end;

begin
  --initialise my_variable
end bob;

Answer 3

Here is the Oracle Doc on Global Temp Tables

Answer 4

A GLOBAL TEMP TABLE set to on Commit Preserve Rows that is setup as Key/Value can help in using the values throughtout the session not just within the package. SQL queries can then be built around IN or EXIST clauses with the table using access by the key.