PLS 00302 component must be declared-CodePudding

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)

CodePudding user response：

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

CodePudding user response：

You seem to be confused about explicit record types and implicit cursor for-loop record types. The my_type and my_variable you are declaring are never used. The my_variable in the cursor loop is a completely independent construct, that you just happen to have given the same name to. From the documentation that is the:

Name for the loop index that the cursor FOR LOOP statement implicitly declares as a %ROWTYPE record variable of the type that cursor or select_statement returns.

record is local to the cursor FOR LOOP statement. Statements inside the loop can reference record and its fields. They can reference virtual columns only by aliases. Statements outside the loop cannot reference record. After the cursor FOR LOOP statement runs, record is undefined.

So in a construct like:

for my_variable in (select sum(table_amount), table_date from table group by table_date)

the my_variable has the row type of the cursor query projection, and fields corresponding to the column expressions or their aliases.

You need to alias the sum(), and then refer to that alias, something like:

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

db<>fiddle with some names changed to be more consistent with what you are describing. Note that the record type and variable declaration are not needed.