Oracle 10g Packages

Oracle Concepts

Selvaraj V Anna University Chennai. Chennai – 25.

Oracle Packages, Procedures, Functions 1. What is a Package? What are the merits/demerits of packages? A package is nothing but a grouping together of Logically related procedures, functions, variables (global and local) etc. It provides many OOPs features, like information hiding, polymorphism etc. And the biggest advantage is, when any Procedure or function is called from the package, the whole package gets loaded into the memory and so it is very fast to call all related procedures and functions from the same package. But again, it acts as a demerit when very few procedures or functions from the package are used, but still the whole package resides in the memory until the called procedures/functions exit. Regarding OOPs concepts, a Package hides the information about a Procedure or function to the outside world. That means, a user can use the inbuilt procedures/functions without knowing how it is built in the package. Again, a procedure/function could be written private, i.e they could not be accessed from outside the package but could be used by other procedures/functions inside the same package. I shall explain this later in this page. So, Merits: Packaged procedures/functions could exhibit polymorphism. Could create Private variables, functions, procedures etc inside the package; not accessible outside.Could share variables and private procedures/functions between other procedures/functions. Whole package is loaded into memory whenever any procedure/function is called. Benefit when 80-90% of the procedures/functions in the package are used. Demerit: The main demerit is again, the whole package is loaded into memory whenever any procedure/function is called. If only 20% of the procedures/functions in the package are used, memory consumption gets high for minimal package usage 2. Package, Procedure and Function Syntax: A package has got 2 components - a Spec and a Body Spec : is nothing but is like a header which holds all Procedure/Function declarations and Global variables, collections etc if used.

Body: body has the actual definition of the stored procedures/Functions. All Data manipulation is done in the procedures and functions, which are defined in the body. Eg: --Spec:

CREATE OR REPLACE PACKAGE EX_PACK_001 package spec AS PROCEDURE EX_PROC_001 procedure EX_PROC_001 ( P_NUMBER IN P_CHAR IN P_RET_CODE OUT ); FUNCTION EX_FUNC_001 EX_FUNC_001 ( P_NUMBER IN P_CHAR IN ) RETURN NUMBER;

--Create

--declaring

NUMBER, VARCHAR2, NUMBER

--declaring

NUMBER, VARCHAR2

END EX_PACK_001; / --Body:

CREATE OR REPLACE PACKAGE BODY EX_PACK_001 AS PROCEDURE EX_PROC_001(P_NUMBER IN NUMBER, P_CHAR VARCHAR2, P_RET_CODE OUT NUMBER) IS BEGIN --CODE HERE-EXCEPTION

IN

function

--EXCEPTION BLOCK-END EX_PROC_001; FUNCTION EX_FUNC_001(P_NUMBER IN NUMBER, P_CHAR IN VARCHAR2) RETURN NUMBER IS V_CODE NUMBER; BEGIN --CODE HERE RETURN V_CODE; EXCEPTION --EXCEPTION BLOCK-END EX_FUNC_001; END EX_PACK_001; 3. How to call the packaged procedures/functions in another Program. Before showing how to call procedures/functions in another Program, I hope that you know what an anonymous block is. For those who dont know, an anonymous block is a piece of code that is similar to a procedure but doesnt have any name or cannot be stored in the database. For eg: the anonymous block below is used to call our procedure and function, but could not be stored in the DB like the Package, procedure or function. Declare v_code number; v_number number; v_char varchar2(50); Begin v_number:=100; v_char:=Hai; EX_PACK_001. EX_PROC_001(v_number,v_char,v_code); v_code is an OUT parameter

--

dbms_output.put_line(v_code); returned to the OUT param

--displays

v_code:= EX_PACK_001. EX_FUNC_001(v_number,v_char); function returns the code dbms_output.put_line(v_code);

data

--

end; 4. Packaged Procedures versus Normal Procedures As I had mentioned earlier, packaged procedures can exhibit some of the OOPs behavior. One main character is polymorphism. In the same package, we can have different procedures/functions with the same name but different signatures. Signature is nothing but the parameter list. 2 procedures with same name could exist in the same package with different number of parameters or different datatypes for parameters. For eg: a. Differs in number of parameters

CREATE OR REPLACE PACKAGE EX_PACK_001 showing only spec AS PROCEDURE EX_PROC_001 ( P_NUMBER IN NUMBER, P_CHAR IN VARCHAR2, P_RET_CODE OUT NUMBER ); PROCEDURE EX_PROC_001 ( P_NUMBER IN NUMBER, P_CHAR IN VARCHAR2 ); END EX_PACK_001; /

--

b. Differs in datatype of parameters, but same number of parameters CREATE OR REPLACE PACKAGE EX_PACK_001 showing only spec

--

AS PROCEDURE EX_PROC_001 ( P_NUMBER IN NUMBER, P_CHAR IN VARCHAR2, P_RET_CODE OUT NUMBER ); PROCEDURE EX_PROC_001 ( P_CHAR1 IN VARCHAR2, P_CHAR2 IN VARCHAR2, P_RET_CODE OUT NUMBER ); END EX_PACK_001; / 5. Procedure Versus Function a. b.

Only function can Return a value (to be precise using the Return keyword) Procedures can use Return keyword but without any value being passed

c. Functions could be used in select statements, provided they doesn’t do any data manipulation inside and also should not have any OUT, IN OUT parameters. 6. How to create Private global variables in a Package? Create variables inside the body (but outside any procedure or function). All of the functions/procedures in the package can use the variable, but could not be used outside 7. How to create Private Procedures/Functions? Write the procedures/functions only in the body. That means, do not declare the procedure/function in the Spec. 8. Can a Package Spec exist without a body?

Yes. Advantage being, we can create global variables or collections and put them in a package Spec. No need to create any body since there are no procedures/functions. Eg:

CREATE OR REPLACE PACKAGE EX_PACK_001 AS V_RECORDS NUMBER; END EX_PACK_001; This variable could be called outside in any code like this: EX_PACK_001.v_records:=1000;