msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

Oracle PL/SQL package initialization

Did you know that Oracle PL/SQL packages can have an initialization section? The initialization part of a package is run only once, the first time you reference the package. Here is an example:

  CREATE PACKAGE emp_actions AS
     /* Declare externally callable subprograms. */
     FUNCTION hire_employee (
        ename  VARCHAR2,
        job    VARCHAR2,
        mgr    REAL,
        sal    REAL,
        comm   REAL,
        deptno REAL) RETURN INT;
  END emp_actions;

  CREATE PACKAGE BODY emp_actions AS
     number_hired INT;  -- visible only in this package

     /* Fully define subprograms specified in package. */
     FUNCTION hire_employee (
        ename  VARCHAR2,
        job    VARCHAR2,
        mgr    REAL,
        sal    REAL,
        comm   REAL,
        deptno REAL) RETURN INT IS
        new_empno INT;
     BEGIN
        SELECT empno_seq.NEXTVAL INTO new_empno 
        FROM dual;
        INSERT INTO emp VALUES (new_empno, ename, job,
           mgr, SYSDATE, sal, comm, deptno);
        number_hired := number_hired + 1;
        RETURN new_empno;
     END hire_employee;

  BEGIN  -- initialization part starts here
     INSERT INTO emp_audit 
     VALUES (SYSDATE, USER, 'EMP_ACTIONS');
     number_hired := 0;
  END emp_actions;

Remember, the initialization part of a package is run just once, the first time you reference the package. So, in the last example, only one row is inserted into the database table emp_audit. Likewise, the variable number_hired is initialized only once.

Every time the procedure hire_employee is called, the variable number_hired is updated. However, the count kept by number_hired is session specific. That is, the count reflects the number of new employees processed by one user, not the number processed by all users.


Filed in Oracle on 29 Jun 05 | Tags:


Reader's Comments

  1. |

    This is a very useful bit of information.

    I can use this to load commission rates in my application.