SQL, SQL Server, Tutorials, Oracle, PL/SQL, Interview Questions & Answers, Joins, Multiple Choice Questions, Quiz, Stored Procedures, Select, Insert, Update, Delete and other latest topics on SQL, SQL Server and Oracle.
Notice that the record used to hold a fetched cursor row is defined as an author table "%rowtype".
c1 author_cursor; Now c1 is a variable of a cursor type. open c1 for select * from authors; Now c1 has all the attributes of the actual cursor.
As with any cursor it is important to close the cursor as soon as you have completed processing.
The cursor variable is returning all columns in the book table, but the receiving variable is defined as a record of columns from the author table.
SQL declare 2 type auth_cursor is ref cursor; 3 c1 auth_cursor; 4 r_c1 author%rowtype; 5 begin 6 open c1 for select * from book; 7 fetch c1 into r_c1; 8 close c1; 9 end; 10 / declare * ERROR at line 1: ORA-06504: PL/SQL: Return types of Result Set variables or query do not match ORA-06512: at line 7 Notice that the error message is pointing to the DECLARE clause.