matrix-rain

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


FUNCTION TEST(I_PARAM IN NUMBER,
              O_PARAM IN OUT NUMBER)
  return BOOLEAN IS

BEGIN

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

return TRUE;

END TEST;

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

DECLARE

     -- 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);

BEGIN
   -- A random in parameter
   in_param := 2;

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

   -- 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);

end;

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 🙂

References

[1] Oracle Database PL/SQL User’s Guide and Reference  ; Performing SQL Operations with Native Dynamic SQL ; http://www.dba-oracle.com/t_with_clause.htm

[2] Oracle Database Application Developer’s Guide – Fundamentals ;  Coding Dynamic SQL ; http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_dynamic_sql.htm