Implicit Cursor FAQS

1. What is an implicit cursor in Oracle?

An implicit cursor is automatically created by Oracle whenever you execute a DML statement (INSERT, UPDATE, DELETE) or a SELECT INTO statement that returns only one row.

You do not declare, open, fetch, or close it manually—Oracle handles everything internally.

2. When does Oracle create an implicit cursor?

Oracle automatically creates an implicit cursor for:

• INSERT
• UPDATE
• DELETE
• MERGE
• SELECT INTO (single-row query)

Example:

DECLARE

   v_name employees.first_name%TYPE;

BEGIN

   SELECT first_name INTO v_name

   FROM employees

   WHERE employee_id = 100;

 

   DBMS_OUTPUT.PUT_LINE(v_name);

END;

Here, Oracle creates an implicit cursor for the SELECT INTO.

3. What are implicit cursor attributes?

Oracle provides SQL cursor attributes to get information about the most recently executed SQL statement.

Attribute	Description
SQL%FOUND	TRUE if at least one row was affected
SQL%NOTFOUND	TRUE if no rows were affected
SQL%ROWCOUNT	Number of rows affected
SQL%ISOPEN	Always FALSE (implicit cursors auto-close)

4. How do you use SQL%ROWCOUNT?

Example:

BEGIN

   UPDATE employees

   SET salary = salary * 1.10

   WHERE department_id = 50;

 

   DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.');

END;

If 5 rows are updated, it prints:

5 rows updated.

5. What happens if SELECT INTO returns no rows?

Oracle raises:

NO_DATA_FOUND

Example:

BEGIN

   SELECT first_name INTO v_name

   FROM employees

   WHERE employee_id = 9999;

END;

If no employee exists → NO_DATA_FOUND exception.

6. What happens if SELECT INTO returns multiple rows?

Oracle raises:

TOO_MANY_ROWS

Because implicit cursors with SELECT INTO must return exactly one row.

7. Is SQL%ISOPEN useful with implicit cursors?

No.
SQL%ISOPEN always returns FALSE because implicit cursors are automatically closed after execution.

8. Difference between implicit and explicit cursors

Feature	Implicit Cursor	Explicit Cursor
Created by	Oracle	Developer
Used for	Single-row queries & DML	Multi-row queries
Manual control	No	Yes
OPEN/FETCH/CLOSE required	No	Yes
Cursor name	Always SQL	User-defined

9. Can implicit cursors be used inside triggers?

Yes. They are commonly used in triggers for checking DML row counts.

Example:

CREATE OR REPLACE TRIGGER check_update

AFTER UPDATE ON employees

BEGIN

   IF SQL%ROWCOUNT > 10 THEN

      DBMS_OUTPUT.PUT_LINE('More than 10 rows updated');

   END IF;

END;

10. What are common mistakes with implicit cursors?

• Using SELECT instead of SELECT INTO
• Not handling NO_DATA_FOUND
• Not handling TOO_MANY_ROWS
• Assuming implicit cursors can process multiple rows (they cannot)

11. When should you use implicit cursors?

Use implicit cursors when:

• Query returns exactly one row
• Performing DML operations
• You don’t need row-by-row processing
• Simpler logic is preferred

12. Best Practices

• Always handle exceptions for SELECT INTO
• Use SQL%ROWCOUNT after DML to validate success
• Use explicit cursors for multi-row processing
• Avoid unnecessary loops for single-row queries