Execute immediate call causes 'variable not in select list' error

0 votes

1 answer

2397 views

                          Execute immediate call causes 'variable not in select list' error


hi there, i'm practicing with collections and execute immediate by combining them in plsql and have the following code:

    set serveroutput on;
    declare
      type TTabList  is table of table1%rowtype index by pls_integer;
      vtabList       TTabList;
      vstrQuery      varchar2(32000);
      vNum           number(10)     := 100;
      vStrs          varchar2(100)  := '''45'',''11'',''20'',''71''';
    begin
      dbms_output.enable;
      vstrQuery := 'select field1
                      from table1
                     where field1 =   '|| vNum ||'
                       and field2 in ('|| vStrs ||')';

      execute immediate vstrQuery bulk collect into vtabList;  
      
      if vtabList.count = 0 then
        dbms_output.put_line('nothing to show!');
      else
        while vtabList.count > 0 loop
          for i in vtabList.first .. vtabList.last loop
            dbms_output.put_line('we have: '|| vtabList(i).field1);
          end loop;
          vtabList.delete;
        end loop;
      end if;
    end;

But its results following error which i can't resolve:

    Error report - 
    ORA-01007: variable not in select list
    ORA-06512: at line 14
    01007. 00000 -  "variable not in select list"
    *Cause:    
    *Action:

Any ideas to fix this?
                        

Asked by bofanda (167 rep)

Jan 8, 2016, 06:48 PM
Last activity: Jan 9, 2016, 12:04 AM

Execute immediate call causes 'variable not in select list' error

Related Questions