Prepare with Oracle 1Z0-147 exam torrent, pass for sure

Oracle 1Z0-147 Exam Overview:

Oracle 1Z0-147 Exam Syllabus Topics:

Oracle9i program with pl/sql Sample Questions:

1. 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.

2. What is a condition predicate in a DML trigger?

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

3. Consider this scenario
A procedure X references a view Y that is based on a table Z .
Which two statements are true? (Choose two.)

A) Y is a direct dependent of X
B) Y is a referenced object
C) Y is an indirect dependent of Z
D) Z is an indirect dependent of Y
E) Z is a direct dependent of X
F) Y is an indirect dependent of X

4. You have a row level BEFORE UPDATE trigger on the EMP table. This trigger contains a SELECT statement on the EMP table to ensure that the new salary value falls within the minimum and maximum salary for a given job title.
What happens when you try to update a salary value in the EMP table?

A) The trigger fires successfully.
B) The trigger fails because a SELECT statement on the table being updated is not allowed.
C) The trigger fails because it needs to be a row level AFTER UPDATE trigger.
D) The trigger fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger.

5. Examine this code:
CREATE OR REPLACE PROCEDURE insert_dept
(p_location_id NUMBER)
IS
v_dept_id NUMBER(4);
BEGIN
INSERT INTO departments
VALUES (5, 'Education', 150, p_location_id);
SELECT department_id
INTO v_dept_id
FROM employees
WHERE employee_id=99999;
END insert_dept;
/
CREATE OR REPLACE PROCEDURE insert_location
( p_location_id NUMBER,
p_city VARCHAR2)
IS
BEGIN
INSERT INTO locations(location_id, city)
VALUES (p_location_id, p_city);
insert_dept(p_location_id);
END insert_location;
/
You just created the departments, the locations, and the employees table. You did not insert any
rows. Next you created both procedures.
You new invoke the insert_location procedure using the following command:
EXECUTE insert_location (19, 'San Francisco')
What is the result in thisEXECUTE command?

A) The locations table and the departments table both have one row. The employees table is empty.
B) The departments table has one row.
The locations and the employees tables are empty.
C) The locations, departments, and employees tables are empty.
D) The location table has one row.
The departments and the employees tables are empty.

Solutions: