Exam Code: 1z0-147 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: oracle9i program with pl/sql
Certification Provider: Oracle
Free Today! Guaranteed Training- Pass 1z0-147 Exam.

2021 Jun 1z0-147 Study Guide Questions:

Q31. Examine this package specification:

CREATE OR REPLACE PACKAGE concat_all

IS

v_string VARCHAR2(100);

PROCEDURE combine (p_num_val NUMBER);

PROCEDURE combine (p_date_val DATE);

PROCEDURE combine (p_char_val VARCHAR2, p_num_val NUMBER);

END concat_all;

/

Which overloaded COMBINE procedure declaration can be added to this package specification?

A. PROCEDURE combine;

B. PROCEDURE combine (p_no NUMBER);

C. PROCEDURE combine (p_val_1 VARCHAR2, p_val_2 NUMBER;

D. PROCEDURE concat_all (p_num_val VARCHAR2, p_char_val NUMBER);

Answer: A


Q32. Which statements are true? (Choose all that apply)

A. If errors occur during the compilation of a trigger, the trigger is still created.

B. If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_TRIGGERS data dictionary view to see the compilation errors.

C. If errors occur during the compilation of a trigger you can use the SHOW ERRORS command within iSQL *Plus to see the compilation errors.

D. If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_ERRORS data dictionary view to see compilation errors.

Answer: ACD


Q33. Examine this package:

CREATE OR REPLACE PACKAGE manage_emps

IS

tax_rate CONSTANT NUMBER(5,2) := .28;

v_id NUMBER;

PROCEDURE insert_emp (p_deptno NUMBER, p_sal NUMBER);

PROCEDURE delete_emp;

PROCEDURE update_emp;

FUNCTION calc_tax (p_sal NUMBER)

RETURN NUMBER;

END manage_emps;

/

CREATE OR REPLACE PACKAGE BODY manage_emps

IS

PROCEDURE update_sal

(p_raise_amt NUMBER)

IS

BEGIN

UPDATE emp

SET sal = (sal * p_raise_emt) + sal

WHERE empno = v_id;

END;

PROCEDURE insert_emp

(p_deptno NUMBER, p_sal NUMBER)

IS

BEGIN

INSERT INTO emp(empno, deptno, sal)

VALYES(v_id, p_depntno, p_sal);

END insert_emp;

PROCEDURE delete_emp

IS

BEGIN

DELETE FROM emp

WHERE empno = v_id;

END delete_emp;

PROCEDURE update_emp

IS

v_sal NUMBER(10, 2);

v_raise NUMBER(10, 2);

BEGIN

SELECT sal

INTO v_sal

FROM emp

WHERE empno = v_id;

IF v_sal < 500 THEN

v_raise := .05;

ELSIP v_sal < 1000 THEN

v_raise := .07;

ELSE

v_raise := .04; END

IF;

update_sal(v_raise);

END update_emp;

FUNCTION calc_tax

(p_sal NUMBER)

RETURN NUMBER

IS

BEGIN

RETURN p_sal * tax_rate;

END calc_tax;

END manage_emps;

/

How many public procedures are in the MANAGE_EMPS package?

A. One

B. Two 

C. Three 

D. Four 

E. Five

Answer: C


certifyforall.com

Update 1z0-147 exam question:

Q34. Examine this package:

CREATE OR REPLACE PACKAGE pack_cur

IS

CURSOR c1 IS

SELECT prodid

FROM poduct

ORDER BY prodid DESC;

PROCEDURE proc1;

PROCEDURE proc2;

END pack_cur;

/

CREATE OR REPLACE PACKAGE BODY pack_cur

IS

v_prodid NUMBER;

PROCEDURE proc1 IS

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO v_prodid;

DBMS_OUTPUT.PUT_LINE('Row is: ' || c1%ROWCOUNT);

EXIT WHEN c1%ROWCOUNT >= 3;

END LOOP; END proc1;

PROCEDURE proc2 IS

BEGIN

LOOP

FETCH c1 INTO v_prodid;

DBMS_OUTPUT.PUT_LINE('Row is: ' ||c1%ROWCOUNT);

EXIT WHEN c1%ROWCOUNT >= 6;

END LOOP;

CLOSE c1;

END proc2;

END pack_cur;

/

The product table has more than 1000 rows. The SQL *Plus SERVEROUTPUT setting is turned on in your session.

You execute the procedure PROC1 from SQL *Plus with the command:

EXECUTE pack_cur.proc1

What is the output in your session?

A. ERROR at line 1:

B. Row is:

Row is:

Row is:

C. Row is: 1

Row is: 2

Row is: 3

D. Row is: 4

Row is: 5

Row is: 6

Answer: C


Q35. Which two does the INSTEAD OF clause in a trigger identify? (Choose two)

A. The view associated with the trigger.

B. The table associated with the trigger.

C. The event associated with the trigger.

D. The package associated with the trigger.

E. The statement level or for each row association to the trigger.

Answer: AC


Q36. What part of a database trigger determines the number of times the trigger body executes?

A. Trigger type

B. Trigger body

C. Trigger event

D. Trigger timing

Answer: A


1z0-147  exam question

Approved 1z0-147 rapidshare:

Q37. When using a packaged function in a query, what is true?

A. The COMMIT and ROLLBACK commands are allowed in the packaged function.

B. You can not use packaged functions in a query statement.

C. The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.

D. The packaged function can execute and INSERT, UPDATE, or DELETE statement against the table that is being queried if it is used in a subquery.

E. The packaged function can execute an INSERT, UPDATEM or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used.

Answer: C


Q38. You have a table with the following definition:

CREATE TABLE long_tab

(id NUMBER);

long_col LONG)

You need to convert the LONG_COL column from a LONG data type to a LOB data type. Which statement accomplish this task?

A. ALTER TABLE long_tab

MODIFY (LONG_COL CLOB);

B. EXECUTE dbms_lob.migrate(long_tab, long_col, clob)

C. EXECUTE dbms_manage.lob.migrate(long_tab, long_col, clob)

D. EXECUTE utl_lob.migrate(long_tab, long_col, clob)

E. EXECUTE utl_manage_lob.migrate(long_tab, long_col, clob)

Answer: A


Q39. Which three statements are true regarding database triggers? (Choose three)

A. A database trigger is a PL/SQL block, C, or Java procedure associated with a table, view, schema, or the database.

B. A database trigger needs to be executed explicitly whenever a particular event takes place.

C. A database trigger executes implicitly whenever a particular event takes place.

D. A database trigger fires whenever a data event (such as DML) or system event (such as logon, shutdown) occurs on a schema or database.

E. With a schema, triggers fire for each event for all users; with a database, triggers fire for each event for that specific user.

Answer: ACD


Q40. Which two statements about packages are true? (Choose two)

A. Packages can be nested.

B. You can pass parameters to packages.

C. A package is loaded into memory each time it is invoked.

D. The contents of packages can be shared by many applications.

E. You can achieve information hiding by making package constructs private.

Answer: DE