PL/SQL-PACKAGE - 1

[debasisdas]

The following thread contains some useful tips/sample codes regarding PACKAGES in oracle, that the forum members may find useful.

A package is a collection of procedures,func tions,cursors,g lobal variables etc.

A package is also stored as an Object in the database.

A package ha s two parts
1. Package-------------Specification.
2. Package body---Implimentaton.

Note:-
1.If the package does not include any procedure or functions then the package body is optional.
2.All the variables declared in the package declaration block have global scope.

At the time of execution the package members must be qualified by the package name.

It supports OVERLOADING.

Once any member of the package is called the entire package gets loaded into the memory there ay avoiding additional roundtrips .

SAMPLE PACKAGE EX#1
=============== =====
package specification
-----------------------------------

CREATE OR REPLACE PACKAGE PACK1 AS
--The procedure is only declared with parametres.
PROCEDURE PROC1
(
NUM EMP.EMPNO%TYPE,
SS EMP.SAL%TYPE
);
--Function is declared here.
FUNCTION  SPELLDATE
(
DD DATE
)
RETURN VARCHAR2;
END;
 

The body part(implementa tion)
-----------------------------------------------

CREATE OR REPLACE PACKAGE BODY PACK1 AS
--Here is the implimentation of the previously declared Pocedure.
PROCEDURE PROC1
(
NUM EMP.EMPNO%TYPE,
SS EMP.SAL%TYPE
)
AS
BEGIN
INSERT INTO EMP (EMPNO,SAL)VALUES(NUM,SS);
DBMS_OUTPUT.PUT_LINE('ONE RECORD INSERTED...!');
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20001,'DUPLICATE ENTRY');
END PROC1;
--and the function is implemented here.
FUNCTION SPELLDATE
(
--receives date as input.
DD DATE
) RETURN VARCHAR2 AS
STR VARCHAR2(500);
BEGIN
SELECT TO_CHAR(DD,'DDSPTH MONTH YEAR') INTO STR FROM DUAL;
--returns the fully spelled out string of the entered date.
RETURN STR;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20001,'SOME ERROR');
--If there is any error returns null.
RETURN NULL;
END SPELLDATE;
END PACK1;
 

SAMPLE PACKAGE EX#2
=============== =====
PACKAGE SPECIFICATION
--------------------------------------------

create or replace package my_pkg as
procedure my_proc(arg1 in varchar2);
function my_fun(arg1 in number) return varchar2;
end my_pkg;
 

-------------
PACKAGE BODY
----------------------------

create or replace package body my_pkg as
 
function my_private_fun(arg1 in number) return varchar2
is
return_val varchar2(20);
begin
select col1 into return_val from tab2 where col2=arg1;
return return_val;
exception
when NO_DATA_FOUND THEN
return 'Sorry no data found.....!';
end  my_private_fun;
 
function my_fun(arg1 in number)return varchar2
is
begin
return my_private_fun(arg1);
end my_fun;
 
procedure my_proc(arg1 in varchar2)
is
begin
update tab1 set col1=col1+1 where col2=arg1;
end my_proc;
end my_pkg;
 

Also check PL/SQL-PACKAGE - 2