ORA-01008: not all variables bound - "not all variables bound"

Question

This is my plsql block, dynamic SQL. I can't find a reason why it comes up with an error 'no ORA-01008: not all variables bound ORA-06512: at line 23'.

I can't find the error on my EXECUTE IMMEDIATE statement.

 DECLARE
      form_name      VARCHAR2(225) := 'MUST AS';
      ad_no          VARCHAR2(225) := :ad_no;
      sql_stmt       VARCHAR2(4000);
      sql_output     VARCHAR2(4000);
      db_table       VARCHAR(225) := inp_reminder_pkg.form_db_table(form_name);
      col_id         VARCHAR(225) := inp_reminder_pkg.get_col_id(form_name);
    BEGIN
      sql_stmt := '
            SELECT :1
              FROM @tableName
             WHERE advno = :2
               AND created = ( SELECT MAX(CREATED)
                                 FROM @tableName
                                WHERE advno = :2 )'
      ;
      sql_stmt := replace(sql_stmt, '@tableName', db_table);
    
      EXECUTE IMMEDIATE sql_stmt
      INTO sql_output
        USING  col_id, ad_no;

      dbms_output.put_line(sql_output);

    EXCEPTION
      WHEN no_data_found THEN

        dbms_output.put_line('no-data');
    END;

Let me know what I am missing. thanks you

Yes, without using any bind variable or hardcoding the variable I was able to get an output. — ebbie, Oct 26 '21 at 13:03
What is `@table`? Unless you `set define @` in SQL\*Plus, the `@` character is part of database link syntax and not valid here. — William Robertson, Oct 26 '21 at 18:48

score 1 · Answer 1 · answered Oct 26 '21 at 12:58

1

There are three bind variables (even though two have the same name), you need to send 3 arguments for them in the execute immediate statement.

Note that you probably didn’t mean for the column name to be input as a bind variable, this is something that has to be dynamically executed if you want to have a variable column being selected.

answered Oct 26 '21 at 12:58

Andrew Sayer

2,296
1
7
9

When you say dynamically executed, what do you mean by that? col_id is a column name of a selected table since some of the tables we have don't have the same column id name. – ebbie Oct 26 '21 at 13:09
I have tried adding 3 variables after USING, but I only need two variables to be used. would it be redundant to use the 2nd as the same? – ebbie Oct 26 '21 at 13:13
By dynamically, I mean it needs to be replaced into the string the same way you replace the table name in. You need the same number of arguments to the execute immediate as you have bind variables. Just because they have the same name in the SQL text doesn’t mean you can skip the arguments - you’re passing the inputs in order of reference rather than by name. – Andrew Sayer Oct 26 '21 at 13:46

score 0 · Answer 2 · answered Oct 26 '21 at 14:04

There are two problems with your code:

The dynamic query has two instances of the placeholder :advno so you need to pass two values in the USING clause. Yes this is a bit rubbish, but I guess Oracle felt parsing dynamic SQL is hard enough without asking the compiler to match placeholder names.
We can't pass column names or other identifiers as parameters. We have to break up the statement to reference the column name variable. For the same reason you have the replace() call to substitute the table name.

So you need to change your procedure so it looks like this:

DECLARE
      form_name      VARCHAR2(225) := 'MUST AS';
      ad_no          VARCHAR2(225) := :ad_no;
      sql_stmt       VARCHAR2(4000);
      sql_output     VARCHAR2(4000);
      db_table       VARCHAR(225) := inp_reminder_pkg.form_db_table(form_name);
      col_id         VARCHAR(225) := inp_reminder_pkg.get_col_id(form_name);
    BEGIN
      sql_stmt := '
            SELECT ' || col_id || '
              FROM @tableName
             WHERE advno = :2
               AND created = ( SELECT MAX(CREATED)
                                 FROM @tableName
                                WHERE advno = :2 )'
      ;
      sql_stmt := replace(sql_stmt, '@tableName', db_table);
    
      EXECUTE IMMEDIATE sql_stmt
      INTO sql_output
        USING  ad_no, ad_no;

      dbms_output.put_line(sql_output);

    EXCEPTION
      WHEN no_data_found THEN

        dbms_output.put_line('no-data');
    END;

ORA-01008: not all variables bound - "not all variables bound"

2 Answers2