The Oracle specialists have the possibility to become a professional in IT industry should also reach the maximum of capacity as well as accomplishment, so they have to participate in diverse Oracle accreditation tests. Oracle 1z0-147 known as oracle9i program with pl/sql is stepping-stone to check the actual candidates knowledge as well as capability in a relevant industry of work. Actualtests Oracle 1z0-147 on the web exercise assessments can promise that you can to control these highly trained as well as competent functions. Our own 1z0-147 exercise examination provides the genuine answers and questions, guaranteeing you move the true 1z0-147 examination along with best grades.

2016 Jun 1z0-147 free exam questions

Q61. 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;

B. EXECUTE INSERT_TEAM(3, V_NAME=>'LONGHORNS', V_CITY=>'AUSTIN');

C. EXECUTE INSERT_TEAM(3, 'AUSTIN','LONGHORNS');

D. EXECUTE INSERT_TEAM (V_ID := V_NAME := 'LONGHORNS', V_CITY := 'AUSTIN');

E. EXECUTE INSERT_TEAM (3, 'LONGHORNS');

Answer: BC


Q62. Examine this code:

CREATE OR REPLACE PACKAGE comm_package

IS

g_comm NUMBER := 10;

PROCEDURE reset_comm(p_comm IN NUMBER);

END comm_package;

/

User Jones executes the following code at 9:01am:

EXECUTE comm_package.g_comm := 15

User Smith executes the following code at 9:05am:

EXECUTE comm_paclage.g_comm := 20

Which statement is true?

A. g_comm has a value of 15 at 9:06am for Smith.

B. g_comm has a value of 15 at 9:06am for Jones.

C. g_comm has a value of 20 at 9:06am for both Jones and Smith.

D. g_comm has a value of 15 at 9:03 am for both Jones and Smith.

E. g_comm has a value of 10 at 9:06am for both Jones and Smith.

F. g_comm has a value of 10 at 9:03am for both Jones and Smith

Answer: B


Q63. 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 it needs to be a row level AFTER UPDATE trigger.

C. The trigger fails because a SELECT statement on the table being updated is not allowed.

D. The trigger fails because you cannot use the minimum and maximum functions in a BEFORE UPDATE trigger.

Answer: C


Q64. Examine this code:

CREATE OR REPLACE FUNCTION gen_email_name

(p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER)

RETURN VARCHAR2

IS

v_email_name VARCHAR2(19=;

BEGIN

v_email_name := SUBSTR(p_first_name, 1, 1) ||

SUBSTR(p_last_name, 1, 7) ||

'@Oracle.com'

UPDATE employees

SET email = v_email_name

WHERE employee_id = p_id;

RETURN v_email_name;

END;

Which statement removes the function?

A. DROP FUNCTION gen_email_name;

B. REMOVE gen_email_name;

C. DELETE gen_email_name;

D. TRUNCATE gen_email_name;

E. ALTER FUNCTION gen_email_name REMOVE;

Answer: A


Q65. You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE.What type of trigger do you create?

A. FOR EACH ROW trigger on the EMP table.

B. Statement-level trigger on the EMP table.

C. FOR EACH ROW trigger on the AUDIT_TABLE table.

D. Statement-level trigger on the AUDIT_TABLE table.

E. FOR EACH ROW statement-level trigger on the EMPtable.

Answer: A


certifyforall.com

Leading 1z0-047 practice test:

Q66. The add_player, upd_player_stat, and upd_pitcher_stat procedures are grouped together in a package. A variable must be shared among only these procedures.

Where should you declare this variable?

A. In the package body.

B. In a database trigger.

C. In the package specification.

D. In each procedure's DECLARE section, using the exact same name in each.

Answer: A


Q67. Examine this procedure:

CREATE OR REPLACE PROCEDURE ADD_PLAYER

(V_ID IN NUMBER, V_LAST_NAME VARCHAR2)

IS

BEGIN

INSERT INTO PLAYER (ID,LAST_NAME)

VALUES (V_ID, V_LAST_NAME);

COMMIT;

END;

This procedure must invoke the APD_BAT_STAT procedure and pass a parameter.

Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?

A. EXECUTE UPD_BAT_STAT(V_ID);

B. UPD_BAT_STAT(V_ID);

C. RUN UPD_BAT_STAT(V_ID);

D. START UPD_BAT_STAT(V_ID);

Answer: B


Q68. Which table should you query to determine when your procedure was last compiled?

A. USER_PROCEDURES

B. USER_PROCS

C. USER_OBJECTS

D. USER_PLSQL_UNITS

Answer: C


Q69. 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 referenced object

B. Z is a direct dependent of X

C. Y is a direct dependent of X

D. Y is an indirect dependent of X

E. Y is an indirect dependent of Z

F. Z is an indirect dependent of Y

Answer: AC


Q70. Examine this code:

CREATE OR REPLACE PROCEDURE set_bonus

(p_cutoff IN VARCHAR2 DEFAULT 'WEEKLY'

p_employee_id IN employees_employee_id%TYPE

p_salary IN employees_salary%TYPE,

p_bonus_percent IN OUT NUMBER DEFAULT 1.5,

p_margin OUT NUMBER DEFAULT 2,

p_bonus_value OUT NUMBER)

IS

BEGIN

UPDATE emp_bonus

SET bonus_amount =(p_salary * p_bonus_percent)/p_margin

WHERE employee_id = p_employee_id;

END set_bonus;

/

You execute the CREATE PROCEDURE statement above and notice that it fails. What are two reasons why it fails? (Choose two)

A. The syntax of the UPDATE statement is incorrect.

B. You cannot update a table using a stored procedure.

C. The format parameter p_bonus_value is declared but is not used anywhere.

D. The formal parameter p_cutoff cannot have a DEFAULT clause.

E. The declaration of the format parameter p_margin cannot have a DEFAULT clause.

F. The declaration of the format parameter p_bonus_percent cannot have a DEFAULT clause.

Answer: EF