Oracle User Defined Packages
Version 21c

General Information
Library Note Morgan's Library Page Header
Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.
Advantages To Using Packages
  • All related code in a single object
  • All related code loaded into memory simultaneously
  • Session global variables and types
  • Single object compilation
  • Variables persist for term of session
  • Initialization section
  • Overloading
  • Fewer objects to manage and grant/revoke privileges
  • Executed as the package owner rather than the caller reducing the dictionary cache load
Data Dictionary Objects
ALL_ARGUMENTS CDB_PROCEDURES ERROR$
ALL_ERRORS_AE CDB_SOURCE_AE SOURCE$
ALL_OBJECT_SIZE CDB_WARNING_SETTINGS USER_ARGUMENTS
ALL_PLSQL_OBJECT_SETTINGS DBA_ARGUMENTS USER_ERRORS_AE
ALL_PROCEDURES DBA_ERRORS_AE USER_OBJECT_SIZE
ALL_SOURCE_AE DBA_OBJECT_SIZE USER_PLSQL_OBJECT_SETTINGS
ALL_WARNING_SETTINGS DBA_PLSQL_OBJECT_SETTINGS USER_PROCEDURES
CDB_ARGUMENTS DBA_PROCEDURES USER_SOURCE_AE
CDB_ERRORS_AE DBA_SOURCE_AE USER_WARNING_SETTINGS
CDB_OBJECT_SIZE DBA_WARNING_SETTINGS WARNING_SETTINGS$
CDB_PLSQL_OBJECT_SETTINGS    
Object Privileges GRANT execute ON <function_name>;

Privileges to tables and views granted through roles may not be valid within a procedure. See the section on AUTHID.
System Privileges
ALTER ANY PROCEDURE CREATE PROCEDURE DROP ANY PROCEDURE
CREATE ANY PROCEDURE DEBUG ANY PROCEDURE EXECUTE ANY PROCEDURE
 
Orphan Package Headers
Package Header Defining Variables and  Constants CREATE OR REPLACE PACKAGE <package_name> AUTHID <authid_value> IS
 <variable name> CONSTANT <data_type> := <value>;
END <package_name>;
/
CREATE OR REPLACE PACKAGE uw_constants AUTHID DEFINER IS
 cStartDate  CONSTANT DATE := TO_DATE('15-MAR-2018');
 cInstructor CONSTANT VARCHAR2(30) := 'A. Morgan';
 cPi         CONSTANT NUMBER(8,7) := 3.1415926;
END uw_constants;
/

set serveroutput on

DECLARE
 x VARCHAR2(20);
BEGIN
  x := 'Daniel ' || uw_constants.cInstructor;
  dbms_output.put_line(x);
END;
/

DECLARE
 x NUMBER(10,8);
BEGIN
  x := uw_constants.cPi * 2;
  dbms_output.put_line(TO_CHAR(x));
END;
/
Package Header Defining REF CURSORS And User Defined Data Types CREATE OR REPLACE PACKAGE uw_type AUTHID DEFINER IS
 TYPE t_ref_cursor IS REF CURSOR;

 TYPE tab_t IS TABLE OF all_tables.table_name%TYPE
 INDEX BY BINARY_INTEGER;

 ptab_t tab_t;
END uw_type;
/

set serveroutput on

DECLARE
 testvar uw_type.tab_t;
BEGIN
  testvar(1) := 'This is a test';
  dbms_output.put_line(testvar(1));
END;
/

-- See Bulk Collection and Native Dynamic SQL link below for demos with these TYPE definitions.
 
Simple Packages
Package With One Procedure CREATE OR REPLACE PACKAGE <package_name> AUTHID <authid_value> AS
 PROCEDURE <procedure_name> (<parameters>);
END <package_name>;
/

CREATE OR REPLACE PACKAGE BODY <package_name> AS
--========================================
PROCEDURE <procedure_name> (<parameters>) IS

<define local variables, constants, and exceptions>

BEGIN
  <procedure_code>;
END <procedure_name>;
--========================================
END <package_name>;
/
CREATE OR REPLACE PACKAGE one_proc AUTHID DEFINER AS
 PROCEDURE get_table(number_in IN PLS_INTEGER);
END one_proc;
/

CREATE OR REPLACE PACKAGE BODY one_proc AS
--========================================
PROCEDURE get_table(number_in IN PLS_INTEGER) IS
 tabname user_tables.table_name%TYPE;
BEGIN
  SELECT table_name
  INTO tabname
  FROM user_tables
  WHERE rownum < number_in;

  dbms_output.put_line(tabname);
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('Too Many Rows');
END get_table;
--========================================
END one_proc;
/

set serveroutput on

exec one_proc.get_table(2);
Package With One Function CREATE OR REPLACE PACKAGE one_function AUTHID DEFINER AS
 FUNCTION GetOSUser RETURN VARCHAR2;
END one_function;
/

CREATE OR REPLACE PACKAGE BODY one_function AS
--========================================
FUNCTION getosuser RETURN VARCHAR2 IS
 vOSUser user_users.username%TYPE;
BEGIN
  SELECT osuser
  INTO vOSUser
  FROM sys.v_$session
  WHERE sid = (
    SELECT sid
    FROM sys.v$mystat
    WHERE rownum = 1);
  RETURN vOSUser;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'UNK';
END getosuser;
--========================================
END one_function;
/

SELECT one_function.getosuser FROM dual;
 
Complex Packages
Package With Multiple Procedures & Functions CREATE OR REPLACE PACKAGE <package_name> AUTHID <authid_value> AS
 PROCEDURE <procedure_name> (<parameters>);
 PROCEDURE <procedure_name> (<parameters>);
 PROCEDURE <procedure_name> (<parameters>);
 FUNCTION <function_name> (<parameters>) RETURNING <data_type>;
END <package_name>;
/

CREATE OR REPLACE PACKAGE BODY <package_name> AS
--========================================
PROCEDURE <procedure_name> (<parameters>) IS

<define local variables, constants, and exceptions>

BEGIN
  <procedure_code>;
END <procedure_name>;
--========================================
PROCEDURE <procedure_name> (<parameters>) IS

<define local variables, constants, and exceptions>

BEGIN
  <procedure_code>;
END <procedure_name>;
--========================================
FUNCTION <function_name> (<parameters>)
RETURNING <data_type> IS

<define local variables, constants, and exceptions>

BEGIN
  <function_code>;
END <function_name>;
--========================================
END <package_name>;
/
CREATE TABLE error_log (
proc_name        VARCHAR2(30),
block_identifier VARCHAR2(30),
system_mode      VARCHAR2(30),
error_code       VARCHAR2(100),
error_message    VARCHAR2(30),
error_user       VARCHAR2(30),
error_date       DATE);

CREATE OR REPLACE PACKAGE pkg_utility AUTHID DEFINER IS
 FUNCTION ConvDate (datestringin VARCHAR2) RETURN DATE;
 FUNCTION GetOSUser RETURN VARCHAR2;
 PROCEDURE errorlogutil(pProcName IN VARCHAR2,
                        pBlockID  IN VARCHAR2,
                        pSysMode  IN VARCHAR2,
                        pErrCode  IN VARCHAR2,
                        pErrMesg  IN VARCHAR2);
END pkg_utility;
/
--========================================
CREATE OR REPLACE PACKAGE BODY pkg_utility IS

FUNCTION ConvDate (datestringin VARCHAR2) RETURN DATE IS
/**************************
6 = M/D/YY
7 = M/DD/YY or MM/D/YY
8 = M/D/YYYY or MM/DD/YY
9 = MM/D/YYYY or M/DD/YYYY
10 = MM/DD/YYYY
**************************/

strlen  PLS_INTEGER;
slash1  PLS_INTEGER;
slash2  PLS_INTEGER;
x       VARCHAR2(10);
baddate EXCEPTION;

BEGIN
  strlen := LENGTH(datestringin);
  slash1 := INSTR(datestringin, '/', 1, 1);
  slash2 := INSTR(datestringin, '/', 1, 2);

  IF strlen = 6 THEN
    x := '0' || SUBSTR(datestringin,1,1) || '/0'
             || SUBSTR(datestringin, 3,1) || '/'
             || SUBSTR(datestringin, 5);
    RETURN TO_DATE(x, 'MM/DD/RRRR');
  ELSIF strlen = 7 THEN
    IF slash1 = 2 THEN
      RETURN TO_DATE('0' || datestringin, 'MM/DD/RRRR');
    ELSIF slash1 = 3 THEN
      x := SUBSTR(datestringin,1,3) || '0' ||
      SUBSTR(datestringin,4);
      RETURN TO_DATE(datestringin, 'MM/DD/RRRR');
    ELSE
      RAISE baddate;
    END IF;
  ELSIF strlen = 8 THEN
    IF slash1 = 2 THEN
      x := '0' || SUBSTR(datestringin,1,2) || '0'
               || SUBSTR(datestringin, 3);
      RETURN TO_DATE(datestringin, 'MM/DD/RRRR');
    ELSIF slash1 = 3 THEN
      RETURN TO_DATE(datestringin, 'MM/DD/RR');
    ELSE
      RAISE baddate;
    END IF;
  ELSIF strlen = 9 THEN
    IF slash1 = 2 THEN
      RETURN TO_DATE('0' || datestringin, 'MM/DD/RRRR');
    ELSIF slash1 = 3 THEN
      RETURN TO_DATE(datestringin, 'MM/DD/RRRR');
    ELSE
      RAISE baddate;
    END IF;
  ELSIF strlen = 10 THEN
    RETURN TO_DATE(datestringin, 'MM/DD/RRRR');
  ELSE
    RAISE baddate;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN TO_DATE('01-JAN-1900');
END ConvDate;
--===========================================
FUNCTION getosuser RETURN VARCHAR2 IS
 vOSUser user_users.username%TYPE;
BEGIN
  SELECT osuser
  INTO vOSUser
  FROM sys.v_$session
  WHERE sid = (
    SELECT sid
    FROM sys.v$mystat
    WHERE rownum = 1);

  RETURN vOSUser;
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'UNK';
END getosuser;
--===========================================
PROCEDURE errorlogutil (
pProcName VARCHAR2,
pBlockID  VARCHAR2,
pSysMode  VARCHAR2,
pErrCode  VARCHAR2,
pErrMesg  VARCHAR2)
IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN
  INSERT INTO error_log
  (proc_name, block_identifier, system_mode,
  error_code, error_message, error_user, error_date)
  VALUES
  (pProcName, pBlockID, pSysMode,
  pErrCode, pErrMesg, getOSUser, SYSDATE);
  COMMIT;

-- no exception handler intentionally
END errorlogutil;
--===========================================
END pkg_utility;
/

SELECT pkg_utility.convdate('1/2/03') FROM dual;

SELECT pkg_utility.convdate('02/03/2018') FROM dual;

SELECT pkg_utility.getosuser FROM dual;
 
Package with Local Function or Procedure
Package With Local Function and Global Procedure CREATE OR REPLACE PACKAGE <package_name> AUTHID <authid_value> AS
 PROCEDURE <procedure_name> (<parameters>);
 -- note the function is not defined in the header
END <package_name>;
/
CREATE OR REPLACE PACKAGE private_function AUTHID DEFINER AS
 PROCEDURE encrypt_name(namein VARCHAR2);
END private_function;
/

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

CREATE OR REPLACE PACKAGE BODY private_function AS
--======================================
-- must appear before it is called
FUNCTION encrypt_name(namein VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN TRANSLATE(namein, 'aeiou', 'uiaeo');
END encrypt_name;
--======================================
PROCEDURE encrypt_name(namein VARCHAR2) IS
 val VARCHAR2(50);
BEGIN
  val := encrypt_name(namein);
  dbms_output.put_line(val);
END encrypt_name;
--======================================
END private_function;
/

set serveroutput on

exec private_function.encrypt_name('Morgan');
 
Package Overloading
Identify An Overloaded Package break on overload skip page

set pagesize 25
set linesize 121
col overload format a8

SELECT overload, position, argument_name, in_out, data_type
FROM all_arguments
WHERE object_name = 'CREATE_TUNING_TASK'
ORDER BY overload, position;
Create An Overloaded Package CREATE TABLE persons (
seqno INTEGER,
name  VARCHAR2(30));

INSERT INTO persons VALUES (1, 'Dan Morgan');
INSERT INTO persons VALUES (2, 'Debra Lilley');
COMMIT;

CREATE OR REPLACE PACKAGE overloaded AUTHID DEFINER IS
 PROCEDURE insby(namein VARCHAR2);
 PROCEDURE insby(numbin PLS_INTEGER);
END overloaded;
/

CREATE OR REPLACE PACKAGE BODY overloaded IS
--==========================================
PROCEDURE local(namein VARCHAR2, nameout OUT VARCHAR2) IS

BEGIN
  nameout := TRANSLATE(namein,'AEIOUaeiou','EIOUAeioua');
END local;
--==========================================
PROCEDURE insby (namein VARCHAR2) IS
 x PLS_INTEGER;
BEGIN
  SELECT MAX(seqno)+1
  INTO x
  FROM persons;

  INSERT INTO persons
  (seqno, name)
  VALUES
  (x, namein);
  COMMIT;
END insby;
--==========================================
PROCEDURE insby (numbin PLS_INTEGER) IS
 x VARCHAR2(30);
BEGIN
  SELECT MAX(name)
  INTO x
  FROM persons;

  local(x, x);

  INSERT INTO persons
  (seqno, name)
  VALUES
  (numbin, x);
  COMMIT;
END insby;
--==========================================
END overloaded;
/

exec overloaded.insby('Helen Lofstrom');

exec overloaded.insby(4);
 
Initialization Section
Initialization Section Demo CREATE OR REPLACE PACKAGE init_sect AUTHID DEFINER IS
 global_var VARCHAR2(50);
 FUNCTION dummy RETURN VARCHAR2;
END init_sect;
/

CREATE OR REPLACE PACKAGE BODY init_sect IS
 FUNCTION dummy RETURN VARCHAR2 IS
 BEGIN
   RETURN global_var;
 END dummy;
 --===============================
BEGIN
  SELECT 'Dan Morgan'
  INTO global_var
  FROM dual;
END init_sect;
/

SELECT init_sect.dummy FROM dual;
Reusable Initialization Section Demo CREATE OR REPLACE PACKAGE init_refresh AUTHID DEFINER IS
 global_year VARCHAR2(4);
 FUNCTION dummy RETURN VARCHAR2;
 PROCEDURE refresh;
END init_refresh;
/

CREATE OR REPLACE PACKAGE BODY init_refresh IS
 FUNCTION dummy RETURN VARCHAR2 IS
 BEGIN
   RETURN global_year;
 END dummy;
--===============================
 PROCEDURE refresh IS
 BEGIN
   SELECT TO_CHAR(SYSDATE, 'YYYY')
   INTO global_year
   FROM dual;
 END refresh;
--===============================
BEGIN
  refresh;
END init_refresh;
/

SELECT init_refresh.dummy FROM dual;
 
Pragma Serially_Reusable
Serial Reusability Demo -- regular package - uses user global memory (UGA)

CREATE OR REPLACE PACKAGE reg_pkg AUTHID DEFINER IS
 x NUMBER(5);
 PROCEDURE setval(val NUMBER);
 PROCEDURE getval;
END reg_pkg;
/

CREATE OR REPLACE PACKAGE BODY reg_pkg IS
 PROCEDURE setval (val NUMBER) IS
 BEGIN
   x := val;
 END setval;
---------------------------------
 PROCEDURE getval IS
 BEGIN
   IF x IS NOT NULL THEN
     dbms_output.put_line(x);
   ELSE
     dbms_output.put_line('x is NULL');
   END IF;
 END getval;
END reg_pkg;
/

-- view variable persistance of standard package
exec reg_pkg.setval(5);
exec reg_pkg.getval;

--===============================
-- serially reusable package - uses shared pool memory

CREATE OR REPLACE PACKAGE sr_pkg AUTHID DEFINER IS
 PRAGMA SERIALLY_REUSABLE;
 x NUMBER(5);
 PROCEDURE setval(val NUMBER);
 PROCEDURE getval;
END sr_pkg;
/

CREATE OR REPLACE PACKAGE BODY sr_pkg IS
 PRAGMA SERIALLY_REUSABLE;
 PROCEDURE setval (val NUMBER) IS
 BEGIN
   x := val;
  END setval;
---------------------------------
 PROCEDURE getval IS
 BEGIN
   IF x IS NOT NULL THEN
     dbms_output.put_line(x);
   ELSE
     dbms_output.put_line('x is NULL');
   END IF;
 END getval;
END sr_pkg;
/

-- view variable non-persistance of serially_resuable package
exec sr_pkg.setval(5);
exec sr_pkg.getval;
 
Alter Package
Package Recompile ALTER PACKAGE <package_name> COMPILE;
SELECT object_name, last_ddl_time
FROM user_objects_ae
WHERE object_type = 'PACKAGE';

ALTER PACKAGE init_refresh COMPILE;

SELECT object_name, last_ddl_time
FROM user_objects_ae
WHERE object_type = 'PACKAGE';
 
Drop Package
Drop Package Header and Body DROP PACKAGE <package_name>;
SELECT object_name
FROM user_objects_ae
WHERE object_type = 'PACKAGE';

DROP PACKAGE init_refresh;

desc init_refresh
Drop Package Body Only DROP PACKAGE BODY <package_name>;
SELECT object_name
FROM user_objects_ae
WHERE object_type = 'PACKAGE';

DROP PACKAGE BODY init_sect;

desc init_sect
 
Undocumented Package
I've no explanation for the following but wanted to document it here.

If anyone can provide clarity please email me.		 CREATE OR REPLACE PACKAGE test AUTHID DEFINER AS
FUNCTION testf(args ...) RETURN VARCHAR2;
END test;
/

desc test

SELECT argument_name, position, data_type, in_out
FROM all_arguments
WHERE package_name = 'TEST';

CREATE OR REPLACE PACKAGE BODY test AS
FUNCTION testf(args ...) RETURN VARCHAR2 IS
  BEGIN
    RETURN 'Z';
  END testf;
END test;
/

SQL> sho err
Errors for PACKAGE BODY TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/16 PLS-00999: implementation restriction (may be temporary) ellipsis
not allowed in this context
 
Package Related Queries
Retrieve Package Metadata SELECT object_name, edition_name
FROM user_objects_ae
WHERE object_type = 'PACKAGE';

col object_name format a30
col data_type format a15

SELECT object_name, argument_name, position, data_type, data_length
FROM user_arguments
WHERE package_name = 'SR_PKG'
ORDER BY object_name, position;
Retrieve Package Source Code SELECT text
FROM user_source
WHERE name = 'SR_PKG';

or

SELECT dbms_metadata.get_ddl('PACKAGE', 'SR_PKG')
FROM dual;
Overloaded Packages conn sys@pdbdev as sysdba

SELECT overload, COUNT(*)
FROM all_arguments
WHERE overload IS NOT NULL
GROUP BY overload
HAVING COUNT(*) > 1
ORDER BY TO_NUMBER(overload);
Is a Package Currently In Use conn sys@pdbdev as sysdba

desc gv$db_object_cache

SELECT name, loads, executions, pins
FROM v$db_object_cache
WHERE type = 'PACKAGE'
AND pins > 0;

Related Topics
Accessible By Clause
Anonymous Blocks
DBMS_METADATA.GET_DDL
DBMS_SESSION.MODIFY_PACKAGE_STATE
DBMS_SESSION.RESET_PACKAGE
Exception Handling
Functions
PL/SQL Object Settings
PL/SQL_Warnings
Pragma Inline
Procedures
SYS_CONTEXT Function
What's New In 21c
What's New In 23c
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx