469,275 Members | 1,817 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

PL/SQL-PACKAGE - 1

debasisdas
8,127 Expert 4TB
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,functions,cursors,global 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
-----------------------------------
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PACKAGE PACK1 AS
  2. --The procedure is only declared with parametres.
  3. PROCEDURE PROC1
  4. (
  5. NUM EMP.EMPNO%TYPE,
  6. SS EMP.SAL%TYPE
  7. );
  8. --Function is declared here.
  9. FUNCTION  SPELLDATE
  10. (
  11. DD DATE
  12. )
  13. RETURN VARCHAR2;
  14. END;
  15.  
The body part(implementation)
-----------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE PACKAGE BODY PACK1 AS
  2. --Here is the implimentation of the previously declared Pocedure.
  3. PROCEDURE PROC1
  4. (
  5. NUM EMP.EMPNO%TYPE,
  6. SS EMP.SAL%TYPE
  7. )
  8. AS
  9. BEGIN
  10. INSERT INTO EMP (EMPNO,SAL)VALUES(NUM,SS);
  11. DBMS_OUTPUT.PUT_LINE('ONE RECORD INSERTED...!');
  12. EXCEPTION
  13. WHEN DUP_VAL_ON_INDEX THEN
  14. RAISE_APPLICATION_ERROR(-20001,'DUPLICATE ENTRY');
  15. END PROC1;
  16. --and the function is implemented here.
  17. FUNCTION SPELLDATE
  18. (
  19. --receives date as input.
  20. DD DATE
  21. ) RETURN VARCHAR2 AS
  22. STR VARCHAR2(500);
  23. BEGIN
  24. SELECT TO_CHAR(DD,'DDSPTH MONTH YEAR') INTO STR FROM DUAL;
  25. --returns the fully spelled out string of the entered date.
  26. RETURN STR;
  27. EXCEPTION
  28. WHEN OTHERS THEN
  29. RAISE_APPLICATION_ERROR(-20001,'SOME ERROR');
  30. --If there is any error returns null.
  31. RETURN NULL;
  32. END SPELLDATE;
  33. END PACK1;
  34.  


SAMPLE PACKAGE EX#2
====================
PACKAGE SPECIFICATION
--------------------------------------------
Expand|Select|Wrap|Line Numbers
  1. create or replace package my_pkg as
  2. procedure my_proc(arg1 in varchar2);
  3. function my_fun(arg1 in number) return varchar2;
  4. end my_pkg;
  5.  
-------------
PACKAGE BODY
----------------------------
Expand|Select|Wrap|Line Numbers
  1. create or replace package body my_pkg as
  2.  
  3. function my_private_fun(arg1 in number) return varchar2
  4. is
  5. return_val varchar2(20);
  6. begin
  7. select col1 into return_val from tab2 where col2=arg1;
  8. return return_val;
  9. exception
  10. when NO_DATA_FOUND THEN
  11. return 'Sorry no data found.....!';
  12. end  my_private_fun;
  13.  
  14. function my_fun(arg1 in number)return varchar2
  15. is
  16. begin
  17. return my_private_fun(arg1);
  18. end my_fun;
  19.  
  20. procedure my_proc(arg1 in varchar2)
  21. is
  22. begin
  23. update tab1 set col1=col1+1 where col2=arg1;
  24. end my_proc;
  25. end my_pkg;
  26.  

Also check PL/SQL-PACKAGE - 2
Jun 7 '07 #1
0 3621

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Peter | last post: by
10 posts views Thread by Dagwood | last post: by
6 posts views Thread by Fuzzydave | last post: by
14 posts views Thread by Developer | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.