Need explanation on this code involving TABLE type collection

Question

CREATE OR REPLACE PROCEDURE multirowupdate AS
  TYPE t_record IS TABLE OF employees%ROWTYPE;
  t_record record_t;
  BEGIN

    UPDATE employees
    SET salary = salary + 10
    RETURNING first_name, salary INTO record_t;

    FOR i IN 1..record_t.count
    LOOP
      dbms_output.put_line(record_t(i).first_name);
      dbms_output.put_line(record_t(i).salary);
    END LOOP;
  END;

Upon executing I am getting the error:

 Error(11,7): PLS-00201: identifier 'RECORD_T' must be declared

Why I am getting this error when I have clearly declared this is the declaration section.

I am using employees table in HR schema

score 2 · Answer 1 · answered Jan 19 '14 at 13:22

You seem to have got your declarations a bit mixed up.

You declare a type of t_record but then use that as the name of your variable?

I think what you probably wanted to do was:

create or replace procedure
MultiRowUpdate
as
type t_record is table of employees%rowtype;
-- This is not correct: t_record record_t;
record_t t_record;

begin

    update employees
    set salary=salary + 10
    returning first_name,salary
    into record_t;

for  I in 1..record_t.count 

    loop

      dbms_output.put_line(record_t(I).first_name);
      dbms_output.put_line(record_t(I).salary);

    end loop;

end;

Hope it helps

Thanks ! Now I see my mistake, I shouldn't have given similar name. Thanks anyway. — redsoxlost, Jan 19 '14 at 13:52

score 1 · Answer 2 · edited May 23 '17 at 12:07

The right declaration of variable should look as follows:

record_t t_record;

With variable preceding it's type. But this correction lead you to another error:

PLS-00642: local collection types not allowed in SQL statements

It means that you are not allowed to mix PL/SQL and SQL context in types declarations. This problem is more complex to solve. To avoid duplication I will not post the solution here. Instead please read the explanation in the answers to this question which seems pretty same with your needs.

Need explanation on this code involving TABLE type collection

2 Answers2