Introduction to Oracle 11g Cursors

Cursors are one of the most common and fundamental terms in the database terminology. It is one of the core database programming concepts, which forms a basic unit of execution of SQL statement.

A cursor is a pointer, which points towards a pre allocated memory location in the SGA. For transparent understanding, it is a handle or gateway adopted by Oracle to execute a SQL query. The memory location to which it points is known as Context area. Oracle associates every SELECT statement with a cursor to hold the query information in this context area.

Cursor follows a defined execution cycle to execute the SQL statement associated with it. The article describes the Oracle cursors and their usage.

There are two types of cursors: Implicit cursors and explicit cursors.

Implicit Cursors

Oracle server processes every SQL statement in a PL/SQL block as an implicit cursor. All the DML statements (INSERT, UPDATE or DELETE) and SELECT query with INTO or BULK COLLECT clauses are candidates for implicit cursors.

Whenever a SQL statement is executed, Oracle automatically allocates a memory area (known as context area) in Oracle database PGA i.e. Process Global Area. This allocated memory space is the query work area which holds the query related information.

For implicit cursor, the complete execution cycle is internally handled and maintained by the oracle server. For developers, implicit cursor appears to be an abstract concept. Only thing which is physically available and visible to them is the cursor status flags and information. Cursor attributes reveal the cursor related information and status. Following are the cursor attributes available

  • SQL%ROWCOUNT – Number of rows returned/changed in the last executed query. Applicable for SELECT as well as DML statement
  • SQL%ISOPEN – Boolean TRUE if the cursor is still open, else FALSE. For implicit cursor it is FALSE only
  • SQL%FOUND – Boolean TRUE, if the cursor fetch points to a record, else FALSE
  • SQL%NOTFOUND – Inverse of SQL%FOUND. The flag is set as FALSE when the cursor pointer does not point to a record in the result set.

These attributes are set at the different stages of execution cycle and retained in the context area.

Example Code [1]: The PL/SQL block below shows an implicit cursor created by SELECT statement.

DECLARE
L_SAL NUMBER;
BEGIN
SELECT SALARY
INTO L_SAL
FROM EMPLOYEES
WHERE EMPLOYEE_ID=110;
DBMS_OUTPUT.PUT_LINE(‘Salary for KING is : ’||TO_CHAR(L_SAL));
END;

Above example code contains a SELECT query in the executable section of the anonymous PL/SQL block. It executes successfully because the query returns scalar output. Having being the query returning multiple rowed result set, it would end up raising an exception and hence, abnormal termination.

The above coding practice carries its own pros and cons. If the SELECT query is expected and framed to written a single row, SELECT with INTO clause is performance efficient.

But, implicit cursor runs serious threat to the application, when it returns zero or multiple rows. Zero rows returning implicit cursor raises NO_DATA_FOUND exception, while multiple rows one raises TOO_MANY_ROWS exception. Check the exception situations in the below screen dumps.

Explicit cursors are best suited in situations where number of records in the result set is not known. It not only avoids the exception threat but also well indents the coding standards.

Single and Multiple row Implicit cursors

Oracle 11g identifies a different categorization basis of implicit cursors. Implicit cursors, based on number of rows affected by the cursors, can be categorized as single row implicit cursors and multiple row implicit cursors. All SELECT statements and DML statements which affect single row, processed within PL/SQL block are classified as Single row implicit cursors. All DML statements and cursor FOR loops, which affect multiple rows fall under the category of multiple row implicit cursors.

Example Code [2]: The PL/SQL block below contains single SELECT statement which selects salary of a single employee. Since, it returns a single row output, it demonstrates the construction of single row implicit cursor.

DECLARE
L_SAL NUMBER;
BEGIN
SELECT SALARY
INTO L_SAL
FROM EMPLOYEES
WHERE EMPLOYEE_ID=110;
END;

Example Code [3]: In the PL/SQL block below, UPDATE statement recalculates the salary of all employees in the company. As the DML statement affects multiple rows, it demonstrates a multiple row implicit cursor.

BEGIN
UPDATE EMPLOYEES
SET SALARY = SALARY + (COMM*SALARY);
END;

Explicit Cursor

These cursors are explicitly declared in the DECLARE section of the block. They possess a specific name and a static SELECT statement attached to them. Explicit cursors are manually executed by the developers and follow complete execution cycle.

Explicit cursor information is also captured in cursor attributes, which are set during the cursor processing and reveal essential information about the cursors. These attributes, as listed below, are same as that in implicit cursors but specific to the cursors.

  • CURSOR%ROWCOUNT
  • CURSOR%ISOPEN
  • CURSOR%FOUND
  • CURSOR%NOTFOUND

Cursor Execution Cycle

The key steps in the cursor execution cycle are OPEN, FETCH and CLOSE. But it would be worth expanding the complete execution cycle for better understanding about the processing of a cursor. A cursor execution cycle refers to the stages which a cursor follows to process and execute the query. The phases of cursor execution are listed below.

I shall explain the activity carried out by the server in the key phases.

OPEN stage

  • PGA memory allocation for cursor processing
  • Parsing of SELECT statement
  • Variable binding
  • SELECT Query execution
  • Move the record pointer to the first record

FETCH stage

The record, to which the record pointer points, is pulled from the result set. The record pointer moves only in the forward direction. The FETCH phase lives until the last record is reached.

CLOSE stage

After the last record of the result set is reached, cursor is closed, and allocated memory is flushed off and released back to SGA. Even if an open cursor is not closed, oracle automatically closes it after the execution of its parent block.

Setting of cursor attributes during cursor execution cycle

The cursor attributes, listed above, are set at different stages of this cycle. The table below captures the stage wise value of the status flags.

Syntax and illustration

Cursor definition
CURSOR [CURSOR NAME] IS
[SELECT QUERY]

Opening a Cursor
OPEN [CURSOR NAME]

Fetching records from the cursor
FETCH [CURSOR NAME] INTO [LIST OF VARIABLES]

Closing a cursor
CLOSE [CURSOR NAME]

Example Code [4] – The PL/SQL block below declares a cursor, which select employee name, job id and salary for the employees with employee id 100. Note the opening, fetching and closing of the cursor. Here cursor returns a single record.

DECLARE
CURSOR C_EMP IS
SELECT EMPLOYEE_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID=100;
L_ENAME VARCHAR2(100);
L_JOBID VARCHAR2(10);
L_SALARY NUMBER;
BEGIN
OPEN C_EMP;
FETCH C_EMP INTO L_ENAME, L_JOBID, L_SALARY;
DBMS_OUTPUT.PUT_LINE(‘Employee ‘||L_ENAME||’ with Job function of
‘||L_JOBID||’ draws ‘||TO_CHAR(L_SALARY)||’ per month’);
CLOSE C_EMP;
END;
Employee JOHN with Job function of MGR draws 2300 per month
PL/SQL procedure successfully completed.

Example Code [5]: When cursor result set contains multiple records, it has to be iterated to fetch each of them. The PL/SQL code below declares a cursor which selects employee details from department 10. A normal loop is used to iterate the result set and display the results.

DECLARE
CURSOR C_EMP IS
SELECT EMPLOYEE_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID=10;
L_ENAME VARCHAR2(100);
L_JOBID VARCHAR2(10);
L_SALARY NUMBER;
BEGIN
OPEN C_EMP;
LOOP
FETCH C_EMP INTO L_ENAME, L_JOBID, L_SALARY;
EXIT WHEN C_EMP%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(C_EMP%ROWCOUNT)||’ Employee ‘||L_ENAME||’
with Job function of ‘||L_JOBID||’ draws ‘||TO_CHAR(L_SALARY)||’ per month’);
END LOOP;
CLOSE C_EMP;
END;
/
1 Employee JOHN with Job function of MGR draws 2300 per month
2 Employee KATE with Job function of TECH draws 5500 per month
PL/SQL procedure successfully completed.

Cursor FOR loops

Explicit cursor processing can also be done using cursor FOR Loops. Cursor FOR loops improvise upon the performance and code interactivity by their implicit actions. Implicitly, they create variables, which can be accessed through cursor index. Even, cursor execution steps are implicitly carried out by them.

Example Code [6]: The above example code [5] can be rewritten using cursor FOR loop as below.

DECLARE
CURSOR C_EMP IS
SELECT EMPLOYEE_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID=10;
BEGIN
FOR I IN C_EMP
LOOP
DBMS_OUTPUT.PUT_LINE(‘Employee ‘||I.EMPLOYEE_NAME||’ with Job function of
‘||I.JOB_ID||’ draws ‘||TO_CHAR(I.SALARY)||’ per month’);
END LOOP;
END;
/

Employee JOHN with Job function of MGR draws 2300 per month
Employee KATE with Job function of TECH draws 5500 per month
PL/SQL procedure successfully completed.

Dynamic Cursor FOR Loops

Cursor FOR loops, as illustrated above, can be extended to include subqueries as dynamic cursors. This implies that the cursors would not be declared in the DECLARE section, but would be implicitly created when a subquery is attached to the FOR loop. I shall rewrite the cursor in Example Code [5] for loop as below.

Example Code [7]

BEGIN
FOR I IN (SELECT EMPLOYEE_NAME, JOB_ID, SALARY
FROM EMPLOYEES WHERE DEPARTMENT_ID=10)
LOOP
DBMS_OUTPUT.PUT_LINE(‘Employee ‘||I.EMPLOYEE_NAME||’ with Job function of
‘||I.JOB_ID||’ draws ‘||TO_CHAR(I.SALARY)||’ per month’);
END LOOP;
END;

Parameterized Cursors

Hard coding of values in the application programming has never been the persistent mode of programming. If you analyze the cursors used in the above example codes for demonstration purpose,
each one of them contains hardcoded value. Parameterized cursors provide cut to the problem by enabling programmer to pass parameter to the cursors. This reduces code redundancy, enhances code reusability and visibility in the program. The same cursor can be reopened for a different value and hence, different result set.

Example Code [8]: The cursor used in example code [5] can be redefined by introducing a
parameter.

DECLARE
CURSOR C_EMP (P_DEPT NUMBER) IS
SELECT EMPLOYEE_NAME, SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID=P_DEPT;
BEGIN
FOR I IN C_EMP(10)
LOOP
DBMS_OUTPUT.PUT_LINE(‘Employee ‘||I.EMPLOYEE_NAME||’ draws
‘||TO_CHAR(I.SALARY)||’ per month’);
END LOOP;
FOR I IN C_EMP(20)
LOOP
DBMS_OUTPUT.PUT_LINE(‘Employee ‘||I.EMPLOYEE_NAME||’ draws
‘||TO_CHAR(I.SALARY)||’ per month’);
END LOOP;
END;
/
Employee JOHN draws 2300 per month
Employee KATE draws 5500 per month
Employee MILLER draws 4820 per month
Employee NIC draws 4310 per month
Employee YEN draws 9420 per month
PL/SQL procedure successfully completed.

Illustrations

Using FOR UPDATE clause in cursor

In a multiple user application environment, application must be robust enough to overcome the locking scenarios. In Oracle, FOR UPDATE OF clause is used to lock a set of rows in a session. This concept can be used in explicit cursors also to impose exclusive row level lock on all the rows contained by the cursor query result set. These rows will remain locked until the session issues ROLLBACK or COMMIT.

FOR UPDATE OF allows a programmer to specify the column names which are more intend to change in other sessions. Nevertheless, the rows would be locked to protest updates against all the columns, but still OF list provides flexibility to make note of ‘update prone’ columns.

Oracle provides WHERE CURRENT OF clause to update or delete the rows which are locked by the FOR UPDATE OF cursor in the session. The clause identifies the row to be updated and thus, prevents coding for row identification from the cursor result set.

Example Code [9]: The PL/SQL block below updates the salary of all employees working in department 30.

DECLARE
CURSOR c1 IS
SELECT SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 30
FOR UPDATE OF SALARY;
L_SALARY NUMBER;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO L_SALARY;
EXIT WHEN C1%NOTFOUND;
UPDATE EMPLOYEES
SET SALARY = SALARY + 1200
WHERE CURRENT OF c1;
END LOOP;
CLOSE c1;
END;

Exceptions with cursors

INVALID_CURSOR and CURSOR_ALREADY_OPEN are the two exceptions which are raised, when oracle optimizer encounters aberrant cursor.

The exception INVALID_CURSOR is raised when programmer attempts to operate a non allowed operation on the cursor. If the operation points to a cursor which does not exist, or which does not points to any SQL context area, the INVALID exception is raised.

Example Code [10a]: Demonstrating INVALID_CURSOR exception

DECLARE
CURSOR C_DEPT IS
SELECT DEPARTMENT_NAME, LOCATION_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_ID=20;
L_DEPTNAME VARCHAR2(100),
L_LOC NUMBER;
BEGIN
OPEN C_EMP;
FETCH C_EMP INTO L_DEPTNAME, L_LOC;
CLOSE C_EMP;
DBMS_OUTPUT.PUT_LINE(L_DEPTNAME||’.’||L_LOC);
END;
/
DECLARE
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at “SCOTT.C_EMP”, line 10
ORA-06512: at line 1

Example Code [10b]: Demonstration of CURSOR_ALREADY_OPEN exception

DECLARE
CURSOR C_DEPT (P_DEPTID NUMBER) IS
SELECT DEPARTMENT_NAME, LOCATION_ID
FROM DEPARTMENTS
WHERE DEPARTMENT_ID=P_DEPTID;
L_DEPTNAME VARCHAR2(100),
L_LOC NUMBER;
BEGIN
OPEN C_DEPT (10);
FETCH C_DEPT INTO L_DEPTNAME, L_LOC;
DBMS_OUTPUT.PUT_LINE(L_DEPTNAME||’.’||L_LOC);
OPEN C_DEPT (20);
FETCH C_DEPT INTO L_DEPTNAME, L_LOC;
CLOSE C_DPET;
DBMS_OUTPUT.PUT_LINE(L_DEPTNAME||’.’||L_LOC);
END;
DECLARE
*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at line 3
ORA-06512: at line 13

Conclusion

I have made a sincere effort to familiarize you all with the world of cursors, which would be justified with the interest of readers and visitors. The codes and explanations are based on self hands on and observations.

38 Responses to Introduction to Oracle 11g Cursors

  1. rahim February 5, 2014 at 11:28 pm #

    Good one. thanks…….

  2. Hema December 17, 2013 at 8:19 am #

    Thanks for your Good explanation on the Cursors.

  3. Saurabh K. Gupta November 24, 2013 at 7:52 pm #

    Thank you all. Your comments means a lot to me.

    Cheers and regards – Saurabh

  4. Sam November 3, 2013 at 12:26 pm #

    This article got 100/100 marks. its nice.

  5. cursos en el sena virtual October 18, 2013 at 2:24 am #

    hola acabo de enterarme de tu blog y la verdad es que me parece muy bueno no sabia de mas personas interesadas en estos temas, aqui tienes un nuevo lector que seguira visitandote abitualmente.

  6. Kandi July 27, 2013 at 1:52 pm #

    Thank you for any other wonderful article. Where else may just anyone get
    that kind of information in such a perfect manner of writing?
    I’ve a presentation subsequent week, and I am at the look for such information.

  7. Umesh May 29, 2013 at 10:59 am #

    Very Nice article Specially Cursor Execution Cycle Part.

  8. ganesh April 25, 2013 at 12:25 pm #

    Your 1st Stored proc selects only the first row of the query ‘Select x from z’ and fetches its result set (x) into y. Only the first row returned by the above query is given as output here.

    Your 2nd line, curs cursor selects the column x with all the rows from the table z . After opening the curs cursor, you are fetching the result set (x) into z again. Why is this ? You are selecting the records from z and fetching the same into z. Why is this ? I think your fetch statement should be updated as
    fetch x into y;

  9. DLoewen February 15, 2013 at 11:01 pm #

    Which, if either, of the following would be more efficient in a function that only needs to look at the first row of a query/cursor. Or are they basically identical under the covers?

    1. begin Select x into y from z where rownum=1; exception …….end;

    2. cursor curs is select x from z;
    ..
    ..
    open curs;
    fetch x into z;
    close curs;

    Thank you in advance.

    D

  10. Navneet February 5, 2013 at 7:42 am #

    Can someone please explian me what is cursor with hold in oracle. Do cursor with hold clause really exists in oracle. I did a net search but what i found is cursor with hold is a db2 clause. If it exists in oracle a simple program can help a lot!!!

    • Santhosh November 29, 2013 at 5:25 am #

      If you wanna keep the cursor open even after COMMIT or ROLLBACK then use this… however not in ORACLE but in DB2

      HTH..

  11. Girish January 17, 2013 at 9:49 am #

    Article is very Good. Good explanation on the Cursors.
    Thanks for posting.

  12. Puneet December 26, 2012 at 5:36 am #

    great work..mate

  13. Mudassir December 20, 2012 at 8:08 am #

    Articulate article.. gives a clear understanding and vision of the Concept..
    No Thanks.. Only Gratitude :D

  14. sudar December 7, 2012 at 10:26 pm #

    excellent!

  15. Irfan October 30, 2012 at 12:48 pm #

    Thanks a ton. Its informative clear and consise information on oracle 11g cursors. I am able to develope my application by refering to this post.

  16. Nic October 16, 2012 at 8:36 pm #

    Hello
    You say that “A cursor is a pointer, which points towards a pre allocated memory location in the SGA. For transparent understanding, it is a handle or gateway adopted by Oracle to execute a SQL query. The memory location to which it points is known as Context area.” is the pre alocated area the Context area? as below you state that “Oracle automatically allocates a memory area (known as context area) in Oracle database PGA ” going fwd at the time when a explicit cursor is closed “After the last record of the result set is reached, cursor is closed, and allocated memory is flushed off and released back to SGA.”

    Can you provide more info/ be more explicit about these memory areas?

    Thanks in advance!

  17. Selvakumar August 24, 2012 at 1:19 pm #

    Really Great tutorial for Begineers
    Thanks a lot

  18. Janardhan August 18, 2012 at 7:10 am #

    Nice Tutorial , Really helped in understanding Cursors…Thanks a lot for Posting

  19. Tiep June 25, 2012 at 3:15 pm #

    In the beginning of the text you said the cursor points to an allocated memory in SGA (known as context area), yet you said the context area is in the PGA then. So, I’m so confused!!

    • Santhosh November 29, 2013 at 5:18 am #

      cursor area will be allocated in UGA (User Global Area), that would be in your PGA, unless you are not in shared sever connections. If so then area will be allocated in SGA.

      HTH….

  20. Emerson Isak June 5, 2012 at 2:17 pm #

    Excelente tutorial y explciacion de cursores :D graxias

  21. Dhiraj Kumar April 23, 2012 at 7:54 pm #

    Thanks alot for a concise introduction to Oracle Cursor. This was very helpful to me in understanding the underlying concept.

  22. Shraddha April 5, 2012 at 6:56 am #

    This tutorial is very effective for beginners like me. It gives a complete baseline information to visualize how cursors undergo memory management and how the data is fetched. It would be more helpful if I would be able to find out the difference in cursors of different versions of Oracle.

    Thanks.

    • raam August 11, 2012 at 9:24 am #

      hi,
      shradda it is fine and great encuragement

  23. Vinod October 31, 2011 at 11:15 pm #

    Great Article…..

    Thanks

  24. Saurabh Gupta August 6, 2011 at 3:23 am #

    Thanks for the feedback Ajay,

    Cursor memory will be freed as soon as the cursor is closed but the memory allocated for the cursor variable will remain busy until the block execution is over or the connection is closed. The same applies for the memory variables also.

    Hope it helps you !

    Saurabh
    sbhoracle.wordpress.com

  25. Ajay August 5, 2011 at 5:22 am #

    Dear Sir/Madam,

    The article is really good and gives good picture on cursors.

    i have following questions, it will be very much helpful if i get answers
    1.what happens to allocated memory once cursor is closed or loop ended in case for
    2.is their any way to release memory allocated to variables(including varchar, number etc)

    Please keep me posted for future articles

    Thanking you
    Ajay K.N

Trackbacks/Pingbacks

  1. Introduction to Oracle 11g Cursors | dbanotes.com « Siam Source Code – สยามซอร์สโค้ด - June 23, 2011

    […] Read this article: Introduction to Oracle 11g Cursors | dbanotes.com […]