JL Computer Consultancy

Bind Variables in PL/SQL

July 1999


Bind Variables seem to cause a little confusion in PL/SQL, so this note is a simple example of using bind variables to create a dynamic call to a procedure that takes an IN OUT parameter. This effectively allows the variables of one procedure to be passed as the variables in a dynamic call to a second procedure.

The first step is to create a procedure with an IN OUT parameter to use in our example.


        create or replace procedure increment_var(i_test in out number) is
        begin
               i_test := i_test + 1;
        end;
        /

In normal circumstances,, if we want to call this procedure from another procedure, passing in a variable from the first procedure, the job is trivial, and we might write something like this:

        create or replace procedure exercise_it as 
               v_var   number  := 22;
               .....
        begin
               .....
               increment_var(v_var);
               .....
        end;
        /

However we may want to do something more complex, which (ignoring errors) looks more like this:

        create or replace procedure exercise_it as 
               v_var   number  := 22;
               .....
        begin
               .....
               dbms_sql.parse(v_cursor,'increment_var(v_var)',dbms_sql.v7);
               v_count := dbms_sql.execute(v_cursor);
               .....
        end;
        /

The problem is that we have a variable and we want to build a dynamic piece of PL/SQL that uses that variable as both an input and an output. We cannot simply concatenate the variable (hence its value) into the PL/SQL string, or we would get a PL/SQL error relating to the called procedure to the effect that we were trying to use a constant as OUT variable, and we cannot introduce the name of the variable to the string as it would be meaningless to the called procedure.

This is where bind variables come in. You insert a meaningless piece of text as a place-holder in the string to be parsed, and then call the bind_variable() procedure to associate that place-holder with a local variable.

Once this association has been made, you can execute the cursor and use the variable_value() procedure to transfer the resulting value from the cursor space into the local variable.

Note: you need only bind the place-holder to the variable once, after which you can execute the cursor and copy the value out many times. The example below executes the cursor twice, and you may get a better gut-feeling for the mechanisms involved by (a) eliminating calls to variable_value, (b) introducing a second bind_variable() to a different variable before the second execute.


set serveroutput on size 10000
declare
        src_cur        pls_integer;
        src_rows       pls_integer;
        var_value      number(3);
begin
        var_value := 99;
        dbms_output.put_line('Starting at: ' || var_value);
        src_cur := dbms_sql.open_cursor;
 
        dbms_sql.parse(
                       src_cur, 
                       'begin increment_var(:m1); end;',
                       dbms_sql.v7
        );
 
        --      Associate the :m1 above with the variable var_value.
        --      The 'm1' below has to be in quotes.  A preceding ':' is optional
 
        dbms_sql.bind_variable(src_cur,'m1',var_value);
 
        src_rows := dbms_sql.execute(src_cur);
        dbms_sql.variable_value(src_cur,'m1',var_value);
        dbms_output.put_line('Ending at: ' || var_value);
 
        --      The same variable is still associated with the place holder,
        --      so its most recent value will appear in the next call.
 
        src_rows := dbms_sql.execute(src_cur);
        dbms_sql.variable_value(src_cur,'m1',var_value);
        dbms_output.put_line('Ending at: ' || var_value);
 
        dbms_sql.close_cursor(src_cur);
end;
.
/

Back to Main Index of Topics