Config Router

You are here: Home / Interview Questions / Cursors – ORACLE PL/SQL Chapter Wise Interview Questions

Cursors – ORACLE PL/SQL Chapter Wise Interview Questions

by

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.

CURSOR c1 (median IN NUMBER) IS
SELECT job, ename FROM emp WHERE sal > median;

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:

% FOUND, %ROWCOUNT, %NOTFOUND, %ISOPEN,
%BULK_ROWCOUNT, %BULK_EXCEPTIONS

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:

CURSOR cursor_name IS select_statement; 
cursor_name - A suitable name for the cursor. 
select _statement - A select query which returns multiple rows.

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:

DECLARE 
variables; 
records; 
create a cursor;
BEGIN 
OPEN cursor;
FETCH cursor;
process the records;
CLOSE cursor;
END;

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

General Syntax to open a cursor is:
OPEN cursor_name;
General Syntax to fetch records from a cursor is:
FETCH cursor_name INTO record_name;
OR                                                                               .
FETCH cursor_name INTO variable_list;
General Syntax to close a cursor is:
CLOSE cursor_name;

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.

Attributes: %FOUND
Return values: TRUE, if fetch statement returns at least one row. 
e.g. Cursor_name%FOUND
Return values: FALSE, if fetch statement doesn't return a row. 

Attributes: %NOTFOUND
Return values: TRUE, if fetch statement doesn't return a row. 
e.g. Cursor_name%NOTFOUND
Returns values: FALSE, if fetch statement returns at least one row. 

Attributes: %ROWCOUNT
Return values: The number of rows fetched by the fetch statement 
e.g. Cursor_name%ROWCOUNT
Returns values: If no row is returned, the PL/SQL statement returns an error.

Attributes: %ISOPEN
Return values: TRUE, if the cursor is already open in the program 
e.g. Cursor_name%ISQPEN
Returns values: FALSE, if the cursor is not opened in the program.

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.

E.g.
TYPE r_emp is RECORD (eno emp.empno% type,ename emp ename %type);
e_rec emp% ROWTYPE cursor cl is select empno,deptno from emp; 
e_rec c1 %ROWTYPE.

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:

FOR record_name IN cusror_name LOOP 
process the row...
END 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

blood_type CHAR := 'O'; 
can be rewritten as follows: 
blood_type CHAR DEFAULT 'O';

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;