Q1. Which two statements are true about the DBMS_LOB.CREATETEMPORARY procedure that is used to create a temporary LOB? (Choose two.) 

A. It can be used for transforming data in permanent internal LOBs. 

B. It is used only for the migration of BasicFile to the SecureFile format. 

C. It is used only for the migration of the LONG column to the LOB column. 

D. It creates a LOB variable that is not associated with any table and is stored in the user's temporary tablespace. 

E. It creates a LOB variable that is associated with a specific table and is temporarily stored in the user's default tablespace. 

View Answer

Q2. View Exhibit1 and examine the structure of the EMPLOYEES table. 

itexamworld.com 

View Exhibit2 and examine the code in the PL/SQL block. 

The PL/SQL block fails to execute. What could be the reason? 

A. Nested tables cannot be returned by a function. 

B. The NEWNAMES nested table has not been initialized. 

C. The assignment operator cannot be used to transfer all the element values from GROUP1 to GROUP2. 

D. The third element of OLDNAMES cannot be assigned to the third element of GROUP1 because they are of inconsistent data types. 

E. LAST_NAME values cannot be assigned to the V_LAST_NAMES nested table because local collection types are not allowed in SQL statements. 

View Answer

Q3. Examine the structure of the EMPLOYEES table that exists in your schema. Name Null? Type 

EMPLOYEE_ID NOT NULL NUMBER(6) 

FIRST_NAME VARCHAR2(20) 

LAST_NAME NOT NULL VARCHAR2(25) 

JOB_ID NOT NULL VARCHAR2(10) 

SALARY NUMBER(8,2) 

COMMISSION_PCT NUMBER(2,2) 

DEPARTMENT_ID NUMBER(4) 

You successfully create a GET_MAX procedure to find the maximum salary in the department of a specified employee. 

You then code a PL/SQL block to display the maximum salary in the departments of the first five employees in the EMPLOYEES table. 

View the Exhibit. Examine the procedure and the block of PL/SQL code. 

What is the outcome of executing the block of PL/SQL code? 

A. It executes successfully and gives the required output. 

B. It gives an error because ROWNUM cannot be used in cursor definitions. 

C. It gives an error because usage of the %ROWCOUNT attribute is not valid. 

D. It executes successfully, but does not give the required output because the procedure call resets the %ROWCOUNT value. 

View Answer

Q4. Which statement describes the purpose of the plshprof command? 

A. It produces HTML output from raw profiler output. 

B. It produces HTML output from profiler tables in the database. 

C. It populates profiler tables in the database from raw profiler output. 

D. It produces raw profiler output on the most recently run applications. 

View Answer

Q5. Examine the following settings for a session: 

PLSQL_CODE_TYPE = NATIVE 

View the Exhibit and examine the PL/SQL code. 

You compile the program with the following attributes: 

SQL> ALTER PROCEDURE proc1 COMPILE PLSQL_OPTIMIZE_LEVEL = 1; 

Which statement is true about the execution of the PROC1 procedure in this scenario? 

A. The FUNC1 function would be called inline because PRAGMA INLINE forces a specific call to be inlined. 

B. The FUNC1 function would be inlined because the value set for the PLSQL_CODE_TYPE parameter is set to NATIVE. 

C. The FUNC1 function would be called inline irrespective of the value set for the PLSQL_OPTIMIZE_LEVEL parameter. 

D. The FUNC1 function would not be called inline because the value for the PLSQL_OPTIMIZE_LEVEL parameter is set to a lower value. 

View Answer

Q6. View the Exhibit and examine the output. 

Which statement is an inference from the output? 

A. The class file is pinned into the Java pool. 

B. The class file has been loaded into the shared library. 

C. Java class methods in the Java class file have been published. 

D. The loadjava command has been executed to load the Java source and class files. 

View Answer

Q7. Which statements are true about internal LOBs? (Choose all that apply.) 

A. They cannot use redo logging. 

B. They can be used as attributes of a user-defined data type. 

C. They cannot be passed as parameters to PL/SQL subprograms. 

D. They can be stored in a tablespace that is different from the tablespace that stores the table containing the LOB column. 

View Answer

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

Q9. Which two types of query results cannot be stored in the query result cache? (Choose two.) 

A. subquery results 

B. results of a query having the SYSDATE function 

C. results of a query having the GROUP BY clause 

D. results of a query having the DATE data type in the WHERE clause 

View Answer

Q10. View Exhibit1 and examine the structure of the EMPLOYEES table. 

View the Exhibit2 and examine the PL/SQL block that you execute for displaying the last name and hire date of the employees in department ID 60. 

Which statement is true about the outcome? 

A. It generates an error because RECORD type cannot be used with varrays. 

B. It generates an error because BULK COLLECT cannot be used with varrays. 

C. It executes successfully only if department ID 60 has five or less than five employees. 

D. It executes successfully even if department ID 60 has more than five employees by dynamically extending the varray. 

View Answer