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_id   NUMBER   ) 

    RETURN NUMBER IS

    v_max    NUMBER := 0;

BEGIN

       BEGIN

           SELECT  NVL( MAX( balance_qty), 0 )

            INTO    v_max

            FROM   str_item_stores               

            WHERE  store_id    = p_store_id  

            AND    item_id     = p_item_id  ;  

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

     For testing  purpose, lt's  call the stored database function from Oracle  form using 
e.g. WHEN-BUTTON-PRESSED 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 easily get the maximum updated quantity item in a specific store.

Learn more about:

•        PDE-PLI018 Could not find library NAME
•     Fingerprint Time Attendance Clock Tips 
•         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.