Q1. Which two statements are true about the tuning of PL/SQL code? (Choose two.)
A. Redundant SQL statements in PL/SQL code should be avoided.
B. Implicit data type conversion in PL/SQL code can improve performance.
C. Usage of the NOT NULL constraint in PL/SQL code can degrade performance.
D. If you have one PL/SQL program unit instead of multiple smaller executable sections, performance can be improved.
Answer: A,C
Q2. Examine the code in the following PL/SQL block:
DECLARE
TYPE NumList IS TABLE OF INTEGER;
List1 NumList := NumList(11,22,33,44);
BEGIN
List1.DELETE(2);
DBMS_OUTPUT.PUT_LINE
( 'The last element# in List1 is ' || List1.LAST ||
' and total of elements is '||List1.COUNT);
List1.EXTEND(4,3);
END;
/
Which two statements are true about the above code? (Choose two.)
A. LAST and COUNT give different values.
B. LAST and COUNT give the same values.
C. The four new elements that are added contain the value 33.
D. The four new elements that are added contain the value 44.
Answer: A,C
Q3. Which two statements are true about the context of an application? (Choose two.)
A. It is attached to a session.
B. It is owned by the user SYS.
C. A user can change the context of his or her application.
D. The PL/SQL package associated with the contex t must exist before the context is created.
E. The predefined attributes in the USEREN V application context can be changed as per the requirements.
Answer: A,B
Q4. DATA_FILES is a directory object that contains the DETAILS.TXT text file.
You have the required permissions to access the directory object.
You create a table using the following command:
CREATE TABLE clob_tab(col2 CLOB);
View the Exhibit and examine the PL/SQL block that you execute for loading the external text file into the table that currently has no rows. The PL/SQL block results in an error.
What correction must be done to ensure the PL/SQL block executes successfully?
A. The L_OUT variable must be initialized to an empty locator.
B. The L_OUT variable has to be declared as a temporary LOB. C. The A_CLOB variable has to be declared as a temporary LOB.
D. The clause RETURNING col2 INTO a_clob should be added to the INSERT statement to correctly initialize the locator.
Answer: D
Q5. Which two statements are true about associative arrays and nested tables? (Choose two.)
A. Only associative arrays can hold an arbitrary number of elements.
B. Only nested tables can be used as column types in database tables.
C. Both associative arrays and nested tables can hold an arbitrary number of elements.
D. Both associative arrays and nested tables can be used as column types in database tables.
Answer: B,C
Q6. Which two statements are true about the extproc process? (Choose two.)
A. It loads the dynamic library.
B. It is started by the server process.
C. It converts the C output back to PL/SQL.
D. A single extproc process serves all user sessions.
Answer: A,C
Q7. Examine the structure of the DEPARTMENTS table.
Name Null? Type
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
LOCATION_ID NUMBER(4)
View the Exhibit and examine the code that you plan to use for creating a package to obtain the
details of an employee using a host variable on the client side.
In SQL*Plus, you plan to use the following commands:
SQL> VARIABLE x REFCURSOR
SQL> EXECUTE emp_data.get_emp(195,:x)
SQL> PRINT x
Which statement is true about the above scenario?
A. The package executes successfully and passes the required data to the host variable.
B. The package specification gives an error on compilation because cursor variable types cannot be defined in the specification.
C. The package specification gives an error on compilation because the cursor variable parameter was specified before you defined it.
D. The package executes successfully, but does not pass the required data to the host variable because the cursor is closed before the PRINT statement runs.
Answer: D
Q8. DATA_FILES is a directory object that contains the DETAILS.TXT text file. You have the required permissions to access the directory object.
You create a table using the following command:
CREATE TABLE clob_tab(col2 CLOB);
View the Exhibit and examine the PL/SQL block that you execute for loading the external text file into the table that currently has no rows. The PL/SQL block results in an error.
What correction must be done to ensure the PL/SQL block executes successfully?
A. The L_OUT variable must be initialized to an empty locator. B. The L_OUT variable has to be declared as a temporary LOB. C. The A_CLOB variable has to be declared as a temporary LOB.
D. The clause RETURNING col2 INTO a_clob should be added to the INSERT statement to correctly initialize the locator.
Answer: D
Q9. In which two situations is the body of a result-cached function executed? (Choose two.)
A. if the memory allocated for the result cache is increased
B. if a session on this database instance invokes the function with the same parameter values
C. if the first time a session on this database instance invokes the function with a parameter value
D. if a session executes a data manipulation language (DML) statement on a table or view that was specified in the RELIES_ON clause of a result-cached function
Answer: C,D
Q10. You created an application context successfully. The user OE was granted the EXECUTE privilege on the DBMS_SESSION package. The user receives this error while setting the value for an attribute within the context: SQL> EXECUTE DBMS_SESSION.SET_CONTEXT('SALES_ORDERS_CTX','ACCOUNT_MGR','OE'); BEGIN DBMS_SESSION.SET_CONTEXT('SALES_ORDERS_CTX','ACCOUNT_MGR','OE'); END; * ERROR at line 1: ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SESSION", line 94
ORA-06512: at line 1
What is the reason for this error?
A. The context was created with a package name in the USING clause.
B. The attribute can be set only in the package associated with the context.
C. The package associated with the context did not exist at the time of creation of the context.
D. The value for an attribute of a user-defined context can be set only by the ALTER SESSION command.
Answer: B