"ORA-01007: variable not in select list" when no rows are returned by EXECUTE IMMEDIATE

Question

I have a procedure which receives as parameter a where clause (i.e. where col1 = 1). I am using this clause to search in some tables using an EXECUTE IMMEDIATE statement and the result to be inserted into a nested table, and than be displayed.

The procedure works fine if any data is found but in case no data is found, then the above error is thrown.

Can someone explain what cause this error, please?

Here is the procedure:

create or replace procedure prc_checks(pi_where varchar2) as

    cursor c_tables is
        select object_name, 
        case object_name
            when 'XP_IMPORT_MW' THEN 99999999
            when 'XP_IMPORT_MW_ARCH' THEN 99999998
            else TO_NUMBER(SUBSTR(object_name, -8, 8))
        end to_order
        from dba_objects 
        where object_type = 'TABLE' 
        and object_name IN ('XP_IMPORT_MW', 'XP_IMPORT_MW_ARCH') 
        or REGEXP_LIKE (object_name, 'XP_IMPORT_MW_ARCH_201(5|6|7)[0-9]{4}') order by 2 desc;

    type t_result is table of xp_import_mw%rowtype;
    v_result t_result;

    v_sql varchar2(300);

BEGIN  
    for i in c_tables
        loop
            v_sql := 'select * from ' || i.object_name || ' ' || pi_where;
            execute immediate v_sql bulk collect into v_result;

            if v_result.count > 0 
                then 
                    for j in v_result.first .. v_result.last
                    loop
                        dbms_output.put_line(v_result(j).art_nr);
                    end loop;
                    dbms_output.put_line('... the required information was found on table name ' || upper(i.object_name));
                    exit;
            end if;
        end loop;
END prc_checks;

Answer 1

You'll get this is one of the tables being found by the cursor has fewer columns than xp_import_mw. For example:

create table xp_import_mw (col1 number, art_nr number, dummy number);
create table xp_import_mw_arch_20160102 (col1 number, art_nr number, dummy number);
create table xp_import_mw_arch_20160101 (col1 number, art_nr number);
insert into xp_import_mw_arch_20160101 values (1, 42); 

So the main xp_import_mw table has three columns but no matching data. One of the old archive tables has one fewer columns.

I added a dbms_output.put_line(v_sql) to the procedure to see which table it fails against, then ran it:

set serveroutput on
exec prc_checks('where col1 = 1');

which got output:

select * from XP_IMPORT_MW where col1 = 1
select * from XP_IMPORT_MW_ARCH_20160102 where col1 = 1
select * from XP_IMPORT_MW_ARCH_20160101 where col1 = 1

Error starting at line : 49 in command -
BEGIN prc_checks('where col1 = 1'); END;
Error report -
ORA-01007: variable not in select list
ORA-06512: at "MY_SCHEMA.PRC_CHECKS", line 25
ORA-06512: at line 1
01007. 00000 -  "variable not in select list"
*Cause:    
*Action:

So the problem isn't that there is no data found; the problem is that there is matching data in a table which has the wrong structure.

You could construct the select list based on the xp_import_mw table's structure, instead of using *; that won't stop it failing, but would at least give you a slightly more helpful error message - in this case ORA-00904: "DUMMY": invalid identifier instead of ORA-01007.

You could do a quick and crude check for discrepancies with something like:

select table_name, count(column_id) as column_count,
  listagg(column_name, ',') within group (order by column_id) as columns
from dba_tab_columns
where table_name IN ('XP_IMPORT_MW', 'XP_IMPORT_MW_ARCH') 
or REGEXP_LIKE (table_name, 'XP_IMPORT_MW_ARCH_201(5|6|7)[0-9]{4}')
group by table_name
having count(column_id) != (
  select count(column_id) from dba_tab_columns where table_name = 'XP_IMPORT_MW'
);

... although if you're using dba_* or all_* view you should really be including the owner, here and in your procedure.