Parsing placeholders in PL/SQL statement for EXECUTE IMMEDIATE

Question

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

Even though you accepted the answer which is ideally not apt for your question i would again like to say , bind variable application is meant just not for `DBMS_OUTPUT` statements. — XING, Jan 12 '18 at 12:21
The importance of the question is reduced if you accept any answer which somehow resolves your problem but can be misleading as conceptual point. It hardly matters if you added any quotes as such people look at the answer not question — XING, Jan 12 '18 at 12:39
@XING someone may found useful ishando's answer more than yours. I found useful your answer so cited and up-voted it but I think that his answer resolves my problem, even if it's a wrong way to use the code. — RinoDrummer, Jan 12 '18 at 12:48
"Bind variables are used only in SQL statements inside a PLSQL block." is wrong though, they are also used in procedure calls to pass arguments - see many examples [here in Oracle docs](https://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS629). And `DBMS_OUTPUT.PUT_LINE` is a procedure call like any other! — Tony Andrews, Jan 12 '18 at 13:13
It may be unusual to use bind variables in a concatenated string within a dynamic procedure call, but it's perfectly valid (if you must), so I think the warning is incorrect. — William Robertson, Jan 12 '18 at 15:48

ishando · Accepted Answer · 2018-01-12T12:09:18.097

3

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;]';

edited Jan 12 '18 at 12:09

answered Jan 12 '18 at 11:25

ishando

306
3
7

Did you try it before posting it. Was it working in your case. This will throw error, – XING Jan 12 '18 at 11:31
I did try it, but had typo when typing it here - should be q'[ – ishando Jan 12 '18 at 12:07

Tony Andrews · Answer 2 · 2018-01-12T18:04:00.497

2

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.

edited Jan 12 '18 at 18:04

answered Jan 12 '18 at 11:23

Tony Andrews

129,880
21
220
259

Unfortunately, I had to downvote as after reading your comment , i could make that even you didnot explained very well why issue `ORA-01008: not all variables bound` would be raised. Irrespective of the fact that actually it didnot raised that error for OP. – XING Jan 12 '18 at 12:35
Tit for tat! :-) – Tony Andrews Jan 12 '18 at 12:39
No..i mean..mine was informative. Your answer is absoultely wrong when you say OP if he receive error `ORA-01008: not all variables bound`. On the other hand your downvote was not effectively justifying the reason. Just going by words you chose to downvote which looks atleast no sense to me. – XING Jan 12 '18 at 12:41
What do you mean? He will certainly get ORA-01008 if he runs the code **after** making my change - I know because I ran it myself. This line will cause it: `EXECUTE IMMEDIATE cmd2;`. That's why I said "**Now** ... the second will fail...". Retracting your downvote yet? – Tony Andrews Jan 12 '18 at 12:44
So that means your solution is anyways leading to an error rather than helping OP. I guess still your answer deserve a downvote. – XING Jan 12 '18 at 15:25
1

OK be a smart-arse if you like, it doesn't impress me much. The OP knows perfectly well that they want to make the first call that doesn't error, not the second one that does. But I'l update my answer so that you can remove your downvote! – Tony Andrews Jan 12 '18 at 15:50
1

While you and @xing were arguing about the correct use of bind variables somebody else nicked the Accepted tick with just about the scantiest answer possible. :) I am reminded of a nature documentary I once saw about stag beetles. – APC Jan 13 '18 at 16:13
@APC Yes it is rather amusing! I actually up-voted that answer myself as I consider it correct. XING of course voted it down, along with mine and even the question! – Tony Andrews Jan 14 '18 at 11:16

XING · Answer 3 · 2018-01-12T15:58:51.657

-1

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>

edited Jan 12 '18 at 15:58

answered Jan 12 '18 at 11:24

XING

9,608
4
22
38

1

The solution of @ishando `q'[BEGIN DBMS_OUTPUT.PUT_LINE(:name || ' has a salary of ' || :salary); END;]';` worked fine! – RinoDrummer Jan 12 '18 at 12:16
@RinoDrummer I know after his correction that should work but its not the correct application of bind variables. – XING Jan 12 '18 at 12:18
Thanks. I'll avoid to use it cases like this! – RinoDrummer Jan 12 '18 at 12:22
Downvoters please feel free to share your knowledge by giving reason for downvoting – XING Jan 12 '18 at 12:28
2

I was about to! I'm down-voting because your answer says "don't do this" rather than showing how it can be done (to match the OP's requirement). There is no rule that says you cannot or should not bind a variable into a DBMS_OUTPUT statement in a dynamic PL/SQL block as long as you get the syntax right! Whether you would really want to in real life is another matter... – Tony Andrews Jan 12 '18 at 12:31
@TonyAndrews You are bit late in downvoting. I never said `Don't Do`. I said it `should not` be used as such bind variables are not meant to be used for `DBMS_OUTPUT` statements. Just because something is available, you should run behind it and use it without understanding the proper usage. – XING Jan 12 '18 at 12:36
"Bind variables are used only in SQL statements inside a PLSQL block": not true, they can also be used in PL/SQL statements such as the OP's, More typically something like `begin myproc (p1=>:var1); end;` – Tony Andrews Jan 12 '18 at 12:47
1

*"Bind variables are used only in SQL statements inside a PLSQL block"* is not true. They may be *best* or *normally* used only in SQL statements, but there is no restriction. – William Robertson Jan 12 '18 at 15:53

Parsing placeholders in PL/SQL statement for EXECUTE IMMEDIATE

3 Answers3

Note