Oracle PL/SQL Package

Oracle PL/SQL Package

      In the name of Allah, Most Gracious, Most Merciful

Praise be to Allah, blessing and peace be upon our prophet Mohammed, his family and his companions. After that,

      It's really a good practice to relay on PL/SQL Packages. Oracle bundle related PL/SQL programming resources into PL/SQL Packages.

It comprises  all the database triggers, procedures, functions and  etc.

PL/SQL Packages Advantage :

1.     Modularity:  One package  for a specific module or function.

2.      Security:      Granting privileges enhance security.

3.     Better Performance:  Loading multiple objects into the memory at once helps in reducing the traffic and increasing the performance.

 Task Definition:   

    Create PL/SQL Package  to group all related database  functions expected to return the name or the descriptions of all lookup tables created within  your application.

Task Solution:

 Well, let's assume  we have already created lookup tables.

Lookup Tables; mainly include code or id as a primary key (pk) and name or description for its related primary key.  

 Create PL/SQL Package  specification code for defining its affiliated database functions.

Please, follow the sample code and read the comments  for each line:

Package Specification Code

  

CREATE OR REPLACE PACKAGE  lookup_pkg IS

   FUNCTION  get_emp_name (p_empno  NUMBER) RETURN VARCHAR2 ;

END;                                                                   

/

      Package Body Code 

  CREATE OR REPLACE PACKAGE BODY lookup_pkg  IS

           FUNCTION get_emp_name  (p_empno  NUMBER) RETURN VARCHAR2

           IS

         v_name    VARCHAR2(333);

BEGIN

         BEGIN

               SELECT ename    

                 INTO v_name

                FROM emp

                  WHERE empno = p_empno;

       RETURN  v_name;

EXCEPTION

       WHEN OTHERS  THEN

        RETURN 'No such employee exists';

END;

END   get_emp_name;

END  lookup_pkg  ;
/

   Now the package is ready to use it from any environment such as:

•      Oracle Apex (we will discuss this latter).
•    Oracle Form e.g. POST-QUERY  Trigger.
•    Oracle Report e.g. Formula column.

Pl/SQL Program calling Package

         BEGIN

               SELECT lookup_pkg.get_emp_name  (empno )  

                 INTO  :ename    

                FROM  dual;

        EXCEPTION

                     WHEN NO_DATA_FOUND THEN

                     MESSAGE ('No such employee exists');

         END;

Note:  

     The dual table would output only one row. For any reason, if you want to pass multiple rows on emp_id  to execute the function-package you can  call the PL/SQL Package Function in the Select statement  as a pre-defined table column in your  database schema as follow:

         BEGIN

               SELECT lookup_pkg.get_emp_name  (emp_id) , etc.

                 INTO  :emp_name , etc.   -- etc. means other table columns.

                FROM  emp;

        EXCEPTION

                     WHEN NO_DATA_FOUND THEN NULL;

                         END;

Note: 

       On the same patter, you can use this concept for all your lookup tables to get names, or descriptions of related code or id which has a foreign key relation with the target table.

Learn more about:

·            Oracle Database Function ( Create & Call )

·          Handling Oracle Forms Sequence

·          Calling a secured Oracle Report 10g

Hope this helps,

My success only comes from Allah, pls. note your comments and suggestions are great help for me in progress thanks in advance.