In Oracle PL/SQL, define the four standard cursor attributes %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN, and explain what each one indicates about cursor state.

Introduction / Context:
When working with explicit cursors or cursor variables in PL/SQL, you need to know whether a cursor is open, whether the last fetch returned a row, and how many rows have been processed. Oracle provides cursor attributes for this purpose. This question focuses on the four standard attributes and what they mean in PL/SQL code.

Given Data / Assumptions:

• We are using explicit cursors or relying on implicit cursors created for SQL statements in PL/SQL.
• We want to control loops and logic based on success or failure of fetch operations or DML statements.
• Oracle defines standard attributes that can be appended to cursor names or to SQL for the implicit cursor.
• These attributes are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.

Concept / Approach:
%FOUND and %NOTFOUND are Boolean attributes that indicate whether the last fetch or DML statement affected any rows. %FOUND becomes true when a fetch successfully returns a row or when a DML statement affects at least one row, and false otherwise. %NOTFOUND is the logical opposite of %FOUND. %ROWCOUNT is a numeric attribute that reports how many rows have been processed so far by the cursor or DML statement. %ISOPEN tells you whether an explicit cursor is currently open, which helps avoid opening a cursor twice or fetching from a closed cursor.

Step-by-Step Solution:
Step 1: For the implicit cursor SQL, SQL%FOUND indicates whether the last INSERT, UPDATE, DELETE, or SELECT INTO affected or returned any rows.Step 2: SQL%NOTFOUND is true when the last DML or SELECT INTO did not affect or return any rows, which is useful for detecting missing data.Step 3: SQL%ROWCOUNT gives the number of rows affected by the last DML statement, which is commonly used for logging and validation.Step 4: For an explicit cursor cur_emp, cur_emp%ISOPEN is true if the cursor has been opened and not yet closed, and false otherwise.Step 5: These behaviours match the description in option A and allow PL/SQL code to manage cursor based loops safely.

Verification / Alternative check:
If you write a loop that fetches rows from an explicit cursor, you can use EXIT WHEN cur_emp%NOTFOUND; to terminate when no more rows are available. Observing that %ROWCOUNT increments with each fetch confirms that it tracks progress. Testing SQL%FOUND after an UPDATE shows whether any rows satisfied the WHERE clause, which verifies the semantics of the implicit cursor attributes.

Why Other Options Are Wrong:
Option B assigns completely unrelated meanings to the attributes and is incorrect. Option C states that cursor attributes are only for SQL Plus, which is false because they are part of PL/SQL. Option D misuses %ISOPEN and claims it returns error text, which is handled by exceptions instead.

Common Pitfalls:
One pitfall is checking %FOUND without performing a fetch first, which leads to unpredictable results. Another is misunderstanding the difference between implicit SQL attributes and explicit cursor attributes. Careful coding patterns, such as always fetching before checking %NOTFOUND in a loop, help avoid off by one errors and missed rows.

Final Answer:
%FOUND is true after a successful fetch or DML affecting at least one row, %NOTFOUND is the opposite, %ROWCOUNT shows how many rows have been processed so far, and %ISOPEN indicates whether the cursor is currently open.