Oracle 1Z0-147 試験概要:

Oracle 1Z0-147 試験シラバストピック:

Oracle9i program with pl/sql 認定 1Z0-147 試験問題：

1. 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 can execute an INSERT, UPDATEM or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used.
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 cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.

2. 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) Application trigger
B) DML trigger
C) System event trigger
D) INSTEAD OF trigger

3. Examine this code:
CREATE OR REPLACE PROCEDURE insert_dept
(p_location_id NUMBER)
IS
v_dept_id NUMBER(4);
BEGIN
INSERT INTO departments
VALUES (5, 'Education', 150, p_location_id);
SELECT department_id
INTO v_dept_id
FROM employees
WHERE employee_id=99999;
END insert_dept;
/
CREATE OR REPLACE PROCEDURE insert_location
( p_location_id NUMBER,
p_city VARCHAR2)
IS
BEGIN
INSERT INTO locations(location_id, city)
VALUES (p_location_id, p_city);
insert_dept(p_location_id);
END insert_location;
/
You just created the departments, the locations, and the employees table. You did not insert any
rows. Next you created both procedures.
You new invoke the insert_location procedure using the following command:
EXECUTE insert_location (19, 'San Francisco')
What is the result in thisEXECUTE command?

A) The locations table and the departments table both have one row. The employees table is empty.
B) The locations, departments, and employees tables are empty.
C) The departments table has one row.
The locations and the employees tables are empty.
D) The location table has one row.
The departments and the employees tables are empty.

4. 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) Row is: 4 Row is: 5 Row is: 6
B) Row is: 1 Row is: 2 Row is: 3
C) ERROR at line 1:
D) Row is: Row is: Row is:

5. Examine this package:
CREATE OR REPLACE PACKAGE discounts
IS
g_id NUMBER := 7829;
discount_rate NUMBER := 0.00;
PROCEDURE display_price (p_price NUMBER);
END discounts;
/
CREATE OR REPLACE PACKAGE BODY discounts
IS
PROCEDURE display_price (p_price NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Discounted '||
TO_CHAR(p_price*NVL(discount_rate, 1)));
END display_price;
BEGIN
discount_rate := 0.10;
END discounts;
/
Which statement is true?

A) The value of DISCOUNT_RATE is set to 0.10 each time the package is invoked in a session.
B) The value of DISCOUNT_RATE always remains 0.00 in a session.
C) The value of DISCOUNT_RATE is set to 0.10 when the package is invoked for the first time in a session.
D) The value of DISCOUNT_RATE is set to 1.00 each time the procedure DISPLAY_PRICE is invoked.

質問と回答：