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

CodePudding user response：

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.

CodePudding user response：

There are two problems with your code:

1. 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.
2. 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;