Thursday, December 21, 2017

Oracle  Database Function
( Create & Call )


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,


      This is just an example of  Oracle Database Function to show you how can we create and call it as an individual database function not related to any PL/SQL package. Later on, In shaa Allah, we will declare and implement it with it's related database functions in a new package called a trans_pkg. 
Now, let's start the current task definition.


Task Definition:   

      1- Get the maximum quantity for each item stored in a specific  storage area.
     2- Update its maximum quantity in  the transaction table. 


Task Solution:

     Create a database function that returns the maximum quantity stored of a specific item. Please, follow the below sample code and read the comments  for each line:


 
CREATE OR REPLACE FUNCTION  UPDATE_QTY   
  ( p_store_id  NUMBER  ,   p_item_serial   NUMBER   ) 
    RETURN NUMBER IS
    v_max    NUMBER := 0;
 
BEGIN
       BEGIN
           SELECT  NVL( MAX( balance_qty), 0 )
            FROM   wh_t_items
            WHERE  store_id    = p_store_id  
            AND    item_serial = p_item_serial  ;  
     
EXCEPTION
WHEN OTHERS THEN
   RAISE_APPLICATION_ERROR(-20001,'An error was encountered - 
      '||SQLCODE||' -ERROR- '||SQLERRM);
 END ; 
 RETURN v_max    ; 
END  UPDATE_QTY   ; 
/


Note:

    RAISE_APPLICATION_ERROR  is actually a procedure that lets you define both error number from  -20,000  up to -20,999 and its customized message.


Oracle Database Function Call

Let's  call the stored database function from Oracle  form using 
e.g. WHEN-BUTTON-PRESSED Trigger or any appropriate trigger to execute the database function code.

 
DECLARE
  v_max  NUMBER := 0;
BEGIN
 -- Call  the database function to return the changes made.
 v_max := Update_Qty   (:block_name.store_id , :block_name.item_serial);
 -- Assign the returned function 's value to the form item.
:block_name.max_balance :=  v_max;
END;



Now, we could get the maximum item quantity in a specific store.


Learn more about:


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.