Oracle9i program with pl/sql 認定 1Z0-147 試験問題:

1. Which three are true statements about dependent objects? (Choose three)

A) Invalid objects cannot be described.
B) The Oracle server automatically records dependencies among objects.
C) You can view whether an object is valid or invalid in the USER_OBJECTS data dictionary view.
D) All schema objects have a status that is recorded in the data dictionary.
E) You can view whether an object is valid or invalid in the USER_STATUS data dictionary view.
F) An object with status of invalid cannot be a referenced object.

2. Examine this procedure:
CREATE OR REPLACE PROCEDURE INSERT_TEAM
(V_ID in NUMBER, V_CITY in VARCHAR2 DEFAULT 'AUSTIN', V_NAME in VARCHAR2)
IS
BEGIN
INSERT INTO TEAM (id, city, name)
VALUES (v_id, v_city, v_name);
COMMIT;
END
Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)

A) EXECUTE INSERT_TEAM(3, V_NAME=>'LONGHORNS', V_CITY=>'AUSTIN');
B) EXECUTE INSERT_TEAM (V_ID := V_NAME := 'LONGHORNS', V_CITY := 'AUSTIN');
C) EXECUTE INSERT_TEAM;
D) EXECUTE INSERT_TEAM(3, 'AUSTIN','LONGHORNS');
E) EXECUTE INSERT_TEAM (3, 'LONGHORNS');

3. 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) Only predefined exceptions are allowed in the EXCEPTION section.
B) The STATS_EXIST_EXCEPTION has not been declared as a number.
C) A comma has been left after the STATS_EXIST_EXCEPTION exception.
D) The STATS_EXIST_EXCEPTION has not been declared as an exception.

4. 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 is not invalidated.
B) VALIDATE_PLAYER_STAT and BB_PACK are invalidated.
C) VALDIATE_PLAYER_STAT is invalidated.
D) VALIDATE_PLAYER_STAT cannot recompile and must be recreated.

5. Which statement about triggers is true?

A) You use a database trigger to fire when an INSERT statement occurs.
B) You use INSTEAD OF trigger to fire when a SELECT statement occurs.
C) You use a system event trigger to fire when an UPDATE statement occurs.
D) You use an application trigger to fire when a DELETE statement occurs.

質問と回答：