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 to returns  the maximum quantity  stored of  a  specific item. Please, follow the sample code and read the comments  for each line:

CREATE OR REPLACE FUNCTION  UDATE_QTY   

  ( p_store_id  NUMBER  ,   p_item_serial   NUMBER   ) 

    RETURN NUMBER IS

    v_max    NUMBER := 0;

  CURSOR CR_UPDATE  IS

           SELECT  NVL( MAX( balance_qty ) , 0  )

            FROM   wh_t_items

            WHERE  store_id    =   p_store_id  

            AND    item_serial = p_item_serial  ;       

BEGIN

    OPEN   CR_UPDATE  ;         -- Open the cursor

    FETCH   CR_UPDATE   INTO  v_max  ;         -- Fetch the cursor

    IF CR_UPDATE%NOTFOUND  THEN   NULL;

ELSE

   LOOP

        UPDATE  item_t_trans

        SET    balance_qty            =  v_max    

        WHERE  item_t_trans.store_id  =  p_store_id 

        AND    item_serial            =  p_item_serial;    

  END LOOP ;

-- Save the changes out of the loop but inside the cursor.

COMMIT; 

END IF;

   CLOSE CR_UPDATE  ; 

      RETURN v_max    ;

 EXCEPTION

WHEN OTHERS THEN

   RAISE_APPLICATION_ERROR(-20001,'An error was encountered - 

      '||SQLCODE||' -ERROR- '||SQLERRM);

END  UDATE_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, let's embed this function within the package.

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.