PRAGMA SERIALLY_REUSABLE

PRAGMA SERIALLY_REUSABLE tells to the compiler that the package’s variables are needed for a single use. After this single use Oracle can free the associated memory. It’s really useful to save memory when a packages uses large temporary space just once in the session.
Let’s see an example.

Let’s define a package with a single numeric variable “var” not initialized:

1 2 3 4

SQL> create or replace package pack is   2  var number;   3  end;   4  /

If we assign a value to var, this will preserve that value for the whole session:

1 2 3 4 5 6 7

SQL> begin   2  pack.var := 1;   3  end;   4  /   SQL> exec dbms_output.put_line('Var='||pack.var); Var=1

If we use the PRAGMA SERIALLY_REUSABLE, var will preserve the value just inside the program that initializes it, but is null in the following calls:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> create or replace package pack is   2  PRAGMA SERIALLY_REUSABLE;   3  var number;   4  end;   5  /   SQL> begin   2  pack.var := 1;   3  dbms_output.put_line('Var='||pack.var);   4  end;   5  / Var=1   SQL> exec dbms_output.put_line('Var='||pack.var); Var=

PRAGMA SERIALLY_REUSABLE is a way to change the default behavior of package variables that is as useful as heavy for memory.