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 Up to the immediate present 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 Sep 1z0-147 test questions

Q51. Which two describe a stored procedure? (Choose two)

A. A stored procedure is typically written in SQL.

B. A stored procedure is a named PL/SQL block that can accept parameters.

C. A stored procedure is a type of PL/SQL subprogram that performs an action.

D. A stored procedure has three parts: the specification, the body, and the exception handler part.

E. The executable section of a stored procedure contains statements that assigns values, control execution, and return values to the calling environment.

Answer: BC


Q52. All users currently have the INSERT privilege on the PLAYER table. You only want your users to insert into this table using the ADD_PLAYTER procedure. Which two actions must you take? (Choose two)

A. GRANT SELECT ON ADD_PLAYER TO PUBLIC;

B. GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;

C. GRANT INSERT ON PLAYER TO PUBLIC;

D. GRANT EXECUTE,INSERT ON ADD_PLAYER TO PUBLIC;

E. REVOKE INSERT ON PLAYER FROM PUBLIC;

Answer: BE


Q53. You need to implement a virtual private database (vpd). In order to have the vpd functionality, a trigger is required to fire when every user initiates a session in the database. What type of trigger needs to be created?

A. DML trigger

B. System event trigger

C. INSTEAD OF trigger

D. Application trigger

Answer: B


Q54. Procedure PROCESS_EMP references the table EMP.

Procedure UPDATE_EMP updates rows if table EMP through procedure PROCESS_EMP.

There is a remote procedure QUERY_EMP that queries the EMP table through the local procedure PROCESS_EMP.

The dependency mode is set to TIMESTAMP in this session.

Which two statements are true? (Choose two)

A. If the signature of procedure PROCESS_EMP is modified and successfully recompiles, the EMP table is invalidated.

B. If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time.

C. If the signature of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time.

D. If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the first time.

E. If internal logic of procedure PROCESS_EMP is modified and successfully recompiles, QUERY_EMP gets invalidated and will recompile when invoked for the second time.

Answer: BE


Q55. How can you migrate from a LONG to a LOB data type for a column?

A. Use the DBMS_MANAGE_LOB.MIGRATE procedure.

B. Use the UTL_MANAGE_LOB.MIGRATE procedure.

C. Use the DBMS_LOB.MIGRATE procedure.

D. Use the ALTER TABLE command.

E. You cannot migrate from a LONG to a LOB date type for a column.

Answer: D


certifyforall.com

Updated 1z0-147 training:

Q56. Examine this code:

CREATE OR REPLACE PROCEDURE add_dept

( p_name departments.department_name%TYPE DEFAULT 'unknown',

p_loc departments.location_id%TYPE DEFAULT 1700)

IS

BEGIN

INSERT INTO departments(department_id, department_name,

loclation_id)

VALUES(dept_seq.NEXTVAL,p_name, p_loc);

END add_dept;

/

You created the add_dept procedure above, and you now invoke the procedure in SQL *Plus.

Which four are valid invocations? (Choose four)

A. EXECUTE add_dept(p_loc=>2500)

B. EXECUTE add_dept('Education', 2500)

C. EXECUTE add_dept('2500', p_loc =>2500)

D. EXECUTE add_dept(p_name=>'Education', 2500)

E. EXECUTE add_dept(p_loc=>2500, p_name=>'Education')

Answer: ABCE


Q57. A dependent procedure or function directly or indirectly references one or more of which four objects? (Choose four)

A. view

B. sequence

C. privilege 

D. procedure

E. anonymous block

F. packaged procedure or function

Answer: ABDF


Q58. Which code successfully calculates tax?

A. CREATE OR REPLACE PROCEDURE calctax (p_no IN NUMBER)

RETURN tax IS

v_sal NUMBER;

tax Number;

BEGIN

SELECT sal INTO v_sal

FROM emp

WHERE empno = p_no;

tax := v_sal * 0.05;

END;

B. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER)

RETURN NUMBER IS

v_sal NUMBER;

BEGIN

SELECT sal INTO v_sal

FROM emp

WHERE empno = p_no;

RETURN (v_sal * 0.05);

END;

C. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER)

RETURN NUMBER IS

v_sal NUMBER;

tax NUMBER;

BEGIN

SELECT sal INTO v_sal

FROM emp

WHERE empno = p_no;

tax := v_sal * 0.05;

END

D. CREATE OR REPLACE FUNCTION calctax (p_no NUMBER) IS

v_sal NUMBER;

tax NUMBER;

BEGIN

SELECT sal INTO v_sal

FROM emp

WHERE empno = p_no;

tax := v_sal * 0.05;

RETURN (tax);

END;

Answer: B


Q59. 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. SELECT CALC_PLAYER_AVG(PLAYER_ID)

FROM PLAYER_BAT_STAT;

B. EXECUTE CALC_PLAYER_AVG(31);

C. CALC_PLAYER('RUTH');

D. CALC_PLAYER_AVG(31);

E. START CALC_PLAYER_AVG(31)

Answer: A


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