Q
Problem solve Get help with specific problems with your technologies, process and projects.

Problems with cursor example in Oracle 8i

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 ignored
 
If 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 editor@searchDatabase.com 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.

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close