Hello everyone, this is my first article in Experts-Exchange forum. - Hope someone will get helped !
Q: What is a package and what is it intended to do?
A: A package is a collection of Variables, Functions, Procedures, Cursors and SQL Statements.
Definition: A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body fully defines cursors and subprograms, and so implements the spec. -- source: Oracle Packages Reference
Package in oracle has two parts:
Package Header (or Package Specification)
It is the header of a package.
Here is where we declare the structure of the Package.
If you are familiar with Java, its similar to
It contains the declaration of all the Variables, Cursors, Functions and Procedures (
A simple example:
I have tried to create a new package with a
Constant, an user defined
Type, a simple
Procedure and a simple
CREATE OR REPLACE PACKAGE SENTHURAS TestConstant CONSTANT INT := 1000; TYPE TestType IS RECORD (VAR1 VARCHAR2(100), INT1 INT); PROCEDURE PROC1; FUNCTION FUNC1 RETURN VARCHAR2; PROCEDURE PROC2(VAR1 VARCHAR2, INT1 OUT INT); FUNCTION FUNC2(INT1 INT) RETURN INT;END SENTHUR;
Here is where we give the Definition (Implementation) of Functions and Procedures being declared in the Package Header.
must implement all the Functions and Procedures being declared in the Procedure Header. (If you are familiar in Java, it's like
A simple example:
Here, I have tried to demonstrate, how to use the Constant defined in the Package and how all the Package-functions and procedures must be implemented.
CREATE OR REPLACE PACKAGE BODY SENTHURAS PROCEDURE PROC1 IS BEGIN -- TestConstant is a constant in the package level -- Can also be accessed using SENTHUR.TestConstant DBMS_OUTPUT.PUT_LINE('Test message - ' || TestConstant); END PROC1; FUNCTION FUNC1 RETURN VARCHAR2 IS BEGIN RETURN 'Test return'; END FUNC1; PROCEDURE PROC2(VAR1 VARCHAR2, INT1 OUT INT) IS BEGIN INT1 := 100; END PROC2; FUNCTION FUNC2(INT1 INT) RETURN INT IS BEGIN RETURN INT1; END FUNC2;END SENTHUR;
In the below statements I have tried to demonstrate how to call (or use) a function or procedure implemented inside a procedure
SET SERVEROUTPUT ON;-- Execution of PROC1EXECUTE SENTHUR.PROC1;-- Execution of FUNC1SELECT SENTHUR.FUNC1 FROM DUAL;SET SERVEROUTPUT ON;VARIABLE iOut NUMBER;-- Execution of PROC2DECLARE vVar1 VARCHAR2(100) := 'Test';BEGIN SENTHUR.PROC2 (vVar1, :iOut); DBMS_OUTPUT.PUT_LINE('iOut is: ' || :iOut);END;-- Execution of FUNC2SELECT SENTHUR.FUNC2(5) FROM DUAL;
It offers you encapsulation. You can make use of private/public scopes to make a sub-program hidden or visible (Hiding implementation details).
Referencing (or calling) a procedure is simple as: Package_Name.Procedure_Nam
e or Function_Name.
Better Performance - Only the first time load of a Package is costlier and time consuming.
But after the first load/initialization, the execution of Procedures or Functions will be much faster and impressive.
They will be pre-compiled and pre-loaded into memory.