In this Hands-On, you create a package by copy and pasting an existing PL/SQL stored procedures.  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.

You will:

    1- Create a Package Specification.

    2- Create a Package Body.

    3- Copy and paste a stored program units’ header to a Package Specification.

    4- Copy and paste a stored program units to a Package Body.

    5- Compile the Package Specification.

    6- Compile the Package Body.

    7- Run and test the package

    8- Store packages locally or on database server.

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.

=

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

=

Compile the package specification.

No strike means the compilation was successful.

=

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.

=

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.

===

Query the department table.

=

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

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.

=

Store the package into the database server.

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

=

Now, you should practice this over and over, until you become a master at it.

Good Luck!