QUESTION 80 Which code successfully calculates tax?
(A) CREATE OR REPLACE PROCEDURE calctax (p_no IN NUMBER) RETURN tax IS v_sal NUMBER; tax Number; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; tax := v_sal * 0.05; END;
(B) CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) RETURN NUMBER IS v_sal NUMBER BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; RETURN:= v_sal * 0.05; end
(C) CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) RETURN NUMBER IS v_sal NUMBER; tax NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; tax := v_sal * 0.05; END
(D) CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) IS v_sal NUMBER; tax NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = p_no; tax := v_sal * 0.05; return (tax); END;

QUESTION 76 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: (C) Row is: 1 Row is: 2 Row is: 3 (D) Row is: 4 Row is: 5 Row is: 6

QUESTION 77 You disabled all triggers on the EMPLOYEES table to perform a data load. Now, you need to enable all triggers on the EMPLOYEES table. Which command accomplished this? (A) You cannot enable multiple triggers on a table in one command. (B) ALTER TRIGGERS ON TABLE employees ENABLE; (C) ALTER employees ENABLE ALL TRIGGERS; (D) ALTER TABLE employees ENABLE ALL TRIGGERS;

QUESTION 78 When creating stored procedures and functions, which construct allows you to transfer values to and from the calling environment? (A) local variables (B) arguments (C) Boolean variables (D) Substitution variables

QUESTION 79 You have the following table: CREATE TABLE Emp_log ( Emp_id NUMBER Log_date DATE, New_salary NUMBER, Action VARCHAR (20)); You have the following data in the EMPLOYEES table: EMPLOYEE_ID LAST_NAME SALARY DEPARTMENT_ID ----------- ------------------- ------------ ------------- 100 Bill 24000 90 101 Kochhar 17000 90 102 De Haan 17000 90 103 Hunold 9000 60 104 Ernst 6000 60 105 Austin 4800 60 106 Pataballa 4800 60 107 Lorentz 4200 60 108 Greenberg 12000 100 201 Hartstein 13000 20 202 Fay 6000 20 You create this trigger: CREATE OR REPLACE TRIGGER Log_salary_increase AFTER UPDATE ON employees FOR EACH ROW WHEN (new.Salary > 1000) BEGIN INSERT INTO Emp_log (Emp_id, Log_date, New_Salary, Action) VALUES (:new.Employee_id, SYSDATE, :new.SALary, 'NEW SAL'); END / Then, you enter the following SQL statement: UPDATE Employee SET Salary = Salary + 1000.0 Where Department_id = 20M What are the result in the EMP_LOG table? A EMP_ID LOG_DATE NEW_SALARY ACTION ---------- -------- ---------- ---------- 201 24-SEP-02 13000 NEW SAL 202 24-SEP-02 600 NEW SAL (B) EMP_ID LOG_DATE NEW_SALARY ACTION ---------- -------- ---------- ---------- 201 24-SEP-02 14000 NEW SAL 202 24-SEP-02 7000 NEW SAL (C) EMP_ID LOG_DATE NEW_SALARY ACTION ---------- -------- ---------- ---------- 201 24-SEP-02 NEW SAL 202 24-SEP-02 NEW SAL (D) No rows are inserted.

QUESTION 81 Examine this code: CREATE OR REPLACE FUNCTION gen_email_name (p_first VARCHAR2, p_last VARCHAR2) RETURN VARCHAR2 IS v_email_name VARCHAR (19) ; BEGIN v_email_bame := SUBSTR(p_first, 1, 1) || SUBSRE(p_last, 1, 7) || RETURN v_email_name; END / Which two statements are true? can will 1. This function is invalid. 2. This function can be used against any table. 3. This function cannot be used in a SELECT statement. 4. This function can be used only if the two parameters passed in are not bull values. 5. This function will generate a string based on 2 character values passed into the function. 6. This function can be used only on tables where there is a p_first and p_last column. (A)12(B)34(C)45(D)56

QUESTION 82 Examine the code examples. Which one is correct? (A) CREATE OR REPLACE TRIGGER authorize_action BEFORE INSERT ON EMPLOYEES CALL log_exectution; / (B) CREATE OR REPLACE TRIGGER authorize_action BEFORE INSERT ON EMPLOYEES CALL log_exectution / (C) CREATE OR REPLACE TRIGGER authorize_action BEFORE EMPLOYEES INSERT CALL log_exectution; (D) CREATE OR REPLACE TRIGGER authorize_action CALL log_exectution; BEFORE INSERT ON EMPLOYEES; /

QUESTION 83 You need to create a DML trigger. Which five pieces need to be identified? 1. Table 2. DML event 3. Trigger body 4. Package body 5. Package name 6. Trigger name 7. System event 8. Trigger timing (A)13567(B)23456(C)25678(D)12368

複選題QUESTION 84 Procedure PROCESS_EMP references the table EMP. Procedure UPDATE_EMP updates rows if table EMP through procedure PROCESS_EMP. There is a remote procedure QUERY_EMP that queries the EMP table through the local procedure PROCESS_EMP. The dependency mode is set to TIMESTAMP in this session. Which two statements are true? (Choose two) BE (A) If the signature of procedure PROCESS_EMP is modified and successfully recompiles, the EMP table is invalidated. (B) If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time. (C) If the signature of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time. (D) If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the first time. (E) If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the second time.

QUESTION 85 Examine this package: CREATE OR REPLACE PACKAGE pack_cur IS CURSOR c1 IS SELECT prodid FROM poduct 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%ROWCONT >= 3; END LOOP; END procl; PROCEDURE proc2 IS BEGIN LOOP FETCH C1 INTO v_prodid; DBMS_OUTPUT.PUT_LINE ( 'Row is: '| | c1%ROWCOUNT); EXIT WHEN c1%ROWCONT >= 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 What is the output in your session? (A) ERROR at line 1: (B) Row is: Row is: Row is: (C) Row is: 1 Row is: 2 Row is: 3 (D) Row is: 4 Row is: 5 Row is: 6

QUESTION 86 The add_player procedure inserts rows into the PLAYER table. Which command will show this directory dependency? (A) SELECT * FROM USER_DEPENDENCIES WHERE REFERENCD NAME = ' PLAYER ' ; (B) SELECT * FROM USER DEPENDENCIES WHERE REFERENCD NAME = ' ADD PLAYER ' ; (C) SELECT * FROM USER_DEPENDENCIES WHERE TYPE = 'DIR' ; (D) SELECT * FROM USER DEPENDENCIES WHERE REFERENCD NAME = ' TABLE ' ;