1. What is a Cursor in Oracle?
A cursor is a mechanism used by Oracle to process the result of a SQL statement, especially when working with rows returned by a query.
SQL Query
↓
Result Set
↓
Cursor
↓
Process rows2. Why do we use Cursors?
Cursors are useful when you need to process query results row by row, perform calculations for each row, apply different logic to different rows, retrieve multiple rows in PL/SQL, or process records sequentially.
3. What are the types of Cursors?
Oracle cursors are mainly divided into two types: Implicit Cursor and Explicit Cursor.
CURSORS
|
┌───────┴───────┐
↓ ↓
Implicit Explicit
Cursor Cursor4. What is an Implicit Cursor?
An implicit cursor is automatically created by Oracle whenever a SQL statement is executed. You do not need to declare, open, fetch from, or close it.
BEGIN
UPDATE employees
SET salary = salary + 1000
WHERE department_id = 10;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
/5. What is an Explicit Cursor?
An explicit cursor is a cursor that the programmer explicitly declares and manages. It is useful when a query returns multiple rows and you want to process them individually.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name
FROM employees;
BEGIN
OPEN emp_cursor;
-- Fetch/process rows here
CLOSE emp_cursor;
END;
/6. What are the steps of an Explicit Cursor?
The traditional explicit cursor lifecycle has four steps: DECLARE, OPEN, FETCH, and CLOSE.
DOFC = Declare → Open → Fetch → Close7. How do you Declare a Cursor?
Syntax:
CURSOR cursor_name IS
SELECT statement;Example:
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name
FROM employees;
BEGIN
NULL;
END;
/8. How do you Open a Cursor?
Use OPEN cursor_name; to execute the cursor query and make the result set available for fetching.
9. What is FETCH?
FETCH retrieves rows from the cursor's result set. Each fetch retrieves the next row into the specified variables.
FETCH emp_cursor
INTO v_id, v_name;10. How do you Close a Cursor?
Use CLOSE cursor_name; to release the cursor resources after processing is complete.
11. Complete Explicit Cursor Example
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name
FROM employees;
v_id employees.employee_id%TYPE;
v_name employees.first_name%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_id, v_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
END LOOP;
CLOSE emp_cursor;
END;
/DECLARE cursor
↓
OPEN cursor
↓
FETCH first row
↓
Process row
↓
FETCH next row
↓
Process row
↓
...
↓
%NOTFOUND = TRUE
↓
EXIT LOOP
↓
CLOSE cursor12. What are Cursor Attributes?
Oracle provides cursor attributes to get information about cursor execution.
For implicit cursors:
SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT
SQL%ISOPENFor explicit cursors:
cursor_name%FOUND
cursor_name%NOTFOUND
cursor_name%ROWCOUNT
cursor_name%ISOPEN13. What is %FOUND?
%FOUND tells whether the most recent fetch or SQL operation affected or found a row.
14. What is %NOTFOUND?
%NOTFOUND becomes TRUE when the most recent fetch did not retrieve a row. It is commonly used to terminate a cursor loop.
LOOP
FETCH emp_cursor INTO v_id, v_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_name);
END LOOP;15. What is %ROWCOUNT?
%ROWCOUNT tells how many rows have been fetched so far for an explicit cursor.
16. What is %ISOPEN?
%ISOPEN tells whether an explicit cursor is currently open.
17. Explicit Cursor Attributes — Summary
| Attribute | Meaning |
|---|---|
| %FOUND | Last fetch found a row |
| %NOTFOUND | Last fetch did not find a row |
| %ROWCOUNT | Number of rows fetched so far |
| %ISOPEN | Whether cursor is currently open |
18. What is an Implicit Cursor Attribute?
For implicit SQL statements, Oracle uses the SQL cursor. The attributes are SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, and SQL%ISOPEN.
19. Difference Between Implicit and Explicit Cursor
| Implicit Cursor | Explicit Cursor |
|---|---|
| Created automatically | Declared by programmer |
| Oracle manages it | Programmer manages it |
| No OPEN required | Usually explicitly opened |
| No explicit FETCH required | Explicit FETCH is possible |
| No explicit CLOSE required | Programmer can explicitly close it |
| Uses SQL%... | Uses cursor_name%... |
20. What is a Cursor FOR LOOP?
A cursor FOR loop simplifies explicit cursor processing. Oracle automatically handles opening the cursor, fetching rows, and closing the cursor.
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name
FROM employees;
BEGIN
FOR emp IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(emp.employee_id || ' - ' || emp.first_name);
END LOOP;
END;
/21. Why is Cursor FOR LOOP useful?
It makes code shorter and reduces mistakes. Instead of manually writing OPEN, FETCH, LOOP, EXIT, and CLOSE, you can use a Cursor FOR loop.
22. Cursor FOR LOOP Without Declaring a Cursor
BEGIN
FOR emp IN (
SELECT employee_id, first_name
FROM employees
)
LOOP
DBMS_OUTPUT.PUT_LINE(emp.employee_id || ' - ' || emp.first_name);
END LOOP;
END;
/23. What is a Parameterized Cursor?
A parameterized cursor accepts parameters when it is opened.
DECLARE
CURSOR emp_cursor(p_dept_id NUMBER) IS
SELECT employee_id, first_name
FROM employees
WHERE department_id = p_dept_id;
BEGIN
FOR emp IN emp_cursor(10) LOOP
DBMS_OUTPUT.PUT_LINE(emp.first_name);
END LOOP;
END;
/24. Why use Parameterized Cursors?
They allow the same cursor definition to be reused with different values.
25. What is a Nested Cursor?
A cursor can be used inside another loop or cursor-processing block. This is useful for department-by-department processing of employees.
BEGIN
FOR dept IN (
SELECT department_id
FROM departments
)
LOOP
FOR emp IN (
SELECT employee_id, first_name
FROM employees
WHERE department_id = dept.department_id
)
LOOP
DBMS_OUTPUT.PUT_LINE(emp.first_name);
END LOOP;
END LOOP;
END;
/26. What happens if you FETCH from a closed cursor?
Oracle raises a cursor-related error such as INVALID_CURSOR.
27. What happens if you OPEN an already open cursor?
Opening an already open explicit cursor causes an error such as INVALID_CURSOR.
28. What happens if you CLOSE an already closed cursor?
Attempting to close a cursor that is not open causes an INVALID_CURSOR error.
29. Can a cursor return multiple rows?
Yes. Explicit cursors are commonly used to process multiple rows sequentially.
30. Can SELECT INTO use multiple rows?
A normal SELECT INTO expects a single row. If multiple rows are returned, Oracle raises TOO_MANY_ROWS.
31. Cursor and SELECT INTO Difference
| SELECT INTO | Cursor |
|---|---|
| Generally expects one row | Can process multiple rows |
| Simple retrieval | Useful for row-by-row processing |
| Multiple rows can cause TOO_MANY_ROWS | Designed for multiple-row processing |
32. What is a REF CURSOR?
A REF CURSOR is a cursor variable that can refer to different query result sets. It is commonly used when passing query results between PL/SQL programs and applications.
33. Strong REF CURSOR vs Weak REF CURSOR
A strong REF CURSOR has a specified return type. A weak REF CURSOR does not specify a fixed return type.
34. Implicit Cursor vs Cursor FOR LOOP
Do not confuse these concepts. An implicit cursor is automatically managed by Oracle for a SQL statement, while a Cursor FOR loop automatically manages the open, fetch, and close process for row-by-row iteration.
35. Complete Cursor Example for Exam
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, first_name, salary
FROM employees;
v_id employees.employee_id%TYPE;
v_name employees.first_name%TYPE;
v_salary employees.salary%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_id, v_name, v_salary;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ' ' || v_name || ' ' || v_salary);
END LOOP;
CLOSE emp_cursor;
END;
/DECLARE
↓
Define cursor
↓
OPEN
↓
FETCH
↓
Check %NOTFOUND
↓
Process row
↓
FETCH next row
↓
CLOSE36. Frequently Asked Interview Questions
| Question | Answer |
|---|---|
| What is a cursor? | A mechanism for processing SQL query results, especially row by row. |
| What are the two main types of cursors? | Implicit and Explicit cursors. |
| Who manages an implicit cursor? | Oracle automatically manages it. |
| Who manages an explicit cursor? | The programmer controls its lifecycle. |
| What are the four steps of an explicit cursor? | DECLARE → OPEN → FETCH → CLOSE |
| Which attribute checks whether a row was found? | cursor_name%FOUND |
| Which attribute checks whether a row was not found? | cursor_name%NOTFOUND |
| Which attribute gives the number of rows fetched? | cursor_name%ROWCOUNT |
| Which attribute checks whether the cursor is open? | cursor_name%ISOPEN |
Important Exam Points
- A cursor is used to process SQL query results.
- There are two main types: Implicit and Explicit.
- Implicit cursors are automatically managed by Oracle.
- Explicit cursors are declared by the programmer.
- Traditional explicit cursor steps are DECLARE → OPEN → FETCH → CLOSE.
%FOUNDtells whether the last fetch found a row.%NOTFOUNDtells whether the last fetch did not find a row.%ROWCOUNTtells the number of rows fetched so far.%ISOPENtells whether an explicit cursor is open.- Cursor FOR loops automatically manage opening, fetching, and closing.
- Parameterized cursors accept values when used or opened.
- SELECT INTO is generally for a single-row result; cursors are suitable for multiple-row processing.
- REF CURSOR is a cursor variable used to return or reference query result sets.
Easy Memory Trick
IMPLICIT
→ Oracle manages it
EXPLICIT
→ Programmer defines it
EXPLICIT CURSOR
→ DECLARE
→ OPEN
→ FETCH
→ CLOSE
ATTRIBUTES
→ FOUND
→ NOTFOUND
→ ROWCOUNT
→ ISOPEN