It is more faster and easier to pass the Oracle 1z0-147 exam by using Virtual Oracle oracle9i program with pl/sql questuins and answers. Immediate access to the Replace 1z0-147 Exam and find the same core area 1z0-147 questions with professionally verified answers, then PASS your exam with a high score now.

2016 Jul 1z0-147 brain dumps

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

V_PLAYER_AVG NUMBER84,3);

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;

VALIDATE_PLAYER_STAT(V_ID);

END UPD_PLAYER_STAT;

PROCEDURE ADD_PLAYER

(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBERI)

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;

If you add an IF statement to the ADD_PLAYER procedure which additional step must you perform?

r A Recompile the ADD PLAYER procedure

Recompile both the BB PACK specification and body

A. Recompile the ADD_PLAYER procedure

B. Recompile both the BB_PACK specification and body

C. Recompile the BB_PACK specification

D. Recompile the BB_PACK body

Answer: D


Q2. Which statement is valid when removing procedures?

A. Use a drop procedure statement to drop a standalone procedure.

B. Use a drop procedure statement to drop a procedure that is part of a package.

Then recompile the package specification.

C. Use a drop procedure statement to drop a procedure that is part of a package.

Then recompile the package body.

D. For faster removal and re-creation, do not use a drop procedure statement.

Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause.

Answer: A


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


Q4. You need to create a DML trigger. Which five pieces need to be identified? (Choose five)

A. Table

B. DML event

C. Trigger body 

D. Package body 

E. Package name 

F. Trigger name 

G. System event 

H. Trigger timing

Answer: ABCFH


Q5. 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: CD


certifyforall.com

Abreast of the times 1z0-147 practice test:

Q6. Examine the trigger heading:

CREATE OR REPLACE TRIGGER salary_check

BEFORE UPDATE OF sal, job ON emp

FOR EACH ROW

Under which condition does this trigger fire?

A. When a row is inserted into the EMP table.

B. When the value of the SAL or JOB column in a row is updated in the EMP table.

C. When any column other than the SAL and JOB columns in a row are updated in the EMP table.

D. Only when both values of the SAL and JOB columns in a row are updated together in the EMP table.

Answer: B


Q7. Which two dictionary views track dependencies? (Choose two)

A. USER_SOURCE

B. UTL_DEPTREE

C. USER_OBJECTS

D. DEPTREE_TEMPTAB

E. USER_DEPENDENCIES

F. DBA_DEPENDENT_OBJECTS

Answer: DE


Q8. Which three are valid ways to minimize dependency failure? (Choose three)

A. Querying with the SELECT * notification.

B. Declaring variables with the %TYPE attribute.

C. Specifying schema names when referencing objects.

D. Declaring records by using the %ROWTYPE attribute.

E. Specifying package.procedure notation while executing procedures.

Answer: ABD


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


Q10. 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?

A. Row

B. Statement

C. ORACLE FORM trigger

D. Before

Answer: A