Scribd Logo
Upload

Welcome to Scribd!

  • 55 views

    Package Specification: Pankaj Jain @twit - Pankajj

    Uploaded by

    sagarbolisetti
    This document discusses Oracle packages, including why they are used, what they can contain, and how they are structured. Packages allow for logical grouping of related objects, management of global variables and session state. A package specification declares public elements and is independently compilable, while a package body defines implementations and private elements. Elements in the specification can be accessed by other database objects once compiled.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    Package Specification: Pankaj Jain @twit - Pankajj

    Uploaded by

    sagarbolisetti
    55 views15 pages
    This document discusses Oracle packages, including why they are used, what they can contain, and how they are structured. Packages allow for logical grouping of related objects, management of global variables and session state. A package specification declares public elements and is independently compilable, while a package body defines implementations and private elements. Elements in the specification can be accessed by other database objects once compiled.

    Original Description:

    package

    Original Title

    Oracle package

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document discusses Oracle packages, including why they are used, what they can contain, and how they are structured. Packages allow for logical grouping of related objects, management of global variables and session state. A package specification declares public elements and is independently compilable, while a package body defines implementations and private elements. Elements in the specification can be accessed by other database objects once compiled.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    55 views15 pages

    Package Specification: Pankaj Jain @twit - Pankajj

    Uploaded by

    sagarbolisetti
    This document discusses Oracle packages, including why they are used, what they can contain, and how they are structured. Packages allow for logical grouping of related objects, management of global variables and session state. A package specification declares public elements and is independently compilable, while a package body defines implementations and private elements. Elements in the specification can be accessed by other database objects once compiled.

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    You are on page 1of 15

    Package Specification

    Pankaj Jain

    @twit_pankajj

    !!

    Why Should You Use Packages

    Logical Grouping

    Global Variables

    Session Data

    Selective
    Exposure

    Better
    Application
    Design

    Performance

    Do Not Place Anything in


    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    When Should You Not Use Packages


    !
    Constantly Changing Specifications

    Do Not Place Anything in


    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    Package
    Structure

    Package Specification
    Public APIs, Variables & Objects
    No Implementations
    Can Exist Independently
    Package Body
    Implementations
    Private APIs, Variables & Objects
    Cannot Exist Independently
    Do Not Place Anything in
    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    Contents of Package

    Procedures

    Functions

    Cursors

    Types, Variables &


    Constants

    Records &
    Collections

    Others

    Do Not Place Anything in


    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    Package Specification
    CREATE PROCEDURE
    CREATE ANY PROCEDURE

    CREATE [OR REPLACE] PACKAGE


    [schema_name.]<package_name> IS | AS
    declarations;
    END [<package_name>];

    Do Not Place Anything in


    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    Package Specification
    !
    CREATE OR REPLACE PACKAGE hr_mgmt AS
    !
    g_active_status CONSTANT VARCHAR2(1) := 'A';
    g_inactive_status CONSTANT VARCHAR2(1) := 'I';

    !
    g_bonus_pct NUMBER;
    !
    dept_not_found_ex EXCEPTION;
    !
    TYPE g_rec IS RECORD(p_profit NUMBER, p_dept_name departments.dept_name%TYPE);
    !
    !
    !

    CURSOR gcur_get_deptid(p_dept_name VARCHAR2) IS


    SELECT dept_id
    FROM departments
    WHERE dept_name = p_dept_name;
    FUNCTION calc_bonus(p_profit NUMBER, p_dept_id NUMBER) RETURN NUMBER;
    PROCEDURE update_emp(p_emp_id NUMBER, p_dept_name VARCHAR2);

    END hr_mgmt;
    Do Not Place Anything in
    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    Order of Declaration
    Referenced Items Declared Before Referring Items

    !
    CREATE OR REPLACE PACKAGE hr_mgmt AS
    !

    g_active_status CONSTANT VARCHAR2(1) := 'A';


    g_inactive_status CONSTANT VARCHAR2(1) := 'I';
    g_bonus_pct NUMBER;

    !
    dept_not_found_ex EXCEPTION;
    !
    TYPE g_rec IS RECORD(p_profit NUMBER, p_dept_name departments.dept_name%TYPE);
    !
    !
    !

    CURSOR gcur_get_sal(p_emp_id NUMBER) IS


    SELECT dept_id
    FROM departments
    WHERE dept_name = g_active_status;
    FUNCTION calc_bonus(p_profit NUMBER, p_dept_id NUMBER) RETURN NUMBER;
    PROCEDURE update_emp(p_emp_id NUMBER, p_dept_name VARCHAR2);

    END hr_mgmt;
    /

    Do Not Place Anything in


    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    Compiling Package Specification


    hr_mgmt.spc

    !
    CREATE OR REPLACE PACKAGE hr_mgmt AS
    !
    g_active_status CONSTANT VARCHAR2(1) := 'A';
    g_inactive_status CONSTANT VARCHAR2(1) := 'I';
    g_bonus_pct NUMBER;
    .
    .
    ..
    END hr_mgmt;
    /

    @C:\Demo\hr_mgmt.spc

    ALTER PACKAGE hr_mgmt COMPILE SPECIFICATION;


    Do Not Place Anything in
    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    Compiling Package Specification


    Errors & Warnings
    Native Compilation
    PLSQL_OPTIMIZE_LEVEL
    Compile for Debug
    ALTER PACKAGE hr_mgmt COMPILE SPECIFICATION PLSQL_CODE_TYPE=NATIVE;
    ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2;

    ALTER PACKAGE hr_mgmt COMPILE DEBUG SPECIFICATION;


    SELECT PLSQL_WARNINGS,
    PLSQL_OPTIMIZE_LEVEL,
    PLSQL_CODE_TYPE
    FROM
    ALL_PLSQL_OBJECT_SETTINGS
    WHERE NAME= 'HR_MGMT';

    Do Not Place Anything in


    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    Executing Package Specification


    Procedures & Functions

    Cannot Execute Without Body

    ORA-04067: not executed, package body DEMO.HR_MGMT does not exist

    DECLARE
    l_emp_id
    NUMBER(10) := 50;
    l_dept_name VARCHAR2(60):= 'IT';
    BEGIN
    demo.hr_mgmt.update_emp(l_emp_id,l_dept_name);
    END;

    DECLARE
    l_profit
    NUMBER(10) := 100000;
    l_dept_id NUMBER
    := 1;
    l_bonus
    NUMBER;
    BEGIN
    l_bonus:= demo.hr_mgmt.calc_bonus(l_profit, l_dept_id);
    END;

    Do Not Place Anything in


    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    Executing Package Specification


    Types, Variables, Constants,Cursors etc.

    !
    BEGIN
    DBMS_OUTPUT.PUT_LINE(demo.hr_mgmt. g_bonus_pct);
    END;

    DECLARE
    l_dept_id NUMBER;
    BEGIN
    OPEN hr_mgmt.gcur_get_deptid(l_dept_name);
    FETCH hr_mgmt.gcur_get_deptid INTO l_dept_id;
    CLOSE hr_mgmt.gcur_get_deptid;
    END;

    Do Not Place Anything in


    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    Dropping Package Specification

    DROP PACKAGE [schema_name.]<package_name> ;

    DROP PACKAGE demo.hr_mgmt;

    Do Not Place Anything in


    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    Global Variables & Session State

    BEGIN
    DBMS_OUTPUT.PUT_LINE(demo.hr_mgmt. g_active_status);
    END;

    !
    CREATE OR REPLACE PACKAGE hr_mgmt AS
    g_active_status CONSTANT VARCHAR2(1) := 'A';
    g_bonus_pct NUMBER;
    ..
    ..
    END hr_mgmt;
    /

    BEGIN
    hr_mgmt. g_bonus_pct := 10;
    END;

    BEGIN
    DBMS_OUTPUT.PUT_LINE(demo.hr_mgmt. g_bonus_pct);
    END;
    Do Not Place Anything in
    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    Summary

    Need for Packages

    Package Specification
    Contents

    Order of Declaration

    Global Variables &


    Session State

    Do Not Place Anything in


    This Space
    (Add watermark during editing)
    Note: Warning will not appear
    during Slide Show view.

    You might also like