Interview Questions -- Cursors

What are cursors? Distinguish between implicit and explict cursors ?

Oracle execute every query in a seperate memory area called context area and pointer (with a name) to this area is known as cursor. Implicit cursor are internally used by oracle engine to run a query. Explicit cursors are user defined which are declared & open for a select query in pl/sql code block. They are used when SELECT INTO query return more than one row and we need to process each row one by one.

What are the different parts of an Explicit Cursor ?

The different parts in the process of making an Explicit Cursor are as follows:

1. Declaring the Cursor

2. Opening the Cursor

3. Fetching the Cursor

4. Closing the Cursor

What are the cursor attributes used in PL/SQL ?

%Rowcount: checks the number of rows that are updated, deleted or fetched.

%Isopen: checks whether the Cursor you want to access is currently open or closed.

%Found: checks if the Cursor fetched a row. It returns a TRUE if any row is fetched.

%NotFound: checks if the Cursor fetched any row or not. It returns a TRUE value if any row is not fetched.

What is the difference between NO DATA FOUND and %NOTFOUND ?

NO DATA FOUND is a oracle exception which is thrown if the SELECT INTO statement doesn't return at least one row. While %NOTFOUND is cursor attribute to check whether cursor has any row or not.

State the advantage and disadvantage of Cursor ?

Advantages:

• we can perform row by row processing so we can perform row wise validation or operations on each row.
• Cursors can be faster than a for loop but at the cost of more overhead

Disadvantages:

• occupies memory from your system that may be available for other processes
• Need to be closed properly to avoid ORA-01000: maximum open cursors exceeded

What is cursor for loop ?

The cursor FOR LOOP statement implicitly declares its loop index as a record variable of the row type that a specified cursor returns, and then opens a cursor. With each iteration, the cursor FOR LOOP statement fetches a row from the result set into the record. When there are no more rows to fetch, the cursor FOR LOOP statement closes the cursor. The cursor also closes if a statement inside the loop transfers control outside the loop or raises an exception.

Explain the usage of WHERE CURRENT OF clause in cursors ?

WHERE CURRENT OF clause is used along with FOR UPDATE clause. When the session opens a cursor with the FOR UPDATE clause, all rows in the return set will hold row-level exclusive locks. Other sessions can only query the rows, but they cannot update, delete, or select with FOR UPDATE.

The WHERE CURRENT OF clause is used with UPDATE or DELETE statement to update or delete the most recent row fetched from the cursor.

Syntax: WHERE [CURRENT OF cursor_name | search_condition]

What is ref cursor ?

A REF CURSOR is a PL/SQL data type whose value is the memory address of a query work area on the database. In essence, a REF CURSOR is a pointer or a handle to a result set on the database.

What are types of Ref cursor ?

There are 2 types of REF cursors : Strong and weak.

TYPE emp_curtype IS REF CURSOR RETURN emp%ROWTYPE; --Strong REF cursor

TYPE emp_curtype IS REF CURSOR; --weak REF cursor

Which one is better - Strong or weak ?

It depends on your requirement. In strong cursor, we attach the return ROWTYPE at the declaration time itself. So, the compiler knows what the cursor variable should return, making it less error prone. On the other hand, weak cursors are more flexible because they can be used with any query, any return ROWTYPE, even different ROWTYPES at different calls to it.

What is the difference between cursor & ref cursor ?

A "normal" plsql cursor is static in defintion while Ref cursors may be dynamically opened or opened based on logic.

A ref cursor can be returned to a client -- a cursor cannot be.

A ref cursor can be passed from subroutine to subroutine -- a cursor cannot be.

A cursor can be global -- a ref cursor cannot.

What are the alternative to cursors for better performance ?

Cursors are not bad for performance. However cursor for loops are bad as they process row by row. You can use bulk collect with SELECT INTO by making sure there is a list of collections in the INTO clause. Even in Oracle 10g and higher Oracle will implicitly perform bulk collects.

What are the Restrictions on Cursor Variables ?

• You cannot declare a cursor variable in a package specification. That is, a package cannot have a public cursor variable (a cursor variable that can be referenced from outside the package).
• You cannot store the value of a cursor variable in a collection or database column.
• You cannot use comparison operators to test cursor variables for equality, inequality, or nullity.
• Using a cursor variable in a server-to-server remote procedure call (RPC) causes an error. However, you can use a cursor variable in a server-to-server RPC if the remote database is a non-Oracle database accessed through a Procedural Gateway.
• You cannot use LOB parameters in a server-to-server RPC.

Which is the Default Cursor in Oracle PL/SQL ?

Implicit Cursors are the Default Cursor in PL/SQL. These cursors are automatically activated when DML statements are encountered such as Insert, Delete or Update.

What is an Active Set ?

The set of rows that a Cursor holds at a single point of time is called as an Active Set.

Why is closing the Cursor required during explicit cursor development ?

It is important because it will consume system memory while it is in active state and if it is not closed or terminated then it won’t let the other things in the memory as memory will be occupied and later on it will be full. Hence, deletion is necessary. At some point time you may face error ORA-01000: maximum open cursors exceeded.

What cursor type do you use to retrieve multiple recordsets ?

Explicit cursors and Ref cursor if recordset need to be returned to calling program.