I've got this example:
DECLARE TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE; TYPE NameList IS TABLE OF emp.ename%TYPE; TYPE SalList IS TABLE OF emp.sal%TYPE; emp_cv EmpCurTyp; names NameList; sals SalList; BEGIN OPEN emp_cv FOR SELECT ename, sal FROM emp; FETCH emp_cv BULK COLLECT INTO names, sals; END; /from the PL/SQL Manual (Oracle8i) however the PL/SQL compiler returned the following error:
ORA-06550: line 9, column 20: PLS-00382: expression is of wrong type ORA-06550: line 9, column 4: PL/SQL: SQL Statement ignored ORA-06550: line 10, column 4: PLS-00394: wrong number of values in the INTO list of a FETCH statement ORA-06550: line 10, column 4: PL/SQL: SQL Statement ignoredIf I use a weakly typed declaration of the cursor, no error occurs. Is this example wrong or is this declaration not compatible with Oracle8i ?
Your problem lies in the declaration of the REF CURSOR type. You've defined it as a cursor that returns EMP%ROWTYPE, which by definition includes every column in the EMP table. However, when you OPEN the cursor, you are only including two of the columns. In addition, when you FETCH the data, you are only fetching two of the columns, which incidentally explains the last four errors in your stack. The trick here is to declare the REF CURSOR type as a cursor which returns just those columns you need. You do this by declaring a RECORD type for those two columns, and declaring the REF CURSOR as returning that RECORD type. Simply substitute this type declaration:
TYPE EmpCurTyp IS REF CURSOR RETURN emp%ROWTYPE;...with these two:
TYPE NameSalRec IS RECORD ( EName emp.ename%TYPE, Sal emp.sal%TYPE ); TYPE EmpCurTyp IS REF CURSOR RETURN NameSalRec;
For More Information
- What do you think about this answer? E-mail the edtiors at [email protected] with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.