PL/SQL by Example / Package

In previous parts, most examples were demonstrated at the level of a single anonymous PL/SQL block. In practice, however, it is more appropriate to unify functionality into logical units, called packages. The advantage is not only the grouping of related objects and thus clearer and more readable code, but also higher performance (the package is kept in memory for the duration of the session), simpler administration of access rights, and the ability to shield internal logic from other objects in the database.

A package consists of two parts. The mandatory part is the specification (sometimes also called the “header”) containing only the list and definition of objects, the optional part is the body, which further describes the objects and their function. The rule is that what is declared in the header is publicly visible, while what is declared only in the package body is private.

CREATE OR REPLACE PACKAGE MyPackage
AS
  -- public declarative part
  g_Public NUMBER:=10;
END;
/

CREATE OR REPLACE PACKAGE BODY MyPackage
AS
  -- private declarative part
  g_Private NUMBER:=20;
  G_CONSTANT CONSTANT NUMBER:=30;
END;
/

BEGIN
  dbms_output.put_line(MyPackage.g_Public); -- 10
  MyPackage.g_Public:=30;
  dbms_output.put_line(MyPackage.g_Public); -- 30
  -- CANNOT MyPackage.g_Private:=40;
  -- CANNOT dbms_output.put_line(MyPackage.g_Private);
END;

It is possible to wrap not only variables and constants in a package, but also types, procedures, functions, cursors, and user-defined exceptions.

CREATE OR REPLACE PACKAGE MyPackage
AS
  -- public type
  TYPE Varchar_TT IS TABLE OF VARCHAR2(4000) INDEX BY PLS_INTEGER;

  -- public function specification
  FUNCTION mf_ArrayToString(
      pt_Array MyPackage.Varchar_TT
    , p_Delimiter CHAR:=', '
  ) RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY MyPackage
AS
  -- public function body
  FUNCTION mf_ArrayToString(
      pt_Array MyPackage.Varchar_TT
    , p_Delimiter CHAR:=', '
  ) RETURN VARCHAR2
  IS
    l_Result VARCHAR2(4000);
  BEGIN
    FOR i IN 1..pt_Array.COUNT LOOP
      l_Result:=l_Result||p_Delimiter||pt_Array(i);
    END LOOP;
    RETURN LTRIM(l_Result, p_Delimiter);
  END;
END;
/

DECLARE
  lt_Array MyPackage.Varchar_TT;
BEGIN
  lt_Array(1):='one';
  lt_Array(2):='two';
  dbms_output.put_line(MyPackage.mf_ArrayToString(lt_Array)); -- one, two
END;

If we refer to an internal method inside the package, it is important that its definition physically precedes the definition of the method from which we are calling it. The difference in the specification and use of a public versus an internal method is illustrated in the following example.

CREATE OR REPLACE PACKAGE MyPackage
AS
  -- public parameterless function specification
  FUNCTION mf_ReturnPrivate
    RETURN NUMBER;

  -- public parameterless procedure specification
  PROCEDURE mp_SetPrivateToHundred;
END;
/

CREATE OR REPLACE PACKAGE BODY MyPackage
AS
  -- private variable
  g_Private NUMBER;

  -- private procedure
  PROCEDURE mpi_SetPrivate(
      p_Parameter NUMBER
  )
  IS
  BEGIN
    g_Private:=p_Parameter;
  END;

  -- public parameterless function body
  FUNCTION mf_ReturnPrivate
    RETURN NUMBER
  IS
  BEGIN
    RETURN g_Private;
  END;

  -- public parameterless procedure body
  PROCEDURE mp_SetPrivateToHundred
  IS
  BEGIN
    -- call to private procedure
    mpi_SetPrivate(100);
  END;
END;
/

BEGIN
  -- CANNOT MyPackage.mpi_SetPrivate(100);
  MyPackage.mp_SetPrivateToHundred;
  dbms_output.put_line(MyPackage.mf_ReturnPrivate); -- 100
END;

Just like stored procedures from the previous chapter, functions in packages can also be used in SQL queries.

SELECT MyPackage.mf_ReturnPrivate
  FROM dual
;
-- 100