Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Passing Parameters to PL/SQL Cursors

Oracle Database Tips by Donald BurlesonJuly 12, 2015


Passing Parameters to PL/SQL Cursors

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) is
select initcap(book_title) bk_title,
sum(quantity) sales, author_key
book join sales using (book_key)
join book_author using (book_key)
where author_key = v_key
group 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> declare
2 cursor c5 is
3 select initcap(author_last_name) a_name,
4 author_key
5 from author;
6 cursor c7 (v_key varchar2) is
7 select initcap(book_title) bk_title,
8 sum(quantity) sales, author_key
9 from
10 book join sales using (book_key)
11 join book_author using (book_key)
12 where author_key = v_key
13 group by initcap(book_title), author_key;
14 r_c5 c5%rowtype;
15 r_c7 c7%rowtype;
16 begin
17 open c5;
18 loop
19 fetch c5 into r_c5;
20 exit when c5%notfound;
21 dbms_output.put_line(chr(10)||
22 begin
23 open c7(r_c5.author_key);
24 loop
25 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.


Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training



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.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster