Creating a package begins by creating the package specification.
The following diagram describes the syntax of the CREATE PACKAGE command and shows an example.
Syntax for creating an "Oracle PL/SQL package".
Below is the extracted PL/SQL code from the image:
CREATE [OR REPLACE] PACKAGE packagename → Defines a package specification.
IS | AS → Specifies that the package body is coming next.
Subprograms (PROCEDURE | FUNCTION subprogram_name) → Declares procedures or functions inside the package.
END [packagename]; → Ends the package specification.
The OR REPLACE phrase is optional. Include it when you want to replace the current version of the package when you run the command.
You must specify either IS or AS, whichever you prefer.
Each public sub-program is defined here with the syntax {PROCEDURE I FUNCTION} subprogram_name [(parameter_specs)]. Separate them with semicolons.
CREATE OR REPLACE PACKAGE
CREATE OR REPLACE PACKAGE GET_MONTHEND_DATA IS
PROCEDURE CALC_PROFIT
(I_YEAR IN NUMBER, I_MONTH IN NUMBER, O_PROFIT OUT NUMBER);
FUNCTION CALC_DIVIDENDS
(I_CUTOFF_DATE IN DATE) RETURN NUMBER;
END GET_MONTHEND_DATA;
Location 1
The OR REPLACE phrase is optional. Include it when you want to replace the current version of the package when you run the command.
Location 2
You must specify either IS or AS, whichever you prefer.
Location 3
Each public sub-program is defined here with the syntax{PROCEDURE | FUNCTION}subprogram_name [(parameter_specs)]. Separate them with semicolons.
Separating the Specification and Body
The specification of a package declares the public
types,
variables,
constants, and
subprograms
that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package. Oracle stores the specification and body of a package separately in the database. Other schema objects that call or reference public program objects depend only on the package specification, not on the package body. Using this distinction, you can change the definition of a program object in the package body without causing Oracle to invalidate other schema objects that call or reference the program object.
Oracle invalidates dependent schema objects only if you change the declaration of the program object in the package specification.
Creating a New Package: Example 6-4
The following example shows a package specification for a package named EMPLOYEE_MANAGEMENT.
The package contains one stored function and two stored procedures.
CREATE PACKAGE employee_management AS
FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,
mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,
deptno NUMBER) RETURN NUMBER;
PROCEDURE fire_emp (emp_id NUMBER);
PROCEDURE sal_raise (emp_id NUMBER, sal_incr NUMBER);
END employee_management;
The body for this package defines the function and the procedures:
CREATE PACKAGE BODY employee_management AS
FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,
mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,
deptno NUMBER) RETURN NUMBER IS
The function accepts all arguments for the fields in the employee table except for the employee number.
A value for this field is supplied by a sequence. The function returns the sequence number generated by the call to this function.
new_empno NUMBER(10);
BEGIN
SELECT emp_sequence.NEXTVAL INTO new_empno FROM dual;
INSERT INTO emp VALUES (new_empno, name, job, mgr,
hiredate, sal, comm, deptno);
RETURN (new_empno);
END hire_emp;
PROCEDURE fire_emp(emp_id IN NUMBER) AS
The procedure deletes the employee with an employee number that corresponds to the argument emp_id. If no employee is found, then an exception is raised.
BEGIN
DELETE FROM emp WHERE empno = emp_id;
IF SQL%NOTFOUND THEN
raise_application_error(-20011, 'Invalid Employee
Number: ' || TO_CHAR(emp_id));
END IF;
END fire_emp;
PROCEDURE sal_raise (emp_id IN NUMBER, sal_incr IN NUMBER) AS
The procedure accepts two arguments. Emp_id is a number that corresponds to an employee number. Sal_incr is the amount by which to increase the employee's salary.
BEGIN
-- If employee exists, then update salary with increase.
UPDATE emp
SET sal = sal + sal_incr
WHERE empno = emp_id;
IF SQL%NOTFOUND THEN
raise_application_error(-20011, 'Invalid Employee
Number: ' || TO_CHAR(emp_id));
END IF;
END sal_raise;
END employee_management;
Once you create the package specification, you do not need to recompile it, even if the package body is recompiled. This promotes system
stability because there is less need to recompile applications, such as Oracle Forms, that depend on the package.
The "EMPLOYEE_MANAGEMENT" package specification and body written for Oracle 9i is highly likely to run on "Oracle 19c" without modification. However, there are a few considerations to ensure compatibility:
Compatibility of PL/SQL Constructs
PL/SQL stored functions and procedures: The syntax and functionality used in the package (functions, procedures, `SELECT INTO`, `INSERT INTO`, etc.) remain valid in Oracle 19c.
Sequences (`emp_sequence.NEXTVAL`): The use of sequences in `SELECT INTO` statements is still supported in 19c.
Standard Data Types: The data types used (`VARCHAR2`, `NUMBER`, `DATE`) are still valid in 19c.
Potential Issues and Recommendations
NUMBER(10) Declaration for `new_empno`
Potential Issue: In the `hire_emp` function, `new_empno` is declared as `NUMBER(10)`. However, `NEXTVAL` from a sequence returns a `NUMBER`, and the explicit `NUMBER(10)` might not be necessary.
Recommendation: Use just `NUMBER`, since sequence-generated numbers do not need precision constraints.
Table Structure Compatibility
If the `emp` table structure has changed (e.g., additional `NOT NULL` constraints or new columns in Oracle 19c), the `INSERT INTO emp VALUES (...)` statement might fail.
Recommendation: Ensure that the table structure in Oracle 19c matches the original assumption in 9i.
User Privileges
Ensure that the schema running this package has the required privileges:
`SELECT` on `DUAL`
`INSERT` on `EMP`
`SELECT` on `EMP_SEQUENCE.NEXTVAL`
`DELETE` or `UPDATE` (for `fire_emp` and `sal_raise` procedures)
Performance Considerations
Optimizations in Oracle 19c: Oracle 19c has introduced various optimizer enhancements. While the existing package should work, using PL/SQL result cache or bulk processing might improve performance.
Final Verdict
Does it need modification?
No, unless you want to optimize it.
Recommended Changes (Optional):
Change `new_empno NUMBER(10);` to `new_empno NUMBER;`
Verify that the `EMP` table structure in Oracle 19c is unchanged.
Ensure proper privileges are granted.
The next lesson shows how to complete the package by defining the package body.
