QUESTION 58 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);

QUESTION 54 This statement fails when executed: CREATE OR REPLACE TRIGGER CALC_TEAM_AVG AFTER INSERT ON PLAYER BEGIN INSERT INTO PLAYER_BATSTAT (PLAYER_ID, SEASON_YEAR,AT_BATS,HITS) VALUES (:NEW.ID, 1997, 0,0); END; To which type must you convert the trigger to correct the error? short (A) Row (B) Statement (C) ORACLE FORM trigger (D) Before

QUESTION 55 Examine this code: CREATE OR REPLACE PROCEDURE audit_emp (p_id IN emp_empno%TYPE) IS v_id NUMBER; PROCEDURE log_exec IS BEGIN INSERT INTO log_table (user_id, log_delete) VALUES (USER, SYSDATE); END log_exec; v_name VARCHAR2(20); BEGIN DELETE FROM emp WHERE empno = p_id; log_exec; SELECT ename, empno INTO v_name, v_id FROM emp WHERE empno = p_id; END audit_emp; Why does this code cause an error when compiled? (A) An insert statement is not allowed in a subprogram declaration. (B) Procedure LOG_EXEC should be declared before any identifiers. (C) Variable v_name should be declared before declaring the LOG_EXEC procedure. (D) The LOG_EXEC procedure should be invoked as EXECUTE log_exec with the AUDIT_EMP procedure.

QUESTION 56 Examine this code: CREATE OR REPLACE PACKAGE metric_converter IS c_height CONSTRAINT NUMBER := 2.54; c_weight CONSTRAINT NUMBER := .454; FUNCTION calc_height (p_height_in_inches NUMBER) RETURN NUMBER; FUNCTION calc_weight (p_weight_in_pounds NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY metric_converter IS FUNCTION calc_height (p_height_in_inches NUMBER) RETURN NUMBER IS BEGIN RETURN p_height_in_inches * c_height; END calc_height; FUNCTION calc_weight (p_weight_in_pounds NUMBER) RETURN NUMBER IS BEGIN RETURN p_weight_in_pounds * c_weight END calc_weight END metric_converter; / CREATE OR REPLACE FUNCTION calc_height (p_height_in_inches NUMBER) RETURN NUMBER IS BEGIN RETURN p_height_in_inches * metric_converter.c_height; END calc_height; / Which statement is true? 中間 (A) If you remove the package specification, then the package body and the stand alone stored function CALC_HEIGHT are removed. (B) If you remove the package body, then the package specification and the stand alone stored function CALC_HEIGHT are removed. (C) If you remove the package specification, then the package body is removed. (D) If you remove the package body, then the package specification is removed. (E) If you remove the stand alone stored function CALC_HEIGHT, then the METRIC_CONVERTER package body and the package specification are removed. (AB). The stand alone function CALC_HEIGHT cannot be created because its name is used in a packaged function.

QUESTION 57 What is a condition predicate in a DML trigger? (A) A conditional predicate allows you to specify a WHEN-LOGGING-ON condition in the trigger body. (B) A conditional predicate means you use the NEW and OLD qualifiers in the trigger body as a condition. (C) A conditional predicate allows you to combine several DBM triggering events into one in the trigger body. (D) A conditional predicate allows you to specify a SHUTDOWN or STARTUP condition in the trigger body.

QUESTION 59 Local procedure A calls remote procedure B.Procedure B was compiled at 8 A.M. Procedure A was modified and recompiled at 9A.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? Long (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.

複選題QUESTION 60 Under which two circumstances do you design database triggers? (Choose two) to for (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.

QUESTION 61 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? L (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.

QUESTION 62 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) VALYES(v_id, p_depntno, p_sal); END insert_emp; PROCEDURE delete_emp IS BEGIN DELETE FROM emp WHERE empno = v_id; END delete_emp; PROCEDURE update_emp IS v_sal NUMBER(10, 2); v_raise NUMBER(10, 2); BEGIN SELECT sal INTO v_sal FROM emp WHERE empno = v_id; IF v_sal < 500 THEN v_raise := .05; ELSIP v_sal < 1000 THEN v_raise := .07; ELSE v_raise := .04; END IF; update_sal(v_raise); END update_emp; FUNCTION calc_tax (p_sal NUMBER) RETURN NUMBER IS BEGIN RETURN p_sal * tax_rate; END calc_tax; END manage_emps; / How many public procedures are in the MANAGE_EMPS package? (A) One (B) Two (C) Three (D) Four (E) Five

QUESTION 63 Which command must you issue to allow users to access the UPD_TEAM_STAT trigger on the TEAM table? (A) GRANT SELECT, INSERT, UPDATE, DELETE ON TEAM TO PUBLIC; (B) GRANT SELECT,INSERT,UPDATE,DELETE ON UPD_TEAM_STAT TO PUBLIC; (C) GRANT EXECUTE ON TEAM TO PUBLIC (D) GRANT SELECT, EXECUTE ON TEAM, UPD_TEAM_STAT TO PUBLIC;

QUESTION 64 Examine this code: CREATE OR REPLACE PROCEDURE set_bonus (p_cutoff IN VARCHAR2 DEFAULT 'WEEKLY' p_employee_id IN employees_employee_id%TYPE p_salary IN employees_salary%TYPE, p_bonus_percent IN OUT NUMBER DEFAULT 1.5, p_margin OUT NUMBER DEFAULT 2, p_bonus_value OUT NUMBER) IS BEGIN UPDATE emp_bonus SET bonus_amount =(p_salary * p_bonus_percent)/p_margin WHERE employee_id = p_employee_id; END set_bonus; / You execute the CREATE PROCEDURE statement above and notice that it fails. What are two reasons why it fails? (Choose two) Is d~~ 1. The syntax of the UPDATE statement is incorrect. 2. You cannot update a table using a stored procedure. 3. The format parameter p_bonus_value is declared but is not used anywhere. 4. The formal parameter p_cutoff cannot have a DEFAULT clause. 5. The declaration of the format parameter p_margin cannot have a DEFAULT clause. 6. The declaration of the format parameter p_bonus_percent cannot have a DEFAULT clause. (A)12(B)23(C)34(D)56