This would execute successfully and would not

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: 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. WWW.ExamMagic.COM - 87: 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; Answer: D Explanation: To enable or disable a single Trigger you issue the following Command at the Trigger Level: ALTER TRIGGER TriggerName ENABLE|DISABLE TriggerName; www.ExamMagic.com 1Z0-147 WWW.ExamMagi c.COM - 88: 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 Answer: B Explanation: Arguments declared in the parameter list of the subprogram are called formal parameters. The MODE determines whether the values can be transferred to the calling environment OUT & IN OUT or from the calling Environment IN & IN OUT. Incorrect Answers A, C & D These variables can't be used to transfer values to & from the calling environment. WWW.ExamMagic.COM - 89: 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 www.ExamMagic.com 1Z0-147 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. Answer: B Explanation: Employees with a Department Code = 20 are updated in the SQL Statement and 1000 is added to there existing salary. Employee Hartstein has a salary of 1300 and Employee Fay has a salary of 6000 before the update. The Trigger inserts a record for each row into the Emp_log table. The trigger specifies to insert the new value of the salary therefore the Salary values inserted will be 14000 for Hartstein & 700 for Fay. Answer A is incorrect the Trigger specifies to insert the new values. If the Trigger specified the: old.salary this would be correct Answer C omits the value f the: new.Salary www.ExamMagic.com 1Z0-147 Answer D is wrong. This trigger will fire and since it is a row-level trigger, it will insert a record into the Emp_log table for each record updated. WWW.ExamMagic.COM - 90: 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; www.ExamMagic.com 1Z0-147 return (tax); END; Answer: B Explanation: Incorrect Answers A. A RETURN clause is only valid in a function C. Missing a RETURN clause in the executable section D. The header portion of the function is missing the RETURN keyword. WWW.ExamMagic.COM - 91: 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(...
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