About Packages:
- Schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions.
Package specification:
- Declares the public items that can be referenced from outside the package. Works as an (API).
- If the public items include cursors or subprograms, then the package must also have a body.
- The scope of a public item is the schema of the package. A public item is visible everywhere in the schema.
- AUTHID clause determines:
- whether subprograms and cursors in the package run with the definer (the default) or invoker privileges.
- whether unqualified references to schema objects are resolved in the schema of the definer or invoker.
Package Body:
- Package body is optional, unless package specification declares cursors or subprograms.
- The package body and package specification must be in the same schema.
- Can also declare and define private items that cannot be referenced from outside the package
- Can have an initialization part, whose statements initialize variables and do other one-time setup steps.
- In either the package specification or package body, you can map a package subprogram to an external Java or C subprogram
- For this, you use a call specification, which maps the external subprogram name, parameter types, and return type to their SQL counterparts.
Why use packages?
- Modularity:
- encapsulate logically related types, variables, constants, subprograms, cursors, and exceptions.
- You can modify package objects without recompiling dependent schema objects
- Let you OVERLOAD subprograms.
- Easier Application design
- Can contain global variables and cursors that are available to all subprograms in the package.
- Packages increase your namespace -- "you get
pkg.procinstead of 5,000 little procedures with meaningless names, you can have 500 packages with good module names, each with 10 procedures that have meaningful names (that might be the same as the name of a procedure in another package). (ask tom) - good coding practices
- Allow many small procedures instead a few large ones.
- Packages maintain a state
- Packages allow you to create types
- Information Hiding
- You may have 20 procedures in the body, but choose to make only 10 of them public in the specificaiton
- Added functionality: Package public variables and cursors can persist for the life of a session.
- Better performance:
After first time you invoke a package subprogram, Oracle Database loads the package as needed into memory. Packages ARE NOT entirely loaded immediately. - Better Security control and privilege management: more efficient grant privileges
What are appropriate public items?
- types, variables, constants, subprograms, cursors, and exceptions
- Associative arrays type of standalone subprogram parameters: Since you cannot declare an AA type at schema level.
- variables that must remain available between subprogram invocations in the same session.
- subprograms that read and write public variables.
- subprograms that invoke each other.
- overloaded subprograms
Note: You cannot reference remote package public variables, even indirectly.
 | Package instantiation and initialization: |
- When a session references a package item, Oracle Database instantiates the package for that session.
- Every session that references a package has its own instantiation of that package.
- Initialization includes whichever of the following are applicable:
- Assigning initial values to public constants
- Assigning initial values to public variables whose declarations specify them.
- Executing the initialization part of the package body
| Package State |
- Package state: the values of the variables, constants, and cursors that a package declares (in either its specification or body)
- If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful;
- otherwise, it is stateless.
- Each session that references a package item has its own instantiation of that package.
- If the package is stateful, the instantiation includes its state.
- The package state persists for the life of a session, except in these situations:
- package is SERIALLY_REUSABLE
- package body recompiled
| SERIALLY_REUSABLE |
- The package state is stored in a work area in a small pool in the system global area (SGA).
- If a package is NOT SERIALLY_REUSABLE:
- its package state is stored in the user global area (UGA) for each user.
- Limit scalability: the amount of UGA memory needed increases linearly with the number of users.
- Potential high usage of memory: The package state can persist for the life of a session, locking UGA memory until the session ends.
- For a SERIALLY_REUSABLE package, the work unit is a server call.
- You must use its public variables only within the work unit.
- If you depend on the value of a public variable that was set in a previous work unit, your program can fail. PL/SQL cannot check for such cases.
 | About Packages: Initialization and variables. |
(1) The package body can have an executable section.
(2) This section is executed when the first reference to a package object
is made. In the example, when you print the value of variable 'value'
in an anonymous block.
(3) The package body can have an initialization part, whose statements
initialize public variables and do other one-time setup steps.
The initialization part runs only the first time the package is referenced.
The initialization part can include an exception handler.
-- (1) create package ..
create or replace package my_pack
as
value number := 100;
const constant number := 999;
procedure update_value (pval number);
end my_pack;
/
-- (2) .. and package body
create or replace package body my_pack
as
procedure update_value (pval number)
is
begin
my_pack.value := pval;
dbms_output.put_line('my_pack.value updated to: '|| my_pack.value);
dbms_output.put_line('my_pack.const: '|| my_pack.const);
end;
begin --- executable section of package. Runs once.
dbms_output.put_line('initial value of my_pack.value: '|| my_pack.value);
dbms_output.put_line('initial value of value: '|| value);
dbms_output.put_line('initial value of my_pack.const: '|| my_pack.const);
my_pack.value := 300;
dbms_output.put_line('New value of my_pack.value: '|| my_pack.value);
end my_pack;
/
--(3) The initialization code is executed after the first call to a package object..
set serveroutput on
declare
x number;
begin
x := my_pack.const; -- First call to a package object.
dbms_output.put_line('x is: '|| x);
x := my_pack.value;
dbms_output.put_line('x is: '|| x);
end;
/
anonymous block completed
initial value of my_pack.value: 100
initial value of value: 100
initial value of my_pack.const: 999
New value of my_pack.value: 300
x is: 999
x is: 300
--(4) Run the anonymous block again. The executable section in the package body IS NOT executed again.
anonymous block completed
x is: 999
x is: 300
--(5) If you recompile the package, the initialization code is executed again, in the first call to a package object.
SQL> alter package my_pack compile;
package MY_PACK altered.
set serveroutput on
declare
x number;
begin
x := my_pack.const;
dbms_output.put_line('x is: '|| x);
x := my_pack.value;
dbms_output.put_line('x is: '|| x);
end;
/
anonymous block completed
initial value of my_pack.value: 100
initial value of value: 100
initial value of my_pack.const: 999
New value of my_pack.value: 300
x is: 999
x is: 300
| One-time Procedures and Global Package variables |
USER A Executes:
create or replace package set_global_commission
as
serv_commission number(4,2);
end;
/
create or replace package body set_global_commission
as
vtemp number(4,2);
begin
select avg(commission_pct) into serv_commission
from usera.employees;
vtemp := serv_commission;
set_global_commission.serv_commission := set_global_commission.serv_commission * 2;
dbms_output.put_line('Original: '|| to_char(vtemp) ||
' Current: ' || to_char(set_global_commission.serv_commission));
end set_global_commission;
/
Package body created.
-- Now initializing the package state:
set serveroutput on
begin
dbms_output.put_line('Service commission is: '|| set_global_commission.serv_commission);
set_global_commission.serv_commission := set_global_commission.serv_commission * 5.37;
dbms_output.put_line('New value for User A session is: '|| set_global_commission.serv_commission);
end;
/
anonymous block completed
Original: .22 Current: .44
Service commission is: .44
New value for User A session is: 2.36
Question: Now Can User B access the variable value?
Answer: User B CANNOT access the value held in the session by User A. User B can ONLY see the value for the variable set within his own session:
As USER B:
set serveroutput on
begin
dbms_output.put_line('Service commission is: '|| usera.set_global_commission.serv_commission);
end;
/
Error report:
ORA-06550: line 2, column 51:
PLS-00201: identifier 'USERA.SET_GLOBAL_COMMISSION' must be declared
ORA-06550: line 2, column 2:
| Accessing Package variables: restriction |
- If a subprogram has as formal parameter a variable defined on package specification,
calls to the subprogram need to reference the package variable definition when defining
the ACTUAL parameter that will be used.
-- Example:
- (1) package my_rec defines a RECORD type used as formal parameter type in a procedure proc1.
- (2) When calling proc1 from OUTSIDE the package, the ACTUAL parameter needs to be defined using the
record type defined in the package.
create or replace package my_rec
is
type t_emprec is record (
empid employees.employee_id%type,
ln employees.last_name%type,
fs employees.first_name%type);
procedure proc1 (p_empid in number, p_emp_info OUT t_emprec);
end my_rec;
/
create or replace package body my_rec
is
procedure proc1 (p_empid in number, p_emp_info OUT t_emprec)
is
begin
select employee_id, last_name, first_name
into p_emp_info
from employees
where employee_id = p_empid;
end proc1;
end my_rec;
-- Case 1: Success
set serveroutput on
declare
emprec my_rec.t_emprec;
begin
my_rec.proc1(100, emprec);
dbms_output.put('Id => '|| emprec.empid || ' ');
dbms_output.put_line('name => '|| emprec.fs || ' '|| emprec.ln);
end;
/
anonymous block completed
Id => 100 name => Steven King
-- Case 2: Failure, even though the data type of the actual parameter RECORD was
formed by the same components used in the definition of the FORMAL parameter.
set serveroutput on
declare
type t_emprec2 is record (
empid employees.employee_id%type,
ln employees.last_name%type,
fs employees.first_name%type);
emprec2 t_emprec2;
begin
my_rec.proc1(100, emprec2);
dbms_output.put('Id => '|| emprec2.empid || ' ');
dbms_output.put_line('name => '|| emprec2.fs || ' '|| emprec2.ln);
end;
/
Error report:
ORA-06550: line 9, column 3:
PLS-00306: wrong number or types of arguments in call to 'PROC1'
ORA-06550: line 9, column 3:
| Information about Packages in the Data Dictionary |
SQL> SELECT object_name, object_type, last_ddl_time, timestamp, status
FROM user_objects
WHERE object_type IN ('PACKAGE','PACKAGE BODY');
/
OBJECT_NAME OBJECT_TYPE LAST_DDL_ TIMESTAMP STATUS
------------------------- ------------------ ---------- ------------------- -------
DEPARTMENT_PKG PACKAGE 03-OCT-11 2011-10-03:14:22:52 VALID
DEPARTMENT_PKG PACKAGE BODY 03-OCT-11 2011-10-03:14:23:45 VALID
MESSAGE_API PACKAGE 19-SEP-11 2011-09-19:01:15:22 VALID
MESSAGE_API PACKAGE BODY 19-SEP-11 2011-09-19:01:18:22 VALID
MY_PACK PACKAGE 19-SEP-11 2011-09-19:13:11:00 VALID
MY_PACK PACKAGE BODY 19-SEP-11 2011-09-19:13:22:10 VALID
SET_EMPNO_CTX_PKG PACKAGE BODY 27-SEP-11 2011-09-27:09:56:41 INVALID
SET_GLOBAL_COMMISSION PACKAGE 23-SEP-11 2011-09-23:10:26:32 VALID
SET_GLOBAL_COMMISSION PACKAGE BODY 23-SEP-11 2011-09-23:10:37:00 VALID
SQL> DESCRIBE my_pack
PROCEDURE Argument Name Type IN/OUT Default
------------ ------------- ------ ------ -------
UPDATE_VALUE PVAL NUMBER IN unknown
| Dependencies between subprograms |
- The view
all_dependencies; user_dependencies; dba_dependencies;let you examine dependencies between stored programs. - Invalid package body does not invalidate procedures that reference (and thus depend upon) the package specification.
- This is because the package specification is the schema-level component.
- In this case, since the subprograms don't see the invalid package body, a run-time error will be raised if the
procedure makes a call to an object that needs to execute within the invalid package body.
SQL>SELECT name, type, referenced_name, referenced_type, dependency_type
FROM user_dependencies
ORDER BY name;
NAME TYPE REFERENCED_NAME REFERENCED DEPE
-------------------- ------------ ------------------------------ ---------- ----
DEPARTMENT_PKG PACKAGE BODY STANDARD PACKAGE HARD
DEPARTMENT_PKG PACKAGE STANDARD PACKAGE HARD
DEPARTMENT_PKG PACKAGE EMPLOYEES TABLE HARD
DEPARTMENT_PKG PACKAGE BODY EMPLOYEES TABLE HARD
DEPARTMENT_PKG PACKAGE BODY DEPARTMENT_PKG PACKAGE HARD
DEPARTMENT_PKG PACKAGE DEPARTMENTS TABLE HARD
DEPARTMENT_PKG PACKAGE BODY DEPARTMENTS TABLE HARD
EMP_NO_SAL VIEW EMPLOYEES TABLE HARD
FIBONACCI FUNCTION STANDARD PACKAGE HARD
GET_BONUS PROCEDURE STANDARD PACKAGE HARD
GET_BONUS PROCEDURE BONUS TABLE HARD
GET_BONUS PROCEDURE DBMS_OUTPUT SYNONYM HARD
GET_BONUS PROCEDURE SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE HARD
MESSAGE_API PACKAGE BODY DBMS_PIPE SYNONYM HARD
MESSAGE_API PACKAGE BODY EMPLOYEES TABLE HARD
MESSAGE_API PACKAGE BODY STANDARD PACKAGE HARD
MESSAGE_API PACKAGE BODY DBMS_STANDARD PACKAGE HARD
MESSAGE_API PACKAGE BODY MESSAGE_API PACKAGE HARD
MESSAGE_API PACKAGE BODY DBMS_OUTPUT SYNONYM HARD
MESSAGE_API PACKAGE EMPLOYEES TABLE HARD
MY_PACK PACKAGE BODY MY_PACK PACKAGE HARD
MY_PACK PACKAGE BODY DBMS_OUTPUT SYNONYM HARD
MY_PACK PACKAGE BODY STANDARD PACKAGE HARD
MY_PACK PACKAGE STANDARD PACKAGE HARD
| Checking dependency and status: |
create or replace procedure pget_salary
(p_empid in employees.employee_id%type)
as
vsal employees.salary%type;
begin
select salary into vsal
from employees
where employee_id = p_empid;
dbms_output.put_line('Salary of empid: '|| p_empid || ' is '|| vsal);
end pget_salary;
/
create or replace package my_pack as
procedure p1 (p_empid employees.employee_id%type);
end;
/
create or replace package body my_pack as
procedure p1 (p_empid employees.employee_id%type)
is
begin
dbms_output.put_line('calling pget_salary for employee '|| p_empid);
pget_salary(p_empid);
end p1;
end my_pack;
/
Now check user_objects, user_dependencies, user_arguments
-- (1) Check object dependencies on USER_DEPENDENCIES
select name, type, referenced_name, referenced_type, dependency_type
from user_dependencies
where name in ('MY_PACK', 'PGET_SALARY')
order by name, type;
NAME TYPE REFERENCED_NAME REFERENCED_T DEPENDENCY
---------------- ------------ ---------------------------- ------------ ----------
MY_PACK PACKAGE EMPLOYEES TABLE HARD
MY_PACK PACKAGE BODY STANDARD PACKAGE HARD
MY_PACK PACKAGE BODY DBMS_OUTPUT SYNONYM HARD
MY_PACK PACKAGE BODY MY_PACK PACKAGE HARD
MY_PACK PACKAGE BODY EMPLOYEES TABLE HARD
MY_PACK PACKAGE BODY PGET_SALARY PROCEDURE HARD
PGET_SALARY PROCEDURE EMPLOYEES TABLE HARD
PGET_SALARY PROCEDURE STANDARD PACKAGE HARD
PGET_SALARY PROCEDURE DBMS_OUTPUT SYNONYM HARD
PGET_SALARY PROCEDURE SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE HARD
-- (2) Check LAST_DDL_TIME and validity status on USER_OBJECTS
select object_name, object_type,
to_char(last_ddl_time, 'DD-MON-YY:HH:MI:SS') LAST_DDL_TIME,
timestamp, status
from user_objects
where object_name in ('MY_PACK', 'PGET_SALARY');
/
OBJECT_NAME OBJECT_TYPE LAST_DDL_TIME TIMESTAMP STATUS
------------- -------------- ------------------ ------------------- -------
MY_PACK PACKAGE 04-OCT-11:11:35:09 2011-10-04:11:35:09 VALID
MY_PACK PACKAGE BODY 04-OCT-11:11:35:14 2011-10-04:11:35:14 VALID
PGET_SALARY PROCEDURE 04-OCT-11:12:06:52 2011-10-04:11:31:12 VALID
set serveroutput on
begin
my_pack.p1(100);
end;
/
anonymous block completed
calling pget_salary for employee 100
Salary of empid: 100 is 7500
alter package my_pack compile;
OBJECT_NAME OBJECT_TYPE LAST_DDL_TIME TIMESTAMP STATUS
------------- -------------- ------------------ ------------------- -------
MY_PACK PACKAGE 04-OCT-11:12:20:21 2011-10-04:11:35:09 VALID
MY_PACK PACKAGE BODY 04-OCT-11:12:20:21 2011-10-04:11:35:14 VALID
PGET_SALARY PROCEDURE 04-OCT-11:12:06:52 2011-10-04:11:31:12 VALID
| Packages break the cycle of dependency. source: Ask Tom |
Scenario:
- (a) Procedure A does something.
- (b) Procedure B calls Procedure A :(B depends on A)
- (c) If procedure A is recompiled, then Procedure B becomes invalid.
-- (1) - create procedure A
SQL> create or replace procedure A as
begin null; end;
/
-- (2) create procedure B. B calls A.
SQL> create or replace procedure B as
begin A; end;
/
-- (3) Check object status: A and B are VALID
SQL> select object_type, object_name, status
from user_objects
where object_name in ('A', 'B');
OBJECT_TYPE OBJECT_NAME STATUS
------------------- ------------- -------
PROCEDURE A VALID
PROCEDURE B VALID
--(4) B depends on A. If you modify A, B becomes invalid.
SQL> create or replace procedure A as
x number;
begin
x :=1;
null;
end;
/
SQL> select object_type, object_name, status
from user_objects
where object_name in ('A', 'B');
OBJECT_TYPE OBJECT_NAME STATUS
------------------- -------------- -------
PROCEDURE A VALID
PROCEDURE B INVALID
Now, if A and B are within PACKAGES:
SQL> create or replace package a as
procedure a; end;
/
SQL> create or replace package b as
procedure b; end;
/
SQL> create or replace package body a as
procedure a is
begin null; end;
end;
/
SQL> create or replace package body b as
procedure b is
begin a.a; end;
end;
/
SQL> select object_type, object_name, status
from user_objects
where object_name in ('A', 'B');
OBJECT_TYPE OBJECT_NAME STATUS
------------------- ------------ -------
PACKAGE A VALID
PACKAGE BODY A VALID
PACKAGE B VALID
PACKAGE BODY B VALID
-- Now, if package body B is modified:
SQL> create or replace package body a as
procedure a is
x number;
begin
x:=1;
null;
end;
end;
SQL> select object_type, object_name, status
from user_objects
where object_name in ('A', 'B');
OBJECT_TYPE OBJECT_NAME STATUS
------------------- ------------- -------
PACKAGE A VALID
PACKAGE BODY A VALID
PACKAGE B VALID
PACKAGE BODY B VALID
Here package B remains VALID.
- This is because only package BODY b was modified.
As long as the spec does not change, package B will remain valid.
