Examine this package:
CREATE OR REPLACE PACKAGE pack_cur
IS
CURSOR c1 IS
SELECT prodid
FROM product
ORDER BY prodid DESC;
PROCEDURE proc1;
PROCEDURE proc2;
END pack_cur;
/
CREATE OR REPLACE PACKAGE BODY pack_cur
IS
v_prodif NUMBER;
PROCEDURE proc1 IS
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_prodid;
DBMS_OUTPUT.PUT_LINE(‘Row is: ‘ || c1%ROWCOUNT);
EXIT WHEN c1%ROWCOUNT >= 3;
END LOOP;
END proc1;
PROCEDURE proc2 IS
BEGIN
LOOP
FETCH c1 INTO v_prodid;
DBMS_OUTPUT.PUT_LINE(‘Row is: ‘ ||c1%ROWCOUNT);
EXIT WHEN c1%ROWCOUNT >= 6;
END LOOP;
CLOSE c1;
END proc2;
END pack_cur;
/
The product table has more than 1000 rows. The SQL*Plus SERVEROUTPUT setting is turned on
in your session.
You execute the procedure PROC1 from SQL *Plus with the command:
EXECUTE pack_cur.PROC1;
You then execute the procedure PROC2 from SQL *Plus with the command:
EXECUTE pack_cur.PROC2;
What is the output in your session from the PROC2 procedure?
A.
ERROR at line 1:
B.
Row is:
Row is:
Rows is:
C.
Row is: 1
Row is: 2
Row is: 3
D.
Row is: 4
Row is: 5
Row is: 6
Explanation:
In the above example, the first procedure is used to fetch the first three rows, and the second
procedure is used to fetch the next three rows from the product table. cursors declared in the
package specification are persistent and retain their status across the user session. The persistent
state of the cursor refers to the rule that you can open a cursor in one block, fetch the rows in
another block, and close the cursor in yet another block.
Incorrect Answers:
A: This would execute successfully and would not generate an error.
B: The DBMS_OUTPUT.PUT_LINE will include the value of the ROWCOUNT of the cursor.
C: The ROWCOUNT is 3 when PROC1 finishes executing. Since the cursor state is persistent
when the PCOC2 procedure executes the next row fetched on ROW 4. The LOOP exits when
after it fetches the 6th Row.