PLS 00302 component must be declared

June 8, 2022

Something happens that I dont understand.

here is a type declared:

type my_type is record (
    amount number(18,2),
    my_date date
)

my_variable my_type

…

for my_variable in (select sum(table_amount), table_date from table group by table_date)
loop
   dbms_output.put_line(my_variable.my_date);
   dbms_output.put_line(my_variable.amount)
end loop;

my_variable.my_date works good but my_variable.amount triggers a PLS00302 error that I dont understand.

table structure:
table_date date,
table_amount number(10,2)

>Solution :

The loop declares a local variable my_variable that shadows the previous declaration. You need to reference the column names and not the record’s attributes:

DECLARE
  type my_type is record (
    amount number(18,2),
    my_date date
  );

  my_variable my_type;
BEGIN
  for my_variable in (select table_amount, table_date from table_name)
  loop
    dbms_output.put_line(my_variable.table_date);
    dbms_output.put_line(my_variable.table_amount);
  end loop;
END;
/

Which is the same as:

DECLARE
  type my_type is record (
    amount number(18,2),
    my_date date
  );

  my_variable my_type;
BEGIN
  for not_my_variable in (select table_amount, table_date from table_name)
  loop
    dbms_output.put_line(not_my_variable.table_date);
    dbms_output.put_line(not_my_variable.table_amount);
  end loop;
END;
/

(But is slightly clearer as you do not have the cursor loop variable shadowing the declared variable.)

db<>fiddle here