CODEWITHSUNDEEP
sqloracle-databaseplsqloracle11g
udarH3
udarH3

Reputation: 199

Loop index variable use is invalid in PL/SQL Collections

After I'm executing this I receive the following:

PL/SQL: ORA-00942: table or view does not exist
PLS-00364: loop index variable 'EMP_REC' use is invalid

It is said that:

In a block or subprogram, user-defined records are instantiated when you enter the block or Subprogram. They cease to exist when you exit the block Subprogram."

Now I think I fully understand what it is saying, I think... But along with this said, is it supposed my FOR section to work right?(why it isn't?), because as I can see, the whole things happen in the block not outside. So till the exit of the block I suppose that v_myrec to exist in the cache or buffer of the private memory allocated by the Oracle server by default because after all v_myrec is a "table" so to speak, therefore the DBMS package should be able to print my "emp_rec.v_sal..." and after the execution of my block completes successfully, THEN v_myrec ceases to exist. I'm little confused here, can anybody explain me this?

If I got it all wrong please correct me. v

DECLARE
 TYPE t_rec IS RECORD
  (v_sal           NUMBER(8) NOT NULL := 0,
   v_min_sal       NUMBER(8) DEFAULT 1000,
   v_hire_date     employees.hire_date%TYPE,
   v_rec1          employees%ROWTYPE);
 v_myrec t_rec;  
BEGIN
 v_myrec.v_sal := v_myrec.v_min_sal + 500;
 v_myrec.v_hire_date := SYSDATE;
  SELECT *
  INTO v_myrec.v_rec1
  FROM employees
  WHERE employee_id = 100;
 DBMS_OUTPUT.PUT_LINE(v_myrec.v_rec1.last_name||' '||v_myrec.v_sal||
                      ' '||v_myrec.v_rec1.salary);

 FOR emp_rec IN (SELECT *
                        FROM v_myrec)   
 LOOP
 DBMS_OUTPUT.PUT_LINE(emp_rec.v_sal,...,...);                
 END LOOP;
END;

Upvotes: 2

Views: 30649

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

They are separate errors; PLS-00364: loop index variable 'EMP_REC' use is invalid is a knock-on error from your cursor being invalid when it's declared, which gets PL/SQL: ORA-00942: table or view does not exist.

v_myrec is not a table. If it was a SQL (schema-level) collection type rather than a PL/SQL collection you could do:

FOR emp_rec IN (SELECT * FROM TABLE(v_myrec))

but it isn't, so you can't. You can't refer to a PL/SQL collection in plain SQL, even inside a PL/SQL block. And this isn't even a collection, it's just a single record type, so looping over it doesn't really make much sense anyway.

Upvotes: 5

Related Questions