How To Call Procedure Dynamically From Another Procedure With IN and OUT Parameters

Sometimes when I create procedure, I separate it into some procedures based on the function. I’ll use and call it dynamically from the main procedure. Here is the way how I do it.

I have a procedure “p_get_fee” that has 2 in parameters and 1 out parameter.

CREATE OR REPLACE PROCEDURE P_GET_FEE (
   in_kode_toko	IN     	VARCHAR2,
   in_id_pel 	IN     	VARCHAR2,
   out_fee    	OUT 	NUMBER
)
IS
BEGIN
   out_fee := 0;
   SELECT   fee
     INTO   out_fee
     FROM   FEE_PELANGGAN
    WHERE   KODE_TOKO = in_kode_toko AND ID_PELANGGAN = in_id_pel;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
      -- Consider logging the error and then re-raise
      RAISE;
END P_GET_FEE;

Continue reading “How To Call Procedure Dynamically From Another Procedure With IN and OUT Parameters”

Advertisements

Summary About Oracle PL/SQL (Part 2)

  • A function must return value.
  • A procedure mainly used to an action. It allowed to not return value.
  • Basic syntax to create procedure
    CREATE [OR REPLACE] PROCEDURE procedurename
    [(parametername [in|out|in out] type, [...])]
    [IS|AS]
    BEGIN
       <procedure body>
    END;
    

    Note:
    [xxx] => optional
    OR REPLACE => used to modify existing procedure.
    IN => value from outside
    OUT => return value
    IN OUT => return value and the value from outside allocated on same parameter
    AS => used for standalone procedure

  • Basic syntax to create function
    CREATE [OR REPLACE] FUNCTION functionname
    [(parametername [in|out|in out] type, [...])]
    RETURN returndatatype
    [IS|AS]
    BEGIN
       <function body>
    END [functionname];
    
  • To execute standalone procedure Continue reading “Summary About Oracle PL/SQL (Part 2)”

Summary About Oracle PL/SQL (Part 1)

  • Basic syntax:
    DECLARE
    <declaration section>
    BEGIN
    <executable section>
    EXCEPTION
    <exception section>
    END;
    
  • To declare a variable : *name_of_variable* *datatype* := *defaultvalue*;
  • %type : used for declare variables with relation to the data type of a column in an existing table.
  • %rowtype : used in cursors to declare a single variable which contain a single record from resultset of a cursor or table without needing to specify individual variable.
  • Constant : in variable declaration its mean that the variable does not allowed to be changed.
  • Condition syntax:
    IF (thecondition) THEN
    <the process>
    END IF;
    

    with else condition Continue reading “Summary About Oracle PL/SQL (Part 1)”