Oracle 1Z0-147 Value Pack (Frequently Bought Together)

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. The stand alone function CALC_HEIGHT cannot be created because its name is used in a packaged function.
• B. If you remove the package body, then the package specification is removed.
• C. If you remove the stand alone stored function CALC_HEIGHT, then the METRIC_CONVERTER package body and the package specification are removed.
• D. If you remove the package specification, then the package body and the stand alone stored function CALC_HEIGHT are removed.
• E. If you remove the package specification, then the package body is removed.
• F. If you remove the package body, then the package specification and the stand alone stored function CALC_HEIGHT are removed.

Examine the trigger:
CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Emp_tab
FOR EACH ROW
DELCARE
n INTEGER;
BEGIN
SELECT COUNT(*)
INTO n
FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE(' There are now ' || a ||
' employees,');
END;
This trigger results in an error after this SQL statement is entered:
DELETE FROM Emp_tab WHERE Empno = 7499;
How do you correct the error?

• A. Change the trigger to a statement-level trigger by removing FOR EACH ROW.
• B. Change the trigger type to a BEFORE DELETE.
• C. Take out the COUNT function because it is not allowed in a trigger.
• D. Remove the DBMS_OUTPUT statement because it is not allowed in a trigger.

Examine this function:
CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID in PLAYER_BAT_STAT.PLAYER_ID%TYPE) RETURN NUMBER IS V_AVG NUMBER; BEGIN SELECT HITS / AT_BATS INTO V_AVG FROM PLAYER_BAT_STAT WHERE PLAYER_ID = V_ID; RETURN (V_AVG); END; Which statement will successfully invoke this function in SQL *Plus?

• A. START CALC_PLAYER_AVG(31)
• B. CALC_PLAYER('RUTH');
• C. EXECUTE CALC_PLAYER_AVG(31);
• D. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;
• E. CALC_PLAYER_AVG(31);

Examine this package:
CREATE OR REPLACE PACKAGE BB_PACK IS
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY
NUMBER);
END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK
IS
PROCEDURE UPD_PLAYER_STAT
(V_ID IN NUMBER, V_AB IN NUMBER DEFAULT 4, V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS = AT_BATS + V_AB,
HITS = HITS + V_HITS
WHERE PLAYER_ID = V_ID;
COMMIT;
END UPD_PLAYER_STAT;
PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER)
IS
BEGIN
INSERT INTO PLAYER(ID,LAST_NAME,SALARY)
VALUES (V_ID, V_LAST_NAME, V_SALARY);
UPD_PLAYER_STAT(V_ID,0,0);
END ADD_PLAYER;
END BB_PACK;
You make a change to the body of the BB_PACK package. The BB_PACK body is recompiled.
What happens if the stand alone procedure VALIDATE_PLAYER_STAT references this package?

• A. VALIDATE_PLAYER_STAT cannot recompile and must be recreated.
• B. VALIDATE_PLAYER_STAT and BB_PACK are invalidated.
• C. VALIDATE_PLAYER_STAT is not invalidated.
• D. VALDIATE_PLAYER_STAT is invalidated.

Why do you use an INSTEAD OF trigger?

• A. To modify data in which the DML statement has been issued against an inherently nonupdateable view.
• B. To insert into an audit table when data is updated in a sensitive column.
• C. To insert data into a view that normally does not accept inserts.
• D. To perform clean up actions when ending a user session.