iSelfSchooling.com  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

Introduction

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

IS

    -- 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)

 

PACKAGE BODY pkg_dept

IS

 

    -- 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)

    IS

        -- No variable

    BEGIN

 

        /* This program add dept. record. */

        INSERT INTO dept

            VALUES (p_deptno, p_dname, p_loc);

 

        --- Save record.

        COMMIT;

 

        -- Added successfully if the get to this line.

        p_status := ‘OK’;

 

    EXCEPTION

 

        -- 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)

 

    IS

        -- Delete a record

        DELETE FROM dept

            WHERE deptno = p_deptno;

 

        -- Save the transaction.

        COMMIT;

 

        -- Check the status.

        p_status := ‘OK’;

 

    EXCEPTION

 

        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.

PL/SQL> SELECT * FROM dept;

 

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

PL/SQL>

DECLARE

    v_status VARCHAR2(40);

BEGIN

    pkg_dept.remove_dept(40, v_status);

    TEXT_IO.PUT_LINE(v_status);

END;

 

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

“OK” means: it was successfully deleted.

 

Query the department table again.

PL/SQL> SELECT * FROM dept;

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

 

Questions:

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?