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

View Answer

Q2. Examine the following parameter values for a session: 

PLSQL_CODE_TYPE = NATIVE 

PLSQL_OPTIMIZE_LEVEL = 2 

Which two statements are true in this scenario? (Choose two.) 

A. The compiler automatically inlines subprograms even if you specify that they not be inlined. 

B. The compiler optimizes PL/SQL programs by elimination of unnecessary computations and exceptions. 

C. PL/SQL statements in a PL/SQL program unit are compiled into machine-readable code, and stored in the SYSTEM tablespace. 

D. PL/SQL statements in a PL/SQL program unit are compiled into an intermediate form, machine-readable code, which is stored in the database dictionary. 

View Answer

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

View Answer

Q4. Which two are major approaches that can be used to reduce the SQL injection by limiting user input? (Choose two.) 

A. Restrict users accessing specified web page. 

B. Use NUMBER data type if only positive integers are needed. 

C. Use dynamic SQL and construct it through concatenation of input values. 

D. In PL/SQL API, expose only those routines that are intended for customer use. 

View Answer

Q5. Which two guidelines should be considered when designing and using cursors in a PL/SQL block? (Choose two.) 

A. When fetching from a cursor, fetch into a record. 

B. When fetching from a cursor, fetch required values into individually declared variables. 

C. Whenever possible, explicitly declare the cursor and use the OPEN, FETCH, and CLOSE statements to manipulate the cursor instead of using the cursor FOR loop. 

D. Whenever possible, use the cursor FOR loop instead of explicitly declaring the cursor and using the OPEN, FETCH, and CLOSE statements to manipulate the cursor. 

View Answer

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

View Answer

Q7. Identify two strategies against SQL injection. (Choose two.) 

A. Using parameterized queries with bind arguments. 

B. Use subprograms that are run with the definer's right. 

C. Use RESTRICT_REFERENCE clauses in functions that use dynamic SQLs. 

D. Validate user inputs to functions that use dynamic SQLs built with concatenated values. 

View Answer

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

View Answer

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

View Answer

Q10. You executed the following command to alter the session parameter: 

SQL> ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL' 

Which two statements are true in this scenario? (Choose two.) 

A. If the SYSAUX tablespace is unavailable, and you compile a program unit, PL/Scope does not collect data for the compiled object. 

B. All the identifiers declared in compiled program units before altering the parameter settings appear in the *_IDENTIFIER static data dictionary views. 

C. All the identifiers declared in compiled program units before altering the parameter settings do not appear in the *_IDENTIFIER static data dictionary views. 

D. If the SYSAUX tablespace is unavailable, and you compile a program unit, PL/Scope collects data for the compiled object and stores it in the SYSTEM tablespace. 

View Answer