Creating PL/SQL packages

You can create a PL/SQL package in a data development project from the Data Project Explorer.

Before you begin

Ensure the following are created:
  1. A connection to a database that supports PL/SQL.
  2. A project.

Procedure

To create a PL/SQL package:

  1. From the Data Project Explorer, expand a schema object.
  2. Select the PL/SQL Packages folder and click New > PL/SQL Package. The New PL/SQL Package wizard opens.
  3. On the Name page, specify the project that this PL/SQL package is in, or click New to create a new project.
  4. Type the new package name or use the default name.
  5. Click Next to view the Summary page.
  6. Click Finish. The new PL/SQL package appears in the project, and the PL/SQL Package editor opens, and displays the Specification and Body pages.

    A package specification establishes which package objects can be referenced from outside of the package; it specifies routines and declares exceptions.

    A package body contains the implementation of all of the procedures and functions that are declared within the package specification.

  7. Create the PL/SQL package specification and body.
    1. From the Specification page, enter the routines and exceptions that are to be implemented by the body. For example:
      CREATE OR REPLACE PACKAGE emp_admin
      IS
      
         FUNCTION get_dept_name (
            p_deptno        NUMBER DEFAULT 10
         )
         RETURN VARCHAR2;
         FUNCTION update_emp_sal (
            p_empno         NUMBER,
            p_raise         NUMBER
         )
         RETURN NUMBER;
         PROCEDURE hire_emp (
            p_empno         NUMBER,
            p_ename         VARCHAR2,
            p_job           VARCHAR2,
            p_sal           NUMBER,
            p_hiredate      DATE DEFAULT sysdate,
            p_comm          NUMBER DEFAULT 0,
            p_mgr           NUMBER,
            p_deptno        NUMBER DEFAULT 10
         );
         PROCEDURE fire_emp (
            p_empno         NUMBER
    2. From the Body page, enter the implementation of the routines. For example:
      --
      --  Package body for the 'emp_admin' package.
      --
      CREATE OR REPLACE PACKAGE BODY emp_admin
      IS
         --
         --  Function that queries the 'dept' table based on the department
         --  number and returns the corresponding department name.
         --
         FUNCTION get_dept_name (
            p_deptno        IN NUMBER DEFAULT 10
         )
         RETURN VARCHAR2
         IS
            v_dname         VARCHAR2(14);
         BEGIN
            SELECT dname INTO v_dname FROM dept WHERE deptno = p_deptno;
            RETURN v_dname;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               DBMS_OUTPUT.PUT_LINE('Invalid department number ' || p_deptno);
               RETURN '';
         END;
         --
         --  Function that updates an employee's salary based on the
         --  employee number and salary increment/decrement passed
         --  as IN parameters.  Upon successful completion the function
         --  returns the new updated salary.
         --
         FUNCTION update_emp_sal (
            p_empno         IN NUMBER,
            p_raise         IN NUMBER
         )
         RETURN NUMBER
         IS
            v_sal           NUMBER := 0;
         BEGIN
            SELECT sal INTO v_sal FROM emp WHERE empno = p_empno;
            v_sal := v_sal + p_raise;
            UPDATE emp SET sal = v_sal WHERE empno = p_empno;
            RETURN v_sal;
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               DBMS_OUTPUT.PUT_LINE('Employee ' || p_empno || ' not found');
               RETURN -1;
            WHEN OTHERS THEN
               DBMS_OUTPUT.PUT_LINE('The following is SQLERRM:');
               DBMS_OUTPUT.PUT_LINE(SQLERRM);
               DBMS_OUTPUT.PUT_LINE('The following is SQLCODE:');
               DBMS_OUTPUT.PUT_LINE(SQLCODE);
               RETURN -1;
         END;
         --
         --  Procedure that inserts a new employee record into the 'emp' table.
         --
         PROCEDURE hire_emp (
            p_empno         NUMBER,
            p_ename         VARCHAR2,
            p_job           VARCHAR2,
            p_sal           NUMBER,
            p_hiredate      DATE    DEFAULT sysdate,
            p_comm          NUMBER  DEFAULT 0,
            p_mgr           NUMBER,
            p_deptno        NUMBER  DEFAULT 10
         )
         AS
         BEGIN
            INSERT INTO emp(empno, ename, job, sal, hiredate, comm, mgr, deptno)
               VALUES(p_empno, p_ename, p_job, p_sal,
                      p_hiredate, p_comm, p_mgr, p_deptno);
         END;
         --
         --  Procedure that deletes an employee record from the 'emp' table based
         --  on the employee number.
         --
         PROCEDURE fire_emp (
            p_empno         NUMBER
         )
         AS
         BEGIN
            DELETE FROM emp WHERE empno = p_empno;
         END;
      END;
  8. Save the package.

Results

The new PL/SQL package contains a specification and a body.


Feedback