CODEWITHSUNDEEP
arraysoracle-databaseplsqlrecord
gcaglion
gcaglion

Reputation: 131

PLS-00302. Unable to resolve

I've been reading the vast majority of topics on this subject in this and other forums, but I still can't find where my problem is. Here's what I have:

drop type RecordCount_tab;
drop type RecordCount_obj;

create type RecordCount_obj is object(Symbol varchar2(6), TimeFrame char(3), TotalCount number, minDate date, maxDate date);
/

show errors;
create type RecordCount_tab is table of RecordCount_obj;
/

show errors;

create or replace function RecordCount return RecordCount_tab is
l_RecordCount_tab RecordCount_tab := RecordCount_tab();
tName varchar2(64); vMinDate date; vMaxDate date; vCount number;
n integer :=0;
BEGIN
for r in (select table_name, substr(table_name,1,instr(table_name,'_')-1) as "BaseTable", substr(table_name,instr(table_name,'_')+1,length(table_name)-instr(table_name,'_')) as "TimeFrame" from user_tables) loop
    dbms_output.put_line('select min(NewDateTime), max(NewDateTime), count(*) from '||r.table_name);
    execute immediate 'select min(NewDateTime), max(NewDateTime), count(*) from '||r.table_name into vMinDate, vMaxDate, vCount;
    n:=n+1;
    l_RecordCount_tab(n) := (r.BaseTable, r.TimeFrame, vCount, vMinDate, vMaxDate);
end loop;
return l_RecordCount_tab;
END;
/

show errors;

I keep getting

PLS-00302: component 'BASETABLE' must be declared

and I don't understand why that is, as the query runs perfectly fine from sqlplus. I also tried to remove the alias, to no avail.

What am I missing?

Upvotes: 0

Views: 176

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 157098

You should remove the double quotes around the field alias in the for:

( select table_name
  ,      substr(table_name,1,instr(table_name,'_')-1) BaseTable
  ,      substr(table_name,instr(table_name,'_')+1,length(table_name)-instr(table_name,'_')) TimeFrame from user_tables
)

As a simplied example. This works:

begin
  for r in (select 'x' d from dual) -- NO quotes
  loop
    dbms_output.put_line(r.d); -- NO quotes
  end loop;
end;

While this doesn't:

begin
  for r in (select 'x' "d" from dual) -- double quotes
  loop
    dbms_output.put_line(r.d); -- NO quotes
  end loop;
end;

But this does:

begin
  for r in (select 'x' "d" from dual) -- double quotes
  loop
    dbms_output.put_line(r."d"); -- double quotes
  end loop;
end;

Upvotes: 2

Related Questions