Binding Variables with EXECUTE IMMEDIATE of PL/SQL Block

View All Scripts Login to Run Script

Script Name Binding Variables with EXECUTE IMMEDIATE of PL/SQL Block
Description When you execute a dynamic PL/SQL block dynamically, variables are bound to placeholders BY NAME, not by position (which is the case with dynamic SQL).
Area PL/SQL General
Contributor Steven Feuerstein
Created Thursday January 14, 2016

Statement 1

CREATE TABLE employees AS SELECT * FROM hr.employees

Table created.

Statement 2

My dyn_binds procedure first executes a generic UPDATE against whatever column you specified. Generally NOT a good idea and as always you must be careful with SQL injection when concatenating (column name in this case). But hey it's just a demo! Then I execute a second dynamic SQL statement to get the total of that column.

Lots of Dynamic SQL!

CREATE OR REPLACE PROCEDURE dyn_binds (  
   col_in      IN     VARCHAR2,  
   value_in    IN     NUMBER,  
   total_out      OUT NUMBER,  
   id_in       IN     NUMBER)  
IS  
BEGIN  
   EXECUTE IMMEDIATE  
         'UPDATE employees SET '  
      || col_in  
      || ' = :val WHERE employee_id = :val'  
      USING value_in, id_in;  
  
   EXECUTE IMMEDIATE  
      'SELECT SUM (' || col_in || ') FROM employees'  
      INTO total_out;  
END;

Procedure created.

Statement 3
This step fails with "ORA-01006: bind variable does not exist". The problem is that my dynamic block string uses the same placeholder twice ":numval". With a dynamic DML statement (insert, update, etc.), variables are assigned to placeholders by position. Four placeholders? Then four variables in USING list, regardless of the names of placeholders. With dynamic PL/SQL, it's different. Binding is by name. So once I've provided 10000 for the first :numval, it will be used for the second as well.
Incorrect Execution of Dynamic Block
```
DECLARE  
   l_total_salary   NUMBER;  
BEGIN  
   EXECUTE IMMEDIATE  
      'begin dyn_binds (:colname, :numval, :total_salary, :numval); end;'  
      USING IN 'salary',  
            IN 10000,  
            OUT l_total_salary,  
            IN 100;  
  
   DBMS_OUTPUT.put_line (l_total_salary);  
   ROLLBACK;  
END; 
```
```
ORA-01006: bind variable does not exist ORA-06512: at line 4
ORA-06512: at "SYS.DBMS_SQL", line 1721
```
More Details: https://docs.oracle.com/error-help/db/ora-01006

Statement 4

I removed the fourth element in the USING list and now the code runs to completion without error. Whether it makes any sense at all, well that's application specific. And most of our application code? Well, it could make MORE sense than it does, anyway! :-)

Correct Execution of Dynamic Block

DECLARE  
   l_total_salary   NUMBER;  
BEGIN  
   EXECUTE IMMEDIATE  
      'begin dyn_binds (:colname, :numval, :total_salary, :numval); end;'  
      USING IN 'salary',  
            IN 10000,  
            OUT l_total_salary;  
  
   DBMS_OUTPUT.put_line (l_total_salary);  
   ROLLBACK;  
END;

Statement 5
OK, you say: so I will use the same name placeholder for the last three parameters of my function. So I just specify the value once, right? Wrong! My first parameter is IN, the second OUT - but I provide a literal, so I get the "ORA-06536: IN bind variable bound to an OUT position" error. And if I provide a variable instead of a literal, I can run into parameter aliasing issues. Don't write such confusing code!
And This is Just a Mess
```
DECLARE  
   l_total_salary   NUMBER;  
BEGIN  
   EXECUTE IMMEDIATE  
      'begin dyn_binds (:colname, :numval, :numval, :numval); end;'  
      USING IN 'salary',  
            IN 10000;  
  
   DBMS_OUTPUT.put_line (l_total_salary);  
   ROLLBACK;  
END; 
```
```
ORA-06536: IN bind variable bound to an OUT position ORA-06512: at line 4
ORA-06512: at "SYS.DBMS_SQL", line 1721
```
More Details: https://docs.oracle.com/error-help/db/ora-06536

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database