Thursday, December 21, 2017

Oracle PL/SQL Packages

      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:

·          Handling Oracle Forms Sequence


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.



Post a Comment