| 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;
|
