<

Packages in Oracle

Published on
9,361 Points
3,261 Views
1 Endorsement
Last Modified:
Approved
Community Pick

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).
A simple example:
I have tried to create a new package with a Constant, an user defined Type, a simple Procedure and a simple Function.
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;

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)
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 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;

Open in new window


Finally, how to call them or make use of them?

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 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;

Open in new window


And, now the advantages of using a Package?


It offers you Modularity.
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_Name 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.

Read more on: Oracle Packages

Best Regards,
Senthur Pandian R

1
Comment
Author:StealthyDev
1 Comment
LVL 61

Expert Comment

by:Kevin Cross
Yes from me, I look forwarding to reading more of your Articles in the future. Probably liked it, being a Java programmer, as your comparisons with Abstract and implementation classes fit perfect.

Regards,
Kevin
0

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month