QUESTION 1
The add_player procedure inserts rows into the PLAYER table. Which command will show this directory dependency?
A. SELECT * FROM USER_DEPENDENCIES WHERE REFERENCD NAME = ' PLAYER ' ;
B. SELECT * FROM USER DEPENDENCIES WHERE REFERENCD NAME = ' ADD PLAYER ' ;
C. SELECT * FROM USER_DEPENDENCIES WHERE TYPE = 'DIR' ;
D. SELECT * FROM USER DEPENDENCIES WHERE REFERENCD NAME = ' TABLE ' ;
Answer: A
QUESTION 2
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
QUESTION 3
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. AKTER TABLE long_tab
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
QUESTION 4
Why do you use an INSTEAD OF trigger?
A. To perform clean up actions when ending a user session.
B. To insert data into a view that normally does not accept inserts.
C. To insert into an audit table when data is updated in a sensitive column.
D. To modify data in which the DML statement has been issued against an inherently non-updateable view.
Answer: D
QUESTION 5
When using a PL/SQL stored package, how is a side effect defined?
A. changes only to database tables
B. changes only to packaged public variables defined in a package body
C. changes only to packaged public variables defined in a package specification
D. changes to database tables or packaged public variables defined in a package body
E. changes to database tables or packaged variables defined in a package specification
Answer: E
QUESTION 6
Examine this package
CREATE OR REPLACE PACKAGE discounts IS g_id NUMBER:=7839 discount_rate NUMBER
:=0.00; PROCEDURE display_price(p_price NUMBER);
END discount;
/
CREATE OR REPLACE PACKAGE BODY discounts
IS PROCEDURE display_price (p_price NUMBERI) IS BEGIN DBMS_OUTPUT.PUT LINE (
'Discounted '|| TO_CHAR(p_price*NVL(discount_rate,1)));
END discount;
BEGIN Discount_rate=0.10;
END discounts/
The SOL*Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure DISPLAY_PRICE from SOL*Plus with the command EXECUTE discount.display_price(100); What is the result?
A. Discounted 10
B. Discounted 100
C. Discounted 0.00
D. Discounted NULL
E. Discounted 0.10
Answer: A
QUESTION 7
Which two statements about functions are true? (Choose two.)
A. A function must have a return statement in its body to execute successfully
B. Client-side functions can be used in SOL statements
C. A stored function that is called from a SOL statement can return a value of any PL/SOL variable data type
D. From SOL*Plus, a function can be executed by giving the command EXECUTE functionname; E. A stored function increases efficiency of queries by performing functions on the server rather than in the application
Answer: A, E
QUESTION 8
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: A, C
QUESTION 9
Examine this code
CREATE OR REPLACE FUNCTION change_dept (p_old_id NUMBER, p_deptname VARCHAR2)
RETURN NUMBER IS v_new_id NUMBER
BEGIN
SELECT departments_seq.nextval
INTO v_new_id
FROM dual;
UPDATE departments
SET departmenet_id = v_new_id, Department_name = p_deptname WHERE
department_id=p_old_id; Return v_new_id;
End;
/
There are no foreign key integrity constraints on the EMPLOYEES and DEPARTMENTS tables. Which statement performs a successful update to the EMPLOYEES table?
A. UPDATE departments SET department_id = change_dept(10, 'Finance') Where dapartment_id=10;
B. UPDATE employees SET department_id = change_dept(10, 'Finance') Where dapartment_id=10;
C. UPDATE departments change_dept(270, 'Outsource') Where dapartment_name='payroll'; D. UPDATE employees SET department_id = change_dept(10, 'Finance') WHERE department_id=departments:CURRVAl:
Answer: B
The add_player procedure inserts rows into the PLAYER table. Which command will show this directory dependency?
A. SELECT * FROM USER_DEPENDENCIES WHERE REFERENCD NAME = ' PLAYER ' ;
B. SELECT * FROM USER DEPENDENCIES WHERE REFERENCD NAME = ' ADD PLAYER ' ;
C. SELECT * FROM USER_DEPENDENCIES WHERE TYPE = 'DIR' ;
D. SELECT * FROM USER DEPENDENCIES WHERE REFERENCD NAME = ' TABLE ' ;
Answer: A
QUESTION 2
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
QUESTION 3
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. AKTER TABLE long_tab
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
QUESTION 4
Why do you use an INSTEAD OF trigger?
A. To perform clean up actions when ending a user session.
B. To insert data into a view that normally does not accept inserts.
C. To insert into an audit table when data is updated in a sensitive column.
D. To modify data in which the DML statement has been issued against an inherently non-updateable view.
Answer: D
QUESTION 5
When using a PL/SQL stored package, how is a side effect defined?
A. changes only to database tables
B. changes only to packaged public variables defined in a package body
C. changes only to packaged public variables defined in a package specification
D. changes to database tables or packaged public variables defined in a package body
E. changes to database tables or packaged variables defined in a package specification
Answer: E
QUESTION 6
Examine this package
CREATE OR REPLACE PACKAGE discounts IS g_id NUMBER:=7839 discount_rate NUMBER
:=0.00; PROCEDURE display_price(p_price NUMBER);
END discount;
/
CREATE OR REPLACE PACKAGE BODY discounts
IS PROCEDURE display_price (p_price NUMBERI) IS BEGIN DBMS_OUTPUT.PUT LINE (
'Discounted '|| TO_CHAR(p_price*NVL(discount_rate,1)));
END discount;
BEGIN Discount_rate=0.10;
END discounts/
The SOL*Plus SERVEROUTPUT setting is turned on in your session. You execute the procedure DISPLAY_PRICE from SOL*Plus with the command EXECUTE discount.display_price(100); What is the result?
A. Discounted 10
B. Discounted 100
C. Discounted 0.00
D. Discounted NULL
E. Discounted 0.10
Answer: A
QUESTION 7
Which two statements about functions are true? (Choose two.)
A. A function must have a return statement in its body to execute successfully
B. Client-side functions can be used in SOL statements
C. A stored function that is called from a SOL statement can return a value of any PL/SOL variable data type
D. From SOL*Plus, a function can be executed by giving the command EXECUTE functionname; E. A stored function increases efficiency of queries by performing functions on the server rather than in the application
Answer: A, E
QUESTION 8
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: A, C
QUESTION 9
Examine this code
CREATE OR REPLACE FUNCTION change_dept (p_old_id NUMBER, p_deptname VARCHAR2)
RETURN NUMBER IS v_new_id NUMBER
BEGIN
SELECT departments_seq.nextval
INTO v_new_id
FROM dual;
UPDATE departments
SET departmenet_id = v_new_id, Department_name = p_deptname WHERE
department_id=p_old_id; Return v_new_id;
End;
/
There are no foreign key integrity constraints on the EMPLOYEES and DEPARTMENTS tables. Which statement performs a successful update to the EMPLOYEES table?
A. UPDATE departments SET department_id = change_dept(10, 'Finance') Where dapartment_id=10;
B. UPDATE employees SET department_id = change_dept(10, 'Finance') Where dapartment_id=10;
C. UPDATE departments change_dept(270, 'Outsource') Where dapartment_name='payroll'; D. UPDATE employees SET department_id = change_dept(10, 'Finance') WHERE department_id=departments:CURRVAl:
Answer: B
