Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out


. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |


Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 


Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 | Lesson 19 | Lesson 20 | Lesson 21 |


Lesson 15

￿A person reveals his character by nothing so clearly as the joke he resents.￿ Georg Christoph Lichtenberg (1742 - 1799)


Read first then play the video:

   PLS-VIDEO -Creating PL/SQL Package


Section 4: Package

Create a Packages


Hands-On introduction

In this Hands-On, you create a package by copy and pasting an existing PL/SQL stored procedure. Then save it in the local library or the database server.


A package is a database construct that allows users to collect many program units into one database object. They consist of two components: a package specification and a package body.


Create a Package Specification


A package should have a PL/SQL package specification and a PL/SQL package body. A ￿PL/SQL package specification￿ contains all your PL/SQL functions header, procedures header, type, variables, etc. A ￿PL/SQL package body￿ contains a complete PL/SQL stored procedures or functions. All the declared PL/SQL functions, procedures, variables, etc in a package specification are called public procedures and functions. They can be accessible to the users who have privilege to execute them. In the PL/SQL package specification, all the functions and procedures must have a PL/SQL procedure in its PL/SQL package body. It is not necessary that all the PL/SQL procedures in a PL/SQL package body have a specification entry in its PL/SQL package specification. Those PL/SQL procedures that have not have any specification entry in the PL/SQL package specification called private PL/SQL procedures.


Select "Program Units" and then click on the ￿Create￿ icon to create a package that contains all of department's functions and procedures.


Name the package (package department) and select package specification. Then click "OK"

Write or ￿copy and paste￿ all functions￿ and procedures￿ header for public use.


Open the add_dept procedure and copy it header specification into the package specification. Do not forget to add the ";"


Repeat this process for any other stored procedures, if needed.


(Procedure Builder)


PACKAGE pkg_dept


    -- No variables


    -- This is the add_dept specification￿

    PROCEDURE add_dept

        (p_dept_rec IN dept%ROWTYPE,

       p_status OUT VARCHAR2);


    -- This is the remove_dept specification￿

    PROCEDURE remove_dept

        (p_deptno IN dept.depno%TYPE,

        p_status OUT VARCHAR2);


    -- Add more and more￿


END pkg_dept;


Add more headers if needed. These two programs are going to be enough to make the point.


Compile a package specification

Compile the package specification. No strike means the compilation was successful.


Create a package body

Create a package body. Name the package body the same as its package specification name. Select ￿Package Body.￿ Then click ￿OK.￿


Copy and paste the entire stored procedures into the package body. Repeat the copy process for other stored procedures if needed.


(Procedure Builder)





    -- Add department procedure￿

    PROCEDURE add_dept

        (p_deptno IN dept.deptno%TYPE,

        p_dname IN dept.dname%TYPE,

        p_loc IN dept.loc%TYPE,

        p_status OUT VARCHAR2)


        -- No variable



        /* This program add dept. record. */

        INSERT INTO dept

            VALUES (p_deptno, p_dname, p_loc);


        --- Save record.



        -- Added successfully if the get to this line.

        p_status := ￿OK￿;




        -- Check for an Unique or Primary Key

        WHEN dup_val_on_index THEN

            p_status := ￿DUPLICATE RECORD￿;

        -- Check for invalid input data

        WHEN invalid_number THEN

            p_status := ￿INVALID INPUT DATA￿;

        -- Check for any other problems

        WHEN others THEN

            p_status := ￿CHECK THIS WE HAVE UNKNOWN PROBLEM.￿;


    END add_dept;



    -- Remove department procedure￿

    PROCEDURE remove_dept

        (p_deptno IN dept.deptno%TYPE,

        p_status OUT VARCHAR2)



        -- Delete a record

        DELETE FROM dept

            WHERE deptno = p_deptno;


        -- Save the transaction.



        -- Check the status.

        p_status := ￿OK￿;




        WHEN no_data_found THEN

            p_status := ￿NO DATA FOUND.￿;

        WHEN others THEN

            p_status := ￿Other Problems.￿;


END remove_dept;


-- And more internal procedures.


END pkg_dept;



Compile a package body

Compile the package body. No strike means: successfully compiled.

Make a syntax error and compile again. Read the error messages. Then correct the error and compile it again.


Then close the window.

Run and test a procedure or function in a package

Query the department table.



Write a PL/SQL procedure block to use the remove procedure to delete the department number 40.



    v_status VARCHAR2(40);


    pkg_dept.remove_dept(40, v_status);




Then output the status parameter to see that the transaction was successfully deleted.

￿OK￿ means: it was successfully deleted.


Query the department table again.


Record was deleted.


Save a package specification and body into the database server

Store the package into the database server.

First store the "package specification" then store the "package body" unit.


￿Without friends no one would choose to live, though he had all other goods.￿ Aristotle (384 BC - 322 BC), Nichomachean Ethics



Q: What is the PL/SQL package?

Q: What are the components of a PL/SQL package?

Q: What is a package body in the PL/SQL language?

Q: What is a package specification in the PL/SQL language?

Q: Where do you save the package body and its package specification?

Q: Can you store a PL/SQL package in a client environment?

Q: How do you create a package specification and body?

Q: What are the dependencies between a package body and its package specification?

Q: Write a PL/SQL package to have all your created PL/SQL functions and procedures?

Q: What is a public PL/SQL procedure or function in a PL/SQL package?

Q: What is a private PL/SQL procedure or function in a PL/SQL package?

Q: What are the differences between a public or private PL/SQL procedure?

Q: How do you run a PL/SQL procedure or function in a PL/SQL package?