CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Packages
Version 11.1
General
Related Data Dictionary Objects
error$ source$  
  all_arguments user_arguments
dba_errors all_errors user_errors
dba_object_size all_object_size user_object_size
dba_procedures all_procedures user_procedures
dba_source all_source user_source
System Privileges Related To Packages
alter any procedure debug any procedure
create any procedure drop any procedure
create procedure execute any procedure
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.
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
 
Orphan Package Headers

Package Header Defining Variables & Constants
CREATE OR REPLACE PACKAGE <package_name> IS

<variable name> CONSTANT <data_type> := <value>;

END <package_name>;
/
CREATE OR REPLACE PACKAGE uw_constants IS
 cStartDate  CONSTANT DATE := TO_DATE('07-JAN-2006');
  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 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 page
-- for demonstrations with these TYPE definition.
 
Simple Packages

Package With One Procedure
CREATE OR REPLACE PACKAGE <package_name> 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 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 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> 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 IS

FUNCTION ConvDate (datestringin VARCHAR2) RETURN DATE;
FUNCTION GetOSUser RETURN VARCHAR2;
PROCEDURE errorlogutil(pProcName VARCHAR2, pBlockID VARCHAR2,
 pSysMode VARCHAR2, pErrCode VARCHAR2, pErrMesg 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/2004') 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> AS

PROCEDURE <procedure_name> (<parameters>);
-- note function is not defined in the header
END <package_name>;
/
CREATE OR REPLACE PACKAGE hidden_function AS

PROCEDURE encrypt_name(namein VARCHAR2);

END hidden_function;
/

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

CREATE OR REPLACE PACKAGE BODY hidden_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 hidden_function;
/

set serveroutput on

exec hidden_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 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 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 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

Serially_Reusable Demo
-- regular package - uses user global memory (UGA)

CREATE OR REPLACE PACKAGE reg_pkg 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 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
Recompile Package ALTER PACKAGE <package_name> COMPILE;
SELECT object_name
FROM user_objects
WHERE object_type = 'PACKAGE';

ALTER PACKAGE init_refresh COMPILE;
 
Drop Package

Drop Package Header and Body
DROP PACKAGE <package_name>;
SELECT object_name
FROM user_objects
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
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 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
FROM user_objects
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';

Overloaded Packages
/ 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
/ 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
Anonymous Blocks
DBMS_METADATA.GET_DDL
DBMS_SESSION.MODIFY_PACKAGE_STATE
DBMS_SESSION.RESET_PACKAGE
Functions
Pragma Inline
Procedures
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [70 users online]    © 2010 psoug.org