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;

This is what I do in the main procedure to call it.

CREATE OR REPLACE PROCEDURE P_DO_PROSES (
   in_internal_key  IN     	VARCHAR2,
   in_kode_toko     IN     	VARCHAR2,
   in_id_pel    	IN     	VARCHAR2,
   in_amount        IN     	NUMBER,
   out_msg    	    OUT 	VARCHAR2
)
IS
   this_amount           NUMBER;
   this_xamount          NUMBER;
   this_tipe_amount      VARCHAR2 (2 BYTE);
   this_table_bantuan    VARCHAR2 (20 BYTE);
   this_proc_bantuan     VARCHAR2 (20 BYTE);
   this_v_sql            VARCHAR2 (300 BYTE);
BEGIN
   out_msg := 'Database sukses';
   this_amount := 0;
   this_xamount := 0;
   
	-- get data --
   SELECT   TIPE_AMOUNT, TABEL_BANTUAN, PROCEDURE_BANTUAN
     INTO   this_tipe_amount, this_table_bantuan, this_proc_bantuan
     FROM   REF_FITUR
    WHERE   KODE_FITUR = in_kode_toko;

   IF     this_tipe_amount = '1'
      AND this_table_bantuan IS NOT NULL
      AND this_proc_bantuan IS NOT NULL
   THEN
      -- create String that call procedure --
      this_v_sql :=
            'DECLARE this_xamount number; BEGIN '
         || this_proc_bantuan
         || '(:'
         || in_kode_toko
         || ',:'
         || in_id_pel
         || ', :this_xamount); END;';

	  -- execute procedure with the in and out parameters --
      EXECUTE IMMEDIATE this_v_sql
         USING IN in_kode_toko, IN in_id_pel, OUT this_xamount;

      this_amount := in_amount + this_xamount;
	  
   END IF;
EXCEPTION
   
END;

Hope this simple article can help 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s