Y is a direct dependent of x d y is an indirect

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: sed on a table Z . Which two statements are true? (Choose two.) A. Y is a referenced object B. Z is a direct dependent of X C. Y is a direct dependent of X D. Y is an indirect dependent of X E. Y is an indirect dependent of Z F. Z is an indirect dependent of Y Answer: A, C Explanation: A. Y is referenced by X. C. There is a direct dependency between Z and X. Incorrect Answers B. X is an indirect dependent of X D. Y is a Direct dependent of X. www.ExamMagic.com 1Z0-147 E. Y is a direct dependent of Z. F. Z is a direct dependent of Y. WWW.ExamMagic.COM - 105: Examine this code CREATE OR REPLACE FUNCTION change_dept (p_old_id NUMBER, p_deptname VARCHAR2) RETURN NUMBER IS v_new_id NUMBER BEGIN SELECT departments_seq.nextval INTO v_new_id FROM dual; UPDATE departments SET departmenet_id = v_new_id, Department_name = p_deptname WHERE department_id=p_old_id; Return v_new_id; End; / There are no foreign key integrity constraints on the EMPLOYEES and DEPARTMENTS tables. Which statement performs a successful update to the EMPLOYEES table? A. UPDATE departments SET department_id = change_dept(10, 'Finance') Where dapartment_id=10; B. UPDATE employees SET department_id = change_dept(10, 'Finance') Where dapartment_id=10; C. UPDATE departments change_dept(270, 'Outsource') Where dapartment_name='payroll'; D. UPDATE employees SET department_id = change_dept(10, 'Finance') WHERE department_id = DEPARTMENTS:CURRVAl; Answer: B Explanation: This statement updates the Department_id of the Employees with department_id 10 to the next sequence number. The Update Statement invokes the change_dept function in the set www.ExamMagic.com 1Z0-147 statement and passes the Current department_id & the New Department Name as input parameters. The Function gets the next Department ID from the Sequence and successfully updates the Department & Department Name based on the parameters passed to the function. Incorrect Answers A. This statement will not update the Employees Table. The Statement would attempt to update the Departments table will it would generate an error due to a mutating table. C. This Statement attempts to update the wrong table, has incorrect syntax and if corrected would result in an error due to a mutating table. D. This is not a valid sequence. You can't have a sequence with the same name as a table and if you tried to use the CURRVAL of the departments_seq.nextval sequence in the WHERE you would get the following error: WWW.ExamMagic.COM - 106: Which two statements about object dependencies are accurate? (Choose two.) A. When referencing a package procedure or function from a stand-alone procedure or function, if the package specification changes, the package body remains valid but the stand-alone procedure becomes invalid B. When referencing a package procedure or function from a stand-alone procedure or function, if the package body changes and the package specification does not change, the stand-alone procedure referencing a package construct remains valid. C. When referencing a package procedure or function from a stand-alone procedure or function, if the package body changes and the package specification does not change, the stand-alone procedure referencing a package construct becomes invalid D. When referencing a package procedure or function from a stand-alone procedure or function, If the package specification changes, the stand-alone procedure referencing a package construct as well as the package body become invalid Answer: B, D Explanation: B. One of the advantages with packages is the improved handling of dependencies. The package structure separates the program unit code (body) from the program unit header (specification). If modifications are made to the code in the package body and the package specification remains unchanged, the status of dependent objects are not changed to INVALID, as is the case with stand-alone program units. D. If you change the package specification any objects including stand-alone procedures or functions that reference the package are invalidated. When you change the Package specification the status of the package body is set to invalid. Incorrect Answers www.ExamMagic.com 1Z0-147 A. When you change the package specification the package body becomes invalid and the stand-alone procedures become invalid. C. If modifications are made to the code in the package body and the package specification remains unchanged, the status of dependent objects are not changed to INVALID. WWW.ExamMagic.COM - 107: You need to create a trigger to ensure that information in the EMP table is only modified during business hours, Monday to Friday from 9:00am to 500pm Which types of trigger do you create? (Choose two.) A. row level AFTER INSERT OR UPDATE OR DELETE ON EMP B. row level BEFORE INSERT OR UPDATE OR DELETE ON EMP C. statement level AFTER INSERT OR UPDATE OR DELETE ON EMP D. statement level BEFORE INSERT OR UPDATE OR DELETE ON EMP Answer: B, D Explanation: B. You would want to create a trigger tha...
View Full Document

Ask a homework question - tutors are online