Using dynamic PLSQL to call functions

Hi all !

This post came to my mind while I was working with dynamic PLSQL. It is quite straightforward to call a procedure using dynamic SQL. But, what about calling a function that has IN/OUT parameters and returns boolean TRUE/FALSE ?! Let’s find out in this small how-to in two easy steps.

1st step – Write a test function

              O_PARAM IN OUT NUMBER)
  return BOOLEAN IS


    dbms_output.put_line('I_PARAM ' || I_PARAM);
    o_param := 3;

return TRUE;


As you noticed, our test function also has a parameter that is simultaneaous “IN/OUT”. Our test function doesn’t do anything really interesting. It will only return TRUE, and set our O_PARAM variable to 3.
So what if you wanted to call this function using dynamic PLSQL ?

2nd Step – Write an anonymous block that calls our TEST function


     -- Some test variables
     in_param integer;
     out_param1 integer;
     out_param_last integer;
     out_ boolean;

     -- A variable to hold our dynamic sql
     sql_statm VARCHAR2(255);

   -- A random in parameter
   in_param := 2;

   -- Our dynamic PLSQL statement
   L_sql_statm := 'DECLARE
                          i_result boolean;
                          i_result := TEST(:1, :2);
                         :3 := sys.diutil.bool_to_int(i_result);

   -- executes our statement using the defined variables
   EXECUTE IMMEDIATE L_sql_statm USING IN in_param,
                                       IN OUT out_param1,
                                       OUT out_param_last;

   dbms_output.put_line('Our TEST function returned :' || out_param_last ||
                        ' as well as : ' || out_param1);


Now let me explain the code above.

A PLSQL function must always return a value, therefore the trick to invoke it from dynamic SQL, is to use an anonymous block and to assign the function output to a temporary variable. Our “TEST” function returned boolean, but I prefer the 0/1 output format to see if a function returned sucessfully or not. That is the reason behind the conversion “sys.diutil.bool_to_int” .
Last but not least, make sure you are using the parameters in the correct order when calling the “EXECUTE IMMEDIATE” .

Final thoughts

And that’s it ! Hope this post can save you some browsing 🙂


[1] Oracle Database PL/SQL User’s Guide and Reference  ; Performing SQL Operations with Native Dynamic SQL ;

[2] Oracle Database Application Developer’s Guide – Fundamentals ;  Coding Dynamic SQL ;