<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Packages in Oracle

Published on
9,409 Points
3,309 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
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

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
Via a live example, show how to take different types of Oracle backups using RMAN.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month