Question 86 examine this package create or replace

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: able would be local to each procedure and this would prevent the variable from being shared. QUESTION 86: 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: ' ll c1 %ROWCOUNT); EXIT WHEN C1%ROWCOUNT >= 3; END LOOP; END Procl; / 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? - The Power of Knowing 1Z0-147 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 Answer: D 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...
View Full Document

This document was uploaded on 03/08/2014.

Ask a homework question - tutors are online