Home » SQL & PL/SQL » SQL & PL/SQL » Collections Error
Collections Error [message #185895] Fri, 04 August 2006 02:50 Go to next message
yashora
Messages: 39
Registered: August 2006
Member
Hi all,

Whats the problem with this?. I am layman to collections.

DECLARE
TYPE T1 IS TABLE OF EMP%ROWTYPE;
V_EMPREC T1;
CURSOR C1 IS SELECT * FROM EMP WHERE DEPTNO=20;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO V_EMPREC;
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
FOR I IN 1..V_EMPREC.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(LPAD(V_EMPREC(I).EMPNO,10,' ')
||'-------'||LPAD(V_EMPREC(I).ENAME,10,'-')||'-------'||LPAD(V_EMPREC(I).DEPTNO,10,'-'));
END LOOP;
END;
/

ERROR at line 8:
ORA-06550: line 8, column 16:
PLS-00597: expression 'V_EMPREC' in the INTO list is of wrong type
ORA-06550: line 8, column 2:
PL/SQL: SQL Statement ignored

Regards
Yashora
Re: Collections Error [message #185896 is a reply to message #185895] Fri, 04 August 2006 02:55 Go to previous messageGo to next message
amit_kiran
Messages: 50
Registered: July 2006
Location: UK
Member

Try with this logic

DECLARE
TYPE T1 IS TABLE OF EMP%ROWTYPE;
V_EMPREC T1;
CURSOR C1 IS SELECT * FROM EMP WHERE DEPTNO=20;
BEGIN
OPEN C1;
FETCH C1 BULK COLLECT INTO V_EMPREC;
CLOSE C1;
FOR I IN 1..V_EMPREC.COUNT LOOP
DBMS_OUTPUT.PUT_LINE(LPAD(V_EMPREC(I).EMPNO,10,' ')
||'-------'||LPAD(V_EMPREC(I).ENAME,10,'-')||'-------'||LPAD(V_EMPREC(I).DEPTNO,10,'-'));
END LOOP;
END;
Re: Collections Error [message #185899 is a reply to message #185896] Fri, 04 August 2006 03:13 Go to previous message
yashora
Messages: 39
Registered: August 2006
Member
Thanks and it worked fine.
Previous Topic: SELECT qry doubt ...
Next Topic: multiple out paramaters
Goto Forum:
  


Current Time: Thu Dec 08 06:13:33 CST 2016

Total time taken to generate the page: 0.26068 seconds