Incorrect answers b d e these are not valid oracle

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: your schema. C. The IDEPTREE View displays indirect dependencies in an indented format. Incorrect Answers B, D & E. These are not valid Oracle Views WWW.ExamMagic.COM - 83: 1Z0-147 Examine the trigger heading: CREATE OR REPLACE TRIGGER salary_check BEFORE UPDATE OF sal, job ON emp FOR EACH ROW Under which condition does this trigger fire? A. When a row is inserted into the EMP table. B. When the value of the SAL or JOB column in a row is updated in the EMP table. C. When any column other than the SAL and JOB columns in a row are updated in the EMP table. D. Only when both values of the SAL and JOB columns in a row are updated together in the EMP table. Answer: B Explanation: The triggering event is based on an UPDATE of the SAL or the JOB column in the EMP Table. Incorrect Answers A. The triggering event is not an INSERT into the table. C. The Trigger is defined to fire when either the SAL or JOB columns are updated. The trigger will not fire if any of the other columns in the table are updated. D. The Trigger fires based on an Update of the SAL OR JOB Column . This is not an AND Operator. WWW.ExamMagic.COM - 84: You have an AFTER UPDATE row-level on the table EMP. The trigger queries the EMP table and inserts the updating user's information into the AUDIT_TABLE. What happens when the user updates rows on the EMP table? A. A compile time error occurs. B. A runtime error occurs. The effect of trigger body and the triggering statement are rolled back. C. A runtime error occurs. The effect of trigger body is rolled back, but the update on the EMP table takes place. D. The trigger fires successfully. The update on the EMP table occurs, and data is inserted into theAUDIT_TABLE table. E. A runtime error occurs. The update on the EMP table does not take place, but the insert into the AUDIT_TABLE occurs. 1Z0-147 Answer: B Explanation: This results in a mutating table. A mutating table is a table against which a data manipulation statement has a corresponding trigger on the DML statement is reading from the same table. When a trigger encounters a mutating table, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application. Incorrect Answers A. This trigger will be created without any compilation errors. The error will not occur until runtime. C. The effects of the trigger body and triggering statement are rolled back D A runtime error occurs and both are rolled back E A runtime error occurs and both are rolled back WWW.ExamMagic.COM - 85: The add_player, upd_player_stat, and upd_pitcher_stat procedures are grouped together in a package. A variable must be shared among only these procedures. Where should you declare this variable? A. In the package body. B. In a database trigger. C. In the package specification. D. In each procedure's DECLARE section, using the exact same name in each. Answer: A Explanation: A You want to declare this variable in the package body before the procedures so that it may be shared among all of the procedure. If it was declared in the package specification it would be accessible outside of the package. Incorrect Answers B. If you declared it inside a trigger it could only be used by the trigger in which it was defined. C. Declaring the variable in the package specification would make this variable public. D. The variable would be local to each procedure and this would prevent the variable from being shared. WWW.ExamMagic.COM - 86: 1Z0-147 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? A. ERROR at line 1: B. Row is: Row is: Rows is: 1Z0-147 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...
View Full Document

This note was uploaded on 01/05/2011 for the course COMS 131 taught by Professor Dad during the Spring '10 term at University of Mumbai.

Ask a homework question - tutors are online