PRAGMA SERIALLY_REUSABLE

PRAGMA SERIALLY_REUSABLE
--============================================

PRAGMA :-

a.    It signifies that the statement is a pragma (compiler directive).

b.    Pragmas are processed at compile time, not at run time.

c.    They do not affect the meaning of a program, they simply convey information to the compiler.

PRAGMA SERIALLY_REUSABLE in ORACLE:-

a.    The pragma SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server

b.    After “PRAGMA SERIALLY_REUSABLE” call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.

c.    If a package has a spec and body, you must mark both. You cannot mark only the body.

d.    Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, Oracle generates an error.

WHY PRAGMA SERIALLY_REUSABLE ?

Because the state of a non-reusable package persists for the lifetime of the session,

this locks up UGA (User Global Area) memory for the whole session. In applications, a log-on session can typically exist for days together.

With SERIALLY_REUSABLE packages, application developers have a way of modelling their applications to manage their memory better for scalability.

Package state that they care about only for the duration of a call to the server should be captured in SERIALLY_REUSABLE packages.

--====================================================

Example-1

--====================================================

--====================================================

-- Normal Package

--====================================================

create or replace package XXSD_NORMAL_PKG as

  V_VARIABLE_VALUE int := 0;

end;

--====================================================

-- Pragma Serially Reusable Package

--====================================================

create or replace package XXSD_SERIALLY_REUSABLE_PKG as

    pragma serially_reusable;

    V_VARIABLE_VALUE int := 0;

end;

--====================================================

-- First anonymous Block

--====================================================

DECLARE

 BEGIN

    dbms_output.put_line ('********'||(XXSD_NORMAL_PKG.V_VARIABLE_VALUE + 1)||'. Run  ********');

    dbms_output.put_line ('                                     | Normal Variable  |  PRAGMA_SERIALLY_REUSABLE Variable  ');

    dbms_output.put_line ('Before Assigning = >   |    '||XXSD_NORMAL_PKG.V_VARIABLE_VALUE ||'                  |            '||  XXSD_SERIALLY_REUSABLE_PKG.V_VARIABLE_VALUE);

   

    XXSD_NORMAL_PKG.V_VARIABLE_VALUE := XXSD_NORMAL_PKG.V_VARIABLE_VALUE+1;

    XXSD_SERIALLY_REUSABLE_PKG.V_VARIABLE_VALUE := XXSD_SERIALLY_REUSABLE_PKG.V_VARIABLE_VALUE+1;

   

    dbms_output.put_line ('After Assigning = >     |   '||XXSD_NORMAL_PKG.V_VARIABLE_VALUE ||'                   |            '||  XXSD_SERIALLY_REUSABLE_PKG.V_VARIABLE_VALUE );

   

 END;

output like:-

--================================================

********1. Run  ********

                                     | Normal Variable  |  PRAGMA_SERIALLY_REUSABLE Variable  

Before Assigning = >   |    0                       |            0

After Assigning = >      |   1                        |            1

********2. Run  ********

                                     | Normal Variable  |  PRAGMA_SERIALLY_REUSABLE Variable  

Before Assigning = >   |    1                       |            0

After Assigning = >      |   2                        |            1

********3. Run  ********

                                     | Normal Variable  |  PRAGMA_SERIALLY_REUSABLE Variable  

Before Assigning = >   |    2                       |            0

After Assigning = >      |   3                        |            1

--====================================================

Example-2

--====================================================

CREATE PACKAGE XXSD_PKG IS

   PRAGMA SERIALLY_REUSABLE;

   num NUMBER := 0;

   PROCEDURE init_pkg_state(n NUMBER);

   PROCEDURE print_pkg_state;

END XXSD_PKG ;

CREATE PACKAGE BODY XXSD_PKG IS

   PRAGMA SERIALLY_REUSABLE;

   PROCEDURE init_pkg_state (n NUMBER) IS

   BEGIN

      XXSD_PKG.num := n;

   END;

   PROCEDURE print_pkg_state IS

   BEGIN

      dbms_output.put_line('Num: ' || XXSD_PKG.num);

   END;

END XXSD_PKG