I've been writing basic web apps for a year (for an Oracle db), and since the functions are pretty simple, most of us stick with regular FOR loops to get our data:

for i in (select * from STUDENTS) loop
      htp.prn(i.student_last_name || ', ' || i.student_first_name || ' ' || i.student_dob);
end loop;

But, cursors seem to be the 'right' way to do things. I can find lots of information on what cursors are and different ways to loop through them, but I can't find a solid reason why to use them over regular FOR loops. Is it dependent on the needs of the procedure? Are there inherent advantages I should be aware of?

  • This type of FOR is just another way to use cursors. See the docs: docs.oracle.com/cd/E11882_01/appdev.112/e10472/… Anyway, what htp.prn() does?
    – dezso
    Oct 16, 2012 at 18:43
  • That's one of our output functions. So, do you have a preference on which method to use?
    – ini
    Oct 16, 2012 at 18:47
  • For this type of things the FOR loop is much more readable, I think. I tend to use 'real' cursors only if I have to step backwards, not just forwards. I asked that other question because I can imagine a table function instead of htp.prn().
    – dezso
    Oct 16, 2012 at 18:51
  • It's worth mentioning that both forms of cursor have inferior performance to a pure SQL solution -- particularly relevant to DML statements. Aug 28, 2015 at 19:47

4 Answers 4


A cursor can be explicit or implicit, and either type can be used in a FOR loop. There are really two aspects to your question.

  1. Why use an explicit cursor FOR loop over an implicit cursor FOR loop?

    • Use an explicit cursor FOR loop when the query will be reused, otherwise an implicit cursor is preferred.
  2. Why use a loop with a FETCH rather than a FOR loop that doesn’t have an explicit FETCH?

    • Use a FETCH inside a loop when you need to bulk collect or when you need dynamic SQL.

Here is some useful information from the documentation.

Example of Implicit Cursor FOR LOOP

   FOR vItems IN (
      SELECT last_name
      FROM employees
      WHERE manager_id > 120
      ORDER BY last_name
      DBMS_OUTPUT.PUT_LINE ('Name = ' || vItems.last_name);

Example of Explicit Cursor FOR LOOP

      SELECT last_name
      FROM employees
      WHERE manager_id > 120
      ORDER BY last_name;
   FOR vItems IN c1 LOOP
      DBMS_OUTPUT.PUT_LINE ('Name = ' || vItems.last_name);

Implicit Cursor

An implicit cursor is a session cursor that is constructed and managed by PL/SQL. PL/SQL opens an implicit cursor every time you run a SELECT or DML statement. You cannot control an implicit cursor, but you can get information from its attributes.

An implicit cursor closes after its associated statement runs; however, its attribute values remain available until another SELECT or DML statement runs.


Explicit Cursor

An explicit cursor is a session cursor that you construct and manage. You must declare and define an explicit cursor, giving it a name and associating it with a query (typically, the query returns multiple rows). Then you can process the query result set in either of these ways:

Open the explicit cursor (with the OPEN statement), fetch rows from the result set (with the FETCH statement), and close the explicit cursor (with the CLOSE statement).

Use the explicit cursor in a cursor FOR LOOP statement (see "Query Result Set Processing With Cursor FOR LOOP Statements").

You cannot assign a value to an explicit cursor, use it in an expression, or use it as a formal subprogram parameter or host variable. You can do those things with a cursor variable (see "Cursor Variables").

Unlike an implicit cursor, you can reference an explicit cursor or cursor variable by its name. Therefore, an explicit cursor or cursor variable is called a named cursor.

Cursor FOR LOOP Statements

The cursor FOR LOOP statement lets you run a SELECT statement and then immediately loop through the rows of the result set. This statement can use either an implicit or explicit cursor.

  • 1
    Implicit cursors fetch 100 rows at a time from 10g onwards. Aug 28, 2015 at 19:46

The code you posted is using a cursor. It is using an implicit cursor loop.

There are cases where using an explicit cursor loop (i.e. declaring a CURSOR variable in the declaration section) produces either cleaner code or better performance

  1. If you have more complex queries that you can't refactor out into views, it can make the code easier to read if your loop iterates over student_cursor rather than including a 30 line SQL statement that embeds a bunch of logic. For example, if you were printing out all the students that were cleared to graduate and that involved joining to tables that had their academic records, the requirements of their degree program, tables with information on academic holds, tables with information about overdue library books, tables with information about unpaid fees, administrative overrides, etc. it would probably make sense to refactor the code so that this query wasn't stuck in the middle of code that is concerned with presenting the list to a user. That might involve creating a view that would encapsulate all this logic. Or it might involve creating an explicit cursor that was declared either as part of the current PL/SQL block or in some higher-level PL/SQL block (i.e. a cursor declared in a package) so that it is reusable. Or it might involve doing something else for encapsulation and reusability (say, creating a pipelined table function instead).
  2. If you want to make use of bulk operations in PL/SQL, you generally want to use explicit cursors. Here is a StackOverflow thread that discusses the performance differences between explicit and implicit cursors. If all you are doing is calling htp.prn, doing a BULK COLLECT probably doesn't buy you anything. In other cases, though, it can result in substantial performance improvements.

I see that many developers are using explicit cursors instead of implicit cursors out of old habit. This because back in Oracle version 7 this was always the more efficient way to go. Nowadays there are generally the other way around. Specially with the optimizer that if needed may rewrite implicit cursor for loops to a bulk collect.


Recently I had to rewrite a bunch of queries from an implicit FOR loop into explicit cursors. The reason was that the queries fetched data from an external database via link and this database had a different encoding than our local database. When transferring data from the implicit cursor into a locally defined record type misterious intermittent errors occurred (only on certain specific rows). Our DBA explained this to us, we wouldn't have been able to get to the bottom of this ourselves. It seems this is a bug in Oracle that's been reported.

We were advised to rewrite everything using explicit cursors and the error was gone.

Not the primary reason you might want to use explicit over implicit, but worth a note.

EDIT: Oracle 12c.

  • Could you add in the bug and/or note number so those reading this can know more about the symptoms and if/when this is resolved? May 24, 2016 at 13:23
  • Sorry, bug reporting was done by one of our DBA's, I don't have access to that information.
    – Robotron
    May 24, 2016 at 13:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.