Showing posts with label Dependencies. Show all posts
Showing posts with label Dependencies. Show all posts

(12) Packages


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:
    1. whether subprograms and cursors in the package run with the definer (the default) or invoker privileges.
    2. 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.proc instead 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. (1) package my_rec defines a RECORD type used as formal parameter type in a procedure proc1.
  2. (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.