D these are system events that can be used with

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: using the :NEW prefix and refer to the data already in the database using the :OLD prefix. D. These are System Events that can be used with triggers but they have no bearing on conditional predicates. WWW.ExamMagic.COM - 68: Examine this package specification: CREATE OR REPLACE PACKAGE concat_all IS v_string VARCHAR2(100); PROCEDURE combine (p_num_val NUMBER); PROCEDURE combine (p_date_val DATE); PROCEDURE combine (p_char_val VARCHAR2, p_num_val NUMBER); END concat_all; / Which overloaded COMBINE procedure declaration can be added to this package specification? A. PROCEDURE combine; B. PROCEDURE combine (p_no NUMBER); C. PROCEDURE combine (p_val_1 VARCHAR2, p_val_2 NUMBER; D. PROCEDURE concat_all (p_num_val VARCHAR2, p_char_val NUMBER); Answer: A Explanation: You use the package overloading feature when the same operation is performed using arguments of different types. Incorrect Answers B. This procedure conflicts with the first procedure declaration in that its parameters differs only by name but have the same data type. You cannot use two procedures with the same name if their parameters differ only in name or mode. C. This procedure conflicts with the third procedure declaration in that its parameters differs only by name but have the same data type. D. This procdure can't be overloaded because it does not use the same name. Package overloading allows more than one procedure or function inside the package to use the same name. WWW.ExamMagic.COM - 69: www.ExamMagic.com 1Z0-147 Local procedure A calls remote procedure B. Procedure B was compiled at 8 A.M. Procedure A was modified and recompiled at 9 A.M. Remote procedure B was later modified and recompiled at 11 A.M. The dependency mode is set to TIMESTAMP. What happens when procedure A is invoked at 1 P.M? A. There is no affect on procedure A and it runs successfully. B. Procedure B is invalidated and recompiles when invoked. C. Procedure A is invalidated and recompiles for the first time it is invoked. D. Procedure A is invalidated and recompiles for the second time it is invoked. Answer: D Explanation: When the local procedure is invoked, at run time the Oracle server compares the two time stamps of the referenced remote procedure. If the time stamps are equal (indicating that the remote procedure has not recompiled), the Oracle server executes the local procedure. If the time stamps are not equal (indicating that the remote procedure has recompiled), the Oracle server invalidates the local procedure and returns a runtime error. If the local procedure, which is now tagged as invalid, is invoked a second time, the Oracle server recompiles it before executing, in accordance with the automatic local dependency mechanism. So if a local procedure returns a run-time error the first time that it is invoked, indicating that the remote procedure's time stamp has changed, you should develop a strategy to re-invoke the local procedure. Incorrect Answers: A, B, C WWW.ExamMagic.COM - 70: Under which two circumstances do you design database triggers? (Choose two) A. To duplicate the functionality of other triggers. B. To replicate built-in constraints in the Oracle server such as primary key and foreign key. C. To guarantee that when a specific operation is performed, related actions are performed. D. For centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement. Answer: C,D www.ExamMagic.com 1Z0-147 Explanation: C. When a specific event occurs you can specify that any related code is executed by implementing a Trigger. D. Triggers allow you have control regardless of which user or which application made the change. Incorrect Answers A. You do not want to create a Trigger when a trigger already exists for the specified event. B. You should not create a Trigger to Trigger to perform functionality that is already built into the Oracle server. WWW.ExamMagic.COM - 71: Examine this procedure: CREATE OR REPLACE PROCEDURE DELETE_PLAYER (V_ID IN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE ID = V_ID; EXCEPTION WHEN STATS_EXITS_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE ('Cannot delete this player, child records exist in PLAYER_BAT_STAT table'); END; What prevents this procedure from being created successfully? A. A comma has been left after the STATS_EXIST_EXCEPTION exception. B. The STATS_EXIST_EXCEPTION has not been declared as a number. C. The STATS_EXIST_EXCEPTION has not been declared as an exception. D. Only predefined exceptions are allowed in the EXCEPTION section. Answer: C Explanation: You can't raise an exception that has not been declared. Incorrect Answers A. This is the correct syntax for raising an exception. B. The STATS_EXIST_EXCEPTION needs to be declared as an exception not as a number. www.ExamMagic.com 1Z0-147 C. You have use predefined, Non_predefined and User_Defined Exceptions in the EXCEPTION section WWW.ExamMagic.COM - 72: Examine this package: CREATE OR REPLACE PACKAGE manage_emps IS tax_rate CONSTANT NUMBER(5,2) := .28; v_id NUMBER; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER); PROCEDURE delete_emp; PROCEDURE update_emp; FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER; END manage_emps; / CREATE OR REPLACE PACKAGE BODY manage_emps IS PROCEDURE update_sal (p_raise_amt NUMBER) IS BEGIN UPDATE emp SET sal = (sal * p_raise_emt) + sal WHERE empno = v_id; END; PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER) IS BEGIN INSERT INTO emp(empno, deptno, sal) VALY...
View Full Document

Ask a homework question - tutors are online