CODEWITHSUNDEEP
plsqlexecute-immediate
rinodrummer
rinodrummer

Reputation: 33

Parsing placeholders in PL/SQL statement for EXECUTE IMMEDIATE

I'm studying this language from just some days. I was trying to use a string containing a PL/SQL block with a placeholdered string which have two fields that I want to replace with some data retrieved from a SELECT statement.

I've correctly created and populated the table employees.

The problem is that I need to "replace" those placeholders (:name and :salary in variable cmd2) but when I EXECUTE IMMEDIATE using the values retrieved I get this error: ORA-01006: bind variable does not exist.

This is the code snippet:

DECLARE
    cmd1 VARCHAR2(200) := 'SELECT * FROM employees';
    cmd2 VARCHAR2(200) := 'BEGIN DBMS_OUTPUT.PUT_LINE('':name has a salary of :salary;''); END;';
    str VARCHAR2(200);

    c1 SYS_REFCURSOR;

    emp employees%ROWTYPE;
BEGIN
    OPEN c1 FOR cmd1;
    LOOP
        FETCH c1 INTO emp;
        EXIT WHEN c1%NOTFOUND;

        -- It doesn't work
        EXECUTE IMMEDIATE cmd2 USING emp.name, emp.salary;

        -- It works, but just prints ':name has a salary of :salary;'
        EXECUTE IMMEDIATE cmd2;
    END LOOP;
END;

The expected result should be:

Name1 has a salary of 300;
Name2 has a salary of 700;
-- ...and so on

Upvotes: 0

Views: 2247

Answers (3)

Tony Andrews
Tony Andrews

Reputation: 132650

The problem is with your PL/SQL to define cmd2:

cmd2 VARCHAR2(200) :=
   'BEGIN DBMS_OUTPUT.PUT_LINE('':name has a salary of :salary;''); END;';

You cannot reference variable names inside a string value - they are just text there. This change will make it work;

cmd2 VARCHAR2(200) := 
   'BEGIN DBMS_OUTPUT.PUT_LINE(:name||'' has a salary of ''||:salary); END;';

Now the first execution will succeed but the second will fail with:

ORA-01008: not all variables bound

So remove the second execution and all will be well!

Note

Your example is not a typical use case for dynamic PL/SQL, since the same can be achieved with static PL/SQL:

BEGIN
    FOR r IN SELECT * FROM employees
    LOOP
      DBMS_OUTPUT.PUT_LINE(r.name || ' has a salary of ' || r.salary');
    END LOOP;
END;

Dynamic SQL and PL/SQL should only really be used when static SQL is not possible - e.g. because the table name, column names or procedure names are not fixed. See some of the examples here in the Oracle docs.

Upvotes: 2

XING
XING

Reputation: 9886

Bind variables are best used in SQL statements inside a PLSQL block. You shouldnot bind variables in DBMS_OUTPUT statements.

In your case

cmd2 VARCHAR2(200) := 'BEGIN DBMS_OUTPUT.PUT_LINE('':name has a salary of :salary;''); END;';

Here application of BIND variables is not correct. This is not allowed.

See below a simple example of using bind variable.

SQL> DECLARE
  2      cmd1 VARCHAR2(200) := 'SELECT * FROM EMP';
  3      cmd2 VARCHAR2(200) := 'SELECT * FROM EMP WHERE ENAME = :name  and sal =:salary'; --<--See how bind variables are used
  4      str VARCHAR2(200);
  5  
  6      c1 SYS_REFCURSOR;
  7  
  8      emp1 emp%ROWTYPE;
  9  BEGIN
 10      OPEN c1 FOR cmd1;
 11      LOOP
 12          FETCH c1 INTO emp1;
 13          EXIT WHEN c1%NOTFOUND;
 14  
 15          -- It doesn't work
 16          EXECUTE IMMEDIATE cmd2 USING emp1.ename, emp1.sal;
 17  
 18          
 19      END LOOP;
 20  END;
 21  /

PL/SQL procedure successfully completed.

SQL>

Upvotes: -1

ishando
ishando

Reputation: 316

The bind variables are in a string, so they are not being seen as binds.

try

cmd2 VARCHAR2(200) := q'[BEGIN DBMS_OUTPUT.PUT_LINE(:name || ' has a salary of ' || :salary); END;]';

Upvotes: 3

Related Questions