Packages in Oracle

Packages in Oracle -- with simple example

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:

1

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 Abstract Class.
It contains the declaration of all the Variables, Cursors, Functions and Procedures (Only declarations).

CREATE OR REPLACE PACKAGE SENTHUR
                      AS
                      	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;

Select all Open in new window

2

Package Body

Here is where we give the Definition (Implementation) of Functions and Procedures being declared in the Package Header.
We must implement all the Functions and Procedures being declared in the Procedure Header. (If you are familiar in Java, it's like Concrete Sub-Class)

CREATE OR REPLACE PACKAGE BODY SENTHUR
                      AS
                      	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;

Select all Open in new window

SET SERVEROUTPUT ON;
                      -- Execution of PROC1
                      EXECUTE SENTHUR.PROC1;
                      
                      -- Execution of FUNC1
                      SELECT SENTHUR.FUNC1 FROM DUAL;
                      
                      SET SERVEROUTPUT ON;
                      VARIABLE iOut NUMBER;
                      -- Execution of PROC2
                      DECLARE
                      	vVar1 VARCHAR2(100) := 'Test';
                      BEGIN
                      	SENTHUR.PROC2 (vVar1, :iOut);
                      	DBMS_OUTPUT.PUT_LINE('iOut is: ' || :iOut);
                      END;
                      
                      -- Execution of FUNC2
                      SELECT SENTHUR.FUNC2(5) FROM DUAL;

Select all Open in new window

And, now the advantages of using a Package?



Read more on: Oracle Packages

Best Regards,
Senthur Pandian R