QUESTION 1 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;
/
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 calc_weight;
/
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.
F. The stand alone function CALC_HEIGHT cannot be created because its name is used in a packaged function.
Answer: C
QUESTION 2
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.
Answer: C
QUESTION 3
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
QUESTION 4
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 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
QUESTION 5
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
QUESTION 6 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
QUESTION 7
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) VALUES (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; FUNCTION calc_tax
END
IF; update_sal (v_raise) ;
END update_emp ; (p_sal NUMBER) RETURN NUMBER IS
BEGIN RETURN p_sal * tax_rate;
END cale_tax;
END manage_emps;
/
How many public procedures are in the MANAGE_EMPS package?
A. One
B. Two
C. Three
D. Four
E. Five
Answer: C
QUESTION 8 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
C. GRANT EXECUTE ON TEAM TO PUBLIC
D. GRANT SELECT, EXECUTE ON TEAM, UPD_TEAM_STAT TO PUBLIC;
Answer: A
QUESTION 9
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)
A. The syntax of the UPDATE statement is incorrect.
B. You cannot update a table using a stored procedure.
C. The format parameter p_bonus_value is declared but is not used anywhere.
D. The formal parameter p_cutoff cannot have a DEFAULT clause.
E. The declaration of the format parameter p_margin cannot have a DEFAULT clause.
F. The declaration of the format parameter p_bonus_percent cannot have a DEFAULT clause.
Answer: E, F
QUESTION 10
Which three statements are true regarding database triggers? (Choose three)
A. A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view, schema, or the database.
B. A database trigger needs to be executed explicitly whenever a particular event takes place.
C. A database trigger executes implicitly whenever a particular event takes place.
D. A database trigger fires whenever a data event (such as DML) or system event (such as logon, shutdown) occurs on a schema or database.
E. With a schema, triggers fire for each event for all users; with a database, triggers fire for each event for that specific user.
Answer: A, C, D
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;
/
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 calc_weight;
/
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.
F. The stand alone function CALC_HEIGHT cannot be created because its name is used in a packaged function.
Answer: C
QUESTION 2
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.
Answer: C
QUESTION 3
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
QUESTION 4
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 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
QUESTION 5
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
QUESTION 6 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
QUESTION 7
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) VALUES (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; FUNCTION calc_tax
END
IF; update_sal (v_raise) ;
END update_emp ; (p_sal NUMBER) RETURN NUMBER IS
BEGIN RETURN p_sal * tax_rate;
END cale_tax;
END manage_emps;
/
How many public procedures are in the MANAGE_EMPS package?
A. One
B. Two
C. Three
D. Four
E. Five
Answer: C
QUESTION 8 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
C. GRANT EXECUTE ON TEAM TO PUBLIC
D. GRANT SELECT, EXECUTE ON TEAM, UPD_TEAM_STAT TO PUBLIC;
Answer: A
QUESTION 9
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)
A. The syntax of the UPDATE statement is incorrect.
B. You cannot update a table using a stored procedure.
C. The format parameter p_bonus_value is declared but is not used anywhere.
D. The formal parameter p_cutoff cannot have a DEFAULT clause.
E. The declaration of the format parameter p_margin cannot have a DEFAULT clause.
F. The declaration of the format parameter p_bonus_percent cannot have a DEFAULT clause.
Answer: E, F
QUESTION 10
Which three statements are true regarding database triggers? (Choose three)
A. A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view, schema, or the database.
B. A database trigger needs to be executed explicitly whenever a particular event takes place.
C. A database trigger executes implicitly whenever a particular event takes place.
D. A database trigger fires whenever a data event (such as DML) or system event (such as logon, shutdown) occurs on a schema or database.
E. With a schema, triggers fire for each event for all users; with a database, triggers fire for each event for that specific user.
Answer: A, C, D
