How Cursor Works Internally?

Why Do we need Cursor and How Cursor Works Internally?

Before understanding the process let’s check why do we need Cursor.

Why Do we need Cursor?

The cursor is used to handle multiple row query in PL/SQL. The TOO_MANY_ROWS Exception (ORA-01422) occurs when a SELECT INTO statement returns more than one row. Cursor helps us to avoid this error.

For Detailed study about CURSOR- Click here.

Now, Let’s understand this practically-

Consider below table-

SQL> SELECT * From EMP_TEST;

        ID NAME           SALARY STATUS         DEPTNO  MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
       102 Ankit            8000 Active             20        101
       104 Nikhil          69000 Active             10        101
       105 Rajan           18000 Active             20        112
       107 Karan          101000 Active             20        112
       110 Sajal           88000 Active             10        101
       103 Ravi            45000 InActive           30        104
       106 Surya           67000 Active             30        104
       108 Sam             99000 InActive           20        105
       109 Jack            77000 Active             10        106

9 rows selected.

Let’s process the rows from this table. First fetch data for one Employee and see the result.

SQL> DECLARE
  2  V_name Varchar2(20);
  3  V_sal EMP_TEST.SALARY%TYPE;
  4  BEGIN
  5  SELECT Name, Salary INTO V_name,V_Sal From EMP_TEST
  6  Where ID=107;
  7  DBMS_OUTPUT.PUT_LINE('Employee: '||V_name||' Has Salary - '||V_sal);
  8  EXCEPTION
  9  When NO_DATA_FOUND Then
 10     DBMS_OUTPUT.PUT_LINE('Employee Not Found');
 11  When TOO_MANY_ROWS Then
 12     DBMS_OUTPUT.PUT_LINE('Too Many Rows Exception');
 13  End;
 14  /
Employee: Karan Has Salary - 101000

PL/SQL procedure successfully completed.

As you can see it’s been executed successfully. Now let’s do complete fetch- Remove where clause from above Block and see the result.

SQL> DECLARE
  2  V_name Varchar2(20);
  3  V_sal EMP_TEST.SALARY%TYPE;
  4  BEGIN
  5  SELECT Name, Salary INTO V_name,V_Sal From EMP_TEST;
  6  DBMS_OUTPUT.PUT_LINE('Employee: '||V_name||' Has Salary - '||V_sal);
  7  EXCEPTION
  8  When NO_DATA_FOUND Then
  9     DBMS_OUTPUT.PUT_LINE('Employee Not Found');
 10  When TOO_MANY_ROWS Then
 11     DBMS_OUTPUT.PUT_LINE('Too Many Rows Exception');
 12  End;
 13  /
Too Many Rows Exception

PL/SQL procedure successfully completed.

The TOO_MANY_ROWS Exception (ORA-01422) occurs when a SELECT INTO statement returns more than one row.

When you use SELECT in a PL/SQL block, it's important to make sure that exactly one row will always be returned by your query. If more than one row is returned, the TOO_MANY_ROWS exception occurs. 

Solution to above problem-

•          Declare a Private area
•          Fetch required Data from base table to Private area.
•          Now You Can Process rows from Private area one by one.

This Private area (Memory object) is what we call CURSOR. By using Cursor, we can resolve above problem (TOO_MANY_ROWS Exception)

Let’s Define a Cursor and see how this is going to help in above problem.

DECLARE- OPEN – FETCH - CLOSE

Declare

V_name Varchar2(20);

V_sal EMP_TEST.Salary%TYPE;

Cursor C1 IS

Select Name, Salary From EMP_TEST;

Begin

                  Open C1;

                  Loop

                  FETCH C1 Into V_name, V_sal;

                  EXIT WHEN C1%NOTFOUND;

                  DBMS_OUTPUT.PUT_LINE('Employee : ' || V_name || ' Has Salary - ' || V_sal);

                  End Loop;

                  Close C1;

End;

SQL> Declare
  2  V_name Varchar2(20);
  3  V_sal EMP_TEST.Salary%TYPE;
  4  Cursor C1 IS
  5  Select Name, Salary From EMP_TEST;
  6  Begin
  7                    Open C1;
  8                    Loop
  9                    FETCH C1 Into V_name, V_sal;
 10                    EXIT WHEN C1%NOTFOUND;
 11                    DBMS_OUTPUT.PUT_LINE('Employee : ' || V_name || ' Has Salary - ' || V_sal);
 12                    End Loop;
 13                    Close C1;
 14  End;
 15  /
Employee : Ankit Has Salary - 8000
Employee : Nikhil Has Salary - 69000
Employee : Rajan Has Salary - 18000
Employee : Karan Has Salary - 101000
Employee : Sajal Has Salary - 88000
Employee : Ravi Has Salary - 45000
Employee : Surya Has Salary - 67000
Employee : Sam Has Salary - 99000
Employee : Jack Has Salary - 77000

PL/SQL procedure successfully completed.

You can also do this using Cursor FOR LOOP. In this case, you don’t need to Open-Fetch-Close Cursor. That will be done internally by ORACLE.

The cursor FOR LOOP statement lets you run a SELECT statement and then immediately loop through the rows of the result set.

•  Declare a Cursor.
•  In this case, you don’t need to define variables. Give composite data at the time of Loop.
•  You will give composite Data type at the time of FOR LOOP and then individual fields can be fetched from  this composite Data type.
•  FOR loop, will traverse the rows will automatically Exit after last fetch.

Let’s Define a Cursor and see how this is going to help in above problem.

Declare

Cursor C1 IS

Select Name, Salary From EMP_TEST;

Begin

                  FOR rec IN C1 Loop

                  DBMS_OUTPUT.PUT_LINE('Employee : ' || rec.NAME|| ' Has Salary - ' || rec.SALARY);

                  End Loop;

End;

SQL> Declare
  2  Cursor C1 IS
  3  Select Name, Salary From EMP_TEST;
  4  Begin
  5                    FOR rec IN C1 Loop
  6                    DBMS_OUTPUT.PUT_LINE('Employee : ' || rec.NAME|| ' Has Salary - ' || rec.SALARY);
  7                    End Loop;
  8  End;
  9  /
Employee : Ankit Has Salary - 8000
Employee : Nikhil Has Salary - 69000
Employee : Rajan Has Salary - 18000
Employee : Karan Has Salary - 101000
Employee : Sajal Has Salary - 88000
Employee : Ravi Has Salary - 45000
Employee : Surya Has Salary - 67000
Employee : Sam Has Salary - 99000
Employee : Jack Has Salary - 77000

PL/SQL procedure successfully completed.

Now Let’s see How Cursor Works Internally?

Using Implicit Cursor-

•  Oracle Internally creates Implicit Cursor.
•  Whenever a DML operation like SELECT, INSERT, UPDATE happens on a row Oracle creates the Cursor  internally.
•  Select on a single row is Implicit Cursor.
•  You can not specify a name to Implicit Cursor.
•  Status of Implicit Cursor can be found using SQL%ROWCOUNT.

Using Explicit Cursor-

•  Declare Cursor for Initializing memory.
•  Open Cursor for allocation memory.
•  Fetch Cursor for retrieving records.
•  Close cursor for releasing memory

Working with Explicit Cursor-

•  If the query does not identify any rows, Oracle Database will not raise NO_DATA_FOUND. Instead, the  Cursor_name%NOTFOUND attribute will return TRUE.
•  Your query can return more than one row, and Oracle Database will not raise TOO_MANY_ROWS.
•   When you declare a cursor in a package (that is, not inside a subprogram of the package) and the cursor is  opened, it will stay open until you explicitly close it or your session is terminated.
•  When the cursor is declared in a declaration section (and not in a package), Oracle Database will also automatically close it when the block in which it is declared terminates. It is still, however, a good idea to explicitly close the cursor yourself. If the cursor is moved to a package, you will have the now necessary CLOSE already in place. 

Let’s check the below cases.

Suppose we are fetching Data into Cursor in One session and now inserting some records in another session to the same table. Will the recently inserted records would be processed by that cursor?

Read Consistency- When you OPEN the cursor, before the first bit of data is even touched, before we do any IO at all - the result set is already "known". Oracle saves the SCN (system change number) and ensures that every block you read while processing that query is rolled back to that point in time.

Since the inserts were not committed when we OPENED the query, the newly inserted data will not be SEEN by the query running in first session Because changes were not committed when we started data fetch in first session.

When Cursor actually gets data (Query gets executed)- at the time we Open Cursor or Fetch from Cursor?

Let’s check this practically with the help of below Table Data-

SQL> SELECT * From EMP_TEST;

        ID NAME           SALARY STATUS         DEPTNO  MANAGERID
---------- ---------- ---------- ---------- ---------- ----------
       102 Ankit            8000 Active             20        101
       104 Nikhil          69000 Active             10        101
       105 Rajan           18000 Active             20        112
       107 Karan          101000 Active             20        112
       110 Sajal           88000 Active             10        101
       103 Ravi            45000 InActive           30        104
       106 Surya           67000 Active             30        104
       108 Sam             99000 InActive           20        105
       109 Jack            77000 Active             10        106

9 rows selected.

In the below Block Let’s delete some records from Table after Opening Cursor.

Declare Cursor- Open Cursor- Delete Some Records- Fetch from Cursor- Close Cursor

Declare

    Cursor C is Select * From EMP_TEST;

    Emp_rec EMP_TEST%rowtype;

Begin

    Open C;

    Delete From EMP_TEST where ID IN(102,104,106);

    Loop

    Fetch C Into Emp_rec;

    Exit When c%NotFound ;

    Dbms_Output.Put_Line(Emp_rec.ID || ' : ' || Emp_rec.NAME);

    End loop;

    Close C;

                  Dbms_Output.Put_Line('Records After Deletion');

                   FOR rec IN C Loop

                  DBMS_OUTPUT.PUT_LINE(rec.ID||' : ' || rec.NAME);

                  End Loop;

End;

SQL> Declare
  2      Cursor C is Select * From EMP_TEST;
  3      Emp_rec EMP_TEST%rowtype;
  4  Begin
  5      Open C;
  6      Delete From EMP_TEST where ID IN(102,104,106);
  7      Loop
  8      Fetch C Into Emp_rec;
  9      Exit When c%NotFound ;
 10      Dbms_Output.Put_Line(Emp_rec.ID || ' : ' || Emp_rec.NAME);
 11      End loop;
 12      Close C;
 13                    Dbms_Output.Put_Line('Records After Deletion');
 14                     FOR rec IN C Loop
 15                    DBMS_OUTPUT.PUT_LINE(rec.ID||' : ' || rec.NAME);
 16                    End Loop;
 17  End;
 18  /
102 : Ankit
104 : Nikhil
105 : Rajan
107 : Karan
110 : Sajal
103 : Ravi
106 : Surya
108 : Sam
109 : Jack
Records After Deletion
105 : Rajan
107 : Karan
110 : Sajal
103 : Ravi
108 : Sam
109 : Jack

PL/SQL procedure successfully completed.

This proves that Cursor gets data when we Open Cursor.

Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.