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