udarH3 udarH3 - 6 months ago 12
SQL Question

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;

Answer

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.