A cursor is passed a parameter in very much the same way a procedure is passed a parameter except that the parameter can only be IN mode. Like the procedure, the cursor definition will declare an unconstrained datatype for the passed variable.

cursor c7 (v_key varchar2) isselect initcap(book_title) bk_title, sum(quantity) sales, author_keyfrom book join sales using (book_key)join book_author using (book_key) where author_key = v_keygroup by initcap(book_title), author_key;

The cursor above is defined as accepting a parameter with a datatype of varchar2. In this example, the variable is used in the WHERE clause to filter the results before grouping. By substituting cursor c7 for c6 in the above example, the PL/SQL block becomes much more efficient because the inner cursorreturns only the row belonging to the specified author.

SQL> declare2 cursor c5 is3 select initcap(author_last_name) a_name,4 author_key5 from author;6 cursor c7 (v_key varchar2) is7 select initcap(book_title) bk_title,8 sum(quantity) sales, author_key9 from10 book join sales using (book_key)11 join book_author using (book_key) 12 where author_key = v_key13 group by initcap(book_title), author_key;14 r_c5 c5%rowtype; 15 r_c7 c7%rowtype;16 begin17 open c5;18 loop 19 fetch c5 into r_c5;20 exit when c5%notfound;21 dbms_output.put_line(chr(10)||r_c5.a_name);22 begin 23 open c7(r_c5.author_key);24 loop25 fetch c7 into r_c7;26 exit when c7%notfound;27 dbms_output.put_line (r_c7.bk_title||28 ', '||r_c7.sales); 29 end loop;30 close c7;31 end;32 end loop; 33 close c5;34 end;35 /

The results returned in this example are the same as the previous example and to save space were omitted. This example using c7 is much more efficient than using c6 because it eliminated returning unneeded data in cursor c7 and also allows the removal of the IF/THEN clause with its condition test. Notice line 23 opened the cursor and passed the current author_key from the outer cursor. If line 23 did not pass in the parameter when the cursor was opened, PL/SQL would throw an exception.

open c7;*ERROR at line 23:ORA-06550: line 23, column 7:PLS-00306: wrong number or types of arguments in call to 'C7'ORA-06550: line 23, column 7: PL/SQL: SQL Statement ignored

Let's close with one final note about cursors. This example was used to demonstrate nested cursors and passing parameters to cursors. Even though the final version was far more efficient that the first version, it still violates an important rule of PL/SQL programming, do not execute SQL inside a loop unless there is no other way to get the data.

The example could have been written to execute one cursor that returned all the needed data: author_name, book_title, and sales. And then it could have use a loop to process the results. This method would be even more efficient as it would execute only one query against the database. So far we have manually controlled the cursor and there is many times when you want that level of control. However, Oracle has provided a streamlined method of using cursors with a FOR loop.

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.