PACKAGES


A package is a schema object that groups logically related PL/SQL types, variables, and subprograms.
Packages usually have two parts:
  • A specification(spec)
  • A body
    • The specification is the interface to the package. It declares the types, variables, constants, etc that can be referenced from outside the package.
    Enable the server to read multiple objects into memory at once. A PL/SQL Packages construct is referenced for first time, the whole package is loaded into memory. Subsequent access to constructs in the same package does not require disk input/output(I/O).

    Advantages of Package :
  • Modularity
  • Easier maintenance
  • Easier application design
  • Hiding information
  • Added functionality
  • Better performance
  • Overloading

    • Package : 
        package specification
    Package package_name
    variable
    Procedure1
    declaration;
    Procedure 2
    declaration;
    End:
    Package body : 
        Package body package_body name
    variable
    Procedure 1
    definition
    begin
    statement:
    end
    Procdure 2
    definition
    begin
    statement;
    end;
    End;
    Package :
    Specification : 
        SQL> create or replace package pg as
    function ff return char;
    procedure pp;
    end;
    /
    Pacakagebody
    SQL> create or replace package body pg as
    function ff return char
    as
    begin
    return ('welcome to package function');
    end;
    procedure pp as
    begin
    dbms_output.put_line('package.procedure');
    end;
    end;
    /
    Call procedure from package : 
        SQL> exec pg.pp();
    package.procedure
    PL/SQL procedure successfully completed
    Raise function from package 
        
    SQL> select pg.ff() from dual;
    PG.FF()
    ------------------------------------
    welcome to package function
    SQL> create or replace package pkg2 as
    procedure p3 (dd in dept.deptno%type);
    function f3 (eid in emp.empno%type) 
    return number;
    end pk g2;
    /
    Package created.

    SQL> create or replace package body pkg2 as
    procedure p3 (dd in dept.deptno%type)
    is
    begin
    update dept set loc='CHENNAI' where deptno=dd;
    end p3;
    function f3 (eid in emp.empno%type)
    return number
    is
    fsal emp.sal%type;
    begin
    select sal into fsal
    from emp where empno=eid;
    return fsal;
    end f3;
    end pkg2;
    /
    Package body created.
    Call procedure from package : 
        SQL> exec pkg2.p3(50);
    PL/SQL procedure successfully completed.
    Raise function from package : 
        SQL> select pkg2.f3(7788) from dual;
    PKG2.F3(7788)
    -------------
    300
    Oracle supports packages : 
        
    dbms_output
    utl_file
    utl_mail
    dbms_alert
    dbms_lock
    dbms_session
    htp
    dbms_ scheduler


    (PL/SQL - Oracle Supplied packages)