0

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?

  • 1
    Firstly, the structure of the collection where you bulk collect (is table of table1%rowrype) is different from the query (select field1) -- unless your table has only one column. Secondly, EXECUTE IMMEDIATE will only work with a query that returns a single row -- is this the case? – mustaccio Jan 8 '16 at 18:59
  • @mustaccio, actually i assumed that i can use the structure of table (its more than one column) that is used in the statement and that should be ok. And regarding result of execute immediate lets consider it as single row. But i would appreciate your answer if you could share technique(s) to handle case when there is more than one row. thanks – Dozent Jan 8 '16 at 19:30
  • 1
    @mustaccio : EXECUTE IMMEDIATE works perfectly regardless of how many rows query returns. – a1ex07 Jan 8 '16 at 23:40
1

As mustaccio pointed out, types must be the same. You either have to change TTabList definition to be collection of just one field (see example below), or select : vstrQuery := 'select * ... Everything else should work after fixing misprint with rowrype (should be rowtype) . For instance, if you prefer to select just field1, you can do

set serveroutput on;
declare
  type TAB1_FIELD1 IS RECORD (field1 table1.field1%type);
  type TTabList  is table of TAB1_FIELD1 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 ||')';

  --works fine with collections (at least starting from 10g) 
  execute immediate vstrQuery bulk collect into vtabList; 

  ....
  • Thank you for your answer. It helped me to get an idea of types. – Dozent Jan 11 '16 at 8:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.