Exam
: Oracle 1Z0-147
Title
: Oracle9i Program with
PL/SQL
Version : Demo
1. What can you do with the DBMS_LOB package?
A. Use the DBMS_LOB.WRITE procedure to write data to a BFILE.
B. Use the DBMS_LOB.BFILENAME function to locate an external BFILE.
C. Use the DBMS_LOB.FILEEXISTS function to find the location of a BFILE.
D. Use the DBMS_LOB.FILECLOSE procedure to close the file being accessed.
Answer: D
2. Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER,
V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID,LAST_NAME) VALUES (V_ID,
V_LAST_NAME); COMMIT; END; This procedure must invoke the UPD_BAT_STAT procedure and pass a
parameter. Which statement, when added to the above procedure, will successfully
invoke the
UPD_BAT_STAT procedure?
A. EXECUTE UPD_BAT_STAT(V_ID);
B. UPD_BAT_STAT(V_ID);
C. RUN UPD_BAT_STAT(V_ID);
D. START UPD_BAT_STAT(V_ID);
Answer: B
3. Which three describe a stored procedure? (Choose three.)
A. A stored procedure is typically written in SQL.
B. By default, a stored procedure executes with the privileges of its owner.
C. A stored procedure has three parts: the specification, the body, and the exception handler part .
D. A stored procedure is stored in the database and can be shared by a number of programs.
E. A stored procedure offers some advantages over a standalone SQL statement, such as programmable
functionality and compiled code.
Answer: BDE
4. 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_prodid 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 S