Cursors – ORACLE PL/SQL Chapter Wise Interview Questions

Cursors – ORACLE PL/SQL Chapter Wise Interview Questions

Question 1:
What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?
Answer:
Cursors allow row-by-row processing of the resultsets.
Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip; where as a normal SELECT query makes only one roundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations).Further, there are restrictions on the SELECT statements that can be used with some types of cursors. Most of the times, set based operations can be used instead of cursors.

Question 2:
What are PL/SQL Cursor Exceptions?
Answer:
Cursor_Already_Open, Invalid_Cursor

Question 3:
What is a package cursor?
Answer:
A package cursor is a cursor which you declare in the package specification without an SQL statement. The SQL statement for the cursor is attached dynamically at runtime from calling procedures.

Question 4:
If all the values from a cursor have been fetched and another fetch is issued, the output will be: error, last record or first record?
Answer:
Last Record

Question 5:
Can you use %RowCount as a parameter to a cursor?
Answer:
Yes

Question 6:
What is the difference between Implicit and Explicit Cursors?
Answer:
Implicit Cursors are declared and used by Oracle internally, whereas Explicit Cursors are declared and used by users. More over Implicit cursors do not need declaration; i.e. Oracle creates process and closes automatically. An explicit cursor should be declared and closed by the user.

Question 7:
Is it possible to pass parameter in cursor? How?
Answer:
Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear.

Question 8:
What is a Cursor?
Answer:
A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time.

Question 9:
What is an Implicit Cursor?
Answer:
Oracle opens an Implicit Cursor to process each SQL statement not associated with an Explicit Cursor.

Question 10:
Name a few Implicit Cursor attributes.
Answer:

Question 11:
What are Cursor Variables? Mention Advantages?
Answer:

  1. Also called REF CURSORS.
  2. They are not tied to a single SQL. They point to any SQL area dynamically.
  3. Advantage is: You can declare variables at Client side and open them Server side. You can thus centralize data retrieval

Question 12:
What is an Explicit Cursor?
Answer:
An Explicit Cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which returns more than one row. We can provide a suitable name for the cursor.
The General Syntax for creating a cursor is as given below:

Question 13:
How to use Explicit Cursor?
Answer:
Steps involved in using Explicit Cursors are as follows:

  1. DECLARE the cursor in the declaration section.
  2. OPEN the cursor in the Execution Section.
  3. FETCH the data from cursor into PL/SQL variables or records in the Execution Section.
  4. CLOSE the cursor in the Execution Section before you end the PL/SQL Block

General Form of using an explicit cursor is:

Question 14:
What is the general syntax to use Explicit cursor?
Answer:

Question 15:
What are Explicit Cursor Attributes?
Answer:
Oracle provides some attributes for Explicit Cursors to control the data processing while using cursors. We use these attributes to avoid errors while accessing cursors through OPEN, FETCH and CLOSE Statements.
These are the attributes available to check the status of an explicit cursor.

Question 16:
What is the difference between %ROWTYPE and TYPE RECORD?
Answer:
% ROWTYPE is to be used whenever query returns an entire row of a table or view. TYPE rec RECORD is to be used
whenever query returns columns of different table or views and variables.

Question 17:
What is the difference between NO_DATA_FOUND and %NOTFOUND?
Answer:
NO_DATA_FOUND is an exception which is raised when either an implicit query returns no data, or you attempt to reference a row in the PL/SQL table which is not yet defined.
SQL%NOTFOUND, is a BOOLEAN attribute indicating whether the recent SQL statement does not match to any row.

Question 18:
What are the benefits of using CURSOR with FOR LOOP?
Answer:
When using FOR LOOP you need not declare a record or variables to store the cursor values, need not open, fetch and close the cursor. These functions are accomplished by the FOR LOOP automatically.
General Syntax for using FOR LOOP:

Question 19:
How to use a DEFAULT keyword, explain with example?
Answer:
You can use the keyword DEFAULT instead of the assignment operator to initialize variables. For example, the declaration

Question 20:
Explain the attribute %TYPE with example.
Answer:
The %TYPE attribute provides the datatype of a variable or database column. In the following example, %TYPE provides the datatype of a variable:
credit REAL(7,2); debit credit%TYPE;
Variables declared using %TYPE are treated like those declared using a datatype specifier
The %TYPE attribute is particularly useful when declaring variables that refer to database columns. You can reference a table and column, or you can reference an owner, table, and column, as in my_dname scott.dept.dname%TYPE;

About the author

Scott

Leave a Comment