Dynamic SQL in a Dynamic World

Bind Variables in
Dynamic PL/SQL

uBind variables are NOT reusable in dynamic SQL – the number of variables is equal to the number of parameters.

uBind variables ARE reusable in dynamic PL/SQL – the number of UNIQUE variables is equal to the number of parameters.

udeclare

u a NUMBER:=2;

b NUMBER:=3;

v_plsql_tx VARCHAR2(2000);

ubegin

v_plsql_tx:='begin :v01:=:v01+:v02; end;';

execute immediate v_plsql_tx

u using in out a, b;

u DBMS_OUTPUT.put_line('a='||a);

uend;