Oracle 1Z0-147 問題集

質問 1：
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 internal logic of procedure PROCESS_EMP is modified and successfully recompiles, UPDATE_EMP gets invalidated and will recompile when invoked for the first time.
B. If the signature of procedure PROCESS_EMP is modified and successfully recompiles, the EMP table is invalidated.
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.
正解：A,E
解説: (Topexam メンバーにのみ表示されます)

質問 2：
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 is set to 1.00 each time the procedure DISPLAY_PRICE is invoked.
C. The value of DISCOUNT_RATE always remains 0.00 in a session.
D. The value of DISCOUNT_RATE is set to 0.10 when the package is invoked for the first time in a session.
正解：D
解説: (Topexam メンバーにのみ表示されます)

質問 3：
Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased?
A. CREATE OR REPLACE TRIGGER check_sal
AFTER UPDATE OR sal ON emp
WHEN (new.sal < old.sal OR
-new.sal > old.sal * 1.1)
BEGIN
RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease
salary not increase by more than 10%');
END;
B. CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN (new.sal < old.sal OR
new.sal > old.sal * 1.1)
BEGIN
RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease
salary not increase by more than 10%');
END;
C. ALTER TABLE emp ADD
CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1);
D. CREATE OR REPLACE TRIGGER check_sal
BEFORE UPDATE OF sal ON emp
WHEN (new.sal < old.sal OR
new.sal > old.sal * 1.1)
BEGIN
RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease
salary not increase by more than 10%');
END;
正解：B
解説: (Topexam メンバーにのみ表示されます)

質問 4：
Which two statements about object dependencies are accurate? (Choose two.)
A. When referencing a package procedure or function from a stand-alone procedure or function, if the package body changes and the package specification does not change, the stand-alone procedure referencing a package construct remains valid.
B. When referencing a package procedure or function from a stand-alone procedure or function, if the package body changes and the package specification does not change, the stand-alone procedure referencing a package construct becomes invalid
C. When referencing a package procedure or function from a stand-alone procedure or function, if the package specification changes, the package body remains valid but the stand-alone procedure becomes invalid
D. When referencing a package procedure or function from a stand-alone procedure or function, If the package specification changes, the stand-alone procedure referencing a package construct as well as the package body become invalid
正解：A,D
解説: (Topexam メンバーにのみ表示されます)

質問 5：
A CALL statement inside the trigger body enables you to call ______.
A. A stored function.
B. A package.
C. Another database trigger.
D. A stored procedure.
正解：D
解説: (Topexam メンバーにのみ表示されます)

質問 6：
You need to create a DML trigger. Which five pieces need to be identified? (Choose five)
A. Package name
B. DML event
C. Trigger name
D. Trigger body
E. Package body
F. Trigger timing
G. Table
H. System event
正解：B,C,D,F,G
解説: (Topexam メンバーにのみ表示されます)