You can create a package by organizing your procedures and functions to form a suitcase of PL/SQL objects.
Packages can contain variables that are persistent for a user's entire session (akin to a server-side GLOBAL variable and just as useful), as well as cursors that may remain open across calls to functions and procedures in the package.
Note: The first time a package is called (by any user on the instance) the entire package is loaded into the Oracle SGA to make subsequent invocation of any procedures or functions it contains very fast.
Restrictions Oracle Forms provides direct support for invoking stored procedures and functions, but not for directly referencing package variables or package cursors. To manipulate package variables and package cursors within the code on the server side, you must design a function or procedure. The available data types are:
If you use these data types to write functions or procedures, values of package variables and fetched cursor data can be returned to the Oracle Forms caller via parameters or a function return value.
Oracle Forms requires that both Package Specification and Body be valid in order to compile an Oracle Forms PL/SQL block that makes reference to the package.
CREATE OR REPLACE PACKAGE LIB_HR AS
-- Retrieve Social Security Number for an Employee
FUNCTION get_ssn(theEmpNo NUMBER) RETURN NUMBER;
-- Hire a new Employee and return his/her Employee Number
FUNCTION hire_employee(theName VARCHAR2,
theDept NUMBER,
theSal NUMBER,
theDate DATE,
theSSN NUMBER) RETURN NUMBER;
-- Terminate an existing Employee
PROCEDURE fire_employee(theEmpno NUMBER,
theReason VARCHAR2);
-- Set Signing Bonus Variable
PROCEDURE set_bonus(newValue NUMBER);
END LIB_HR;
CREATE OR REPLACE PACKAGE BODY LIB_HR AS
-- Package Variables --
signingBonus NUMBER := 1000;
-- Package Cursor --
CURSOR next_empid
IS SELECT empid_sequence.NEXTVAL
FROM dual;
-- Retrieve Social Security Number for an Employee
FUNCTION get_ssn(theEmpNo NUMBER) RETURN NUMBER IS
tmpSSN NUMBER;
BEGIN
SELECT ssn
INTO tmpSSN
FROM emp
WHERE empno = theEmpNo;
RETURN(tmpSSN);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN(-1);
END;
-- Hire a new Employee and return Employee Number
FUNCTION hire_employee(theName VARCHAR2,
theDept NUMBER,
theSal NUMBER,
theDate DATE,
theSSN NUMBER) RETURN NUMBER IS
tmpEmpNo NUMBER;
BEGIN
IF (NOT next_empid%ISOPEN) THEN
OPEN next_empid;
END IF;
FETCH next_empid INTO tmpEmpNo;
-- We don't have to CLOSE the cursor because it will
-- remain open between calls
INSERT INTO EMP(empno, ename, deptno, sal,
hireDate, ssn, bonus)
VALUES(tmpEmpNo, theName, theDept, theSal,
theDate, theSSN, signingBonus);
RETURN(tmpEmpNo);
EXCEPTION
WHEN OTHERS THEN
RETURN(-1);
END;
-- Terminate an existing Employee
PROCEDURE fire_employee(theEmpno NUMBER,
theReason VARCHAR2) IS
BEGIN
DELETE FROM emp
WHERE empno = theEmpno;
INSERT INTO terminations(empno, reason)
VALUES(theEmpno, theReason);
END;
-- Set Signing Bonus Variable
PROCEDURE set_bonus(newValue NUMBER) IS
BEGIN
signingBonus := newValue;
END;
END LIB_HR;