Q1. Examine the structure of the PRODUCTS table. Name Null? Type 

PRODUCT_ID NOT NULL NUMBER(6) 

PRODUCT_NAME VARCHAR2(50) 

CATEGORY_ID NUMBER(2) 

SUPPLIER_ID NUMBER(6) 

LIST_PRICE NUMBER(8,2) 

View the Exhibit and examine the PL/SQL block. 

On execution, the PL/SQL block generates the following error: 

ORA-01001: invalid cursor 

What could be the reason? 

A. Both the cursor variable arguments should have been passed in IN mode. 

B. The contents of one cursor variable cannot be assigned to another cursor variable using the := operator. 

C. The CLOSE statement closes both the cursor variables, therefore the last FETCH statement cannot execute. 

D. The name of the cursor variables defined and the name of the cursor variables passed as arguments must be the same. 

View Answer

Q2. Match the following external C procedure components with their descriptions: 

1. External procedure a. a process that starts the extproc process 

2. Shared library b. a session-specific process that executes the external procedure 

3. Alias library c. schema object that represents the operating system (OS) shared library 

4. The extproc process d. operating system file that stores the external procedure 

5. Listener process e. a unit of code written in C 

A. 1-e; 2-d; 3-c; 4-b; 5-a 

B. 1-c; 2-d; 3-e; 4-b; 5-a 

C. 1-e; 2-c; 3-d; 4-b; 5-a 

D. 1-a; 2-d; 3-e; 4-c; 5-b 

View Answer

Q3. You enabled PL/SQL tracing in a user session using the following command: 

SQL> EXECUTE DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ALL_CALLS); 

View Exhibit1 to examine the output. After some time, the query produces a different result as shown in Exhibit2. 

What is the cause for the change? 

A. The FOO procedure has been executed more than once. 

B. The PLSQL_DEBUG parameter is set to FALSE for the user session. 

C. The FOO procedure has been compiled with the DEBUG option, and executed. 

D. Schema level statistics have been gathered by the database administrator (DBA). 

View Answer

Q4. View Exhibit1 and examine the structure of the EMPLOYEES and DEPARTMENTS tables existing in your schema. 

View Exhibit2 and examine the PL/SQL block that you execute to display the department-wise incremented salary for all the departments in your company. 

The code generates an error on execution. 

What correction should be done to ensure the code executes successfully? 

A. The cursor variable parameter should be passed in IN OUT mode. 

B. The cursor variable should be defined as a strong REF CURSOR type. 

C. The cursor variable name passed as actual and formal parameters should be identical. 

D. The %NOTFOUND cursor attribute cannot be used with the cursor variables and should be replaced with a user defined exception. 

View Answer

Q5. Identify three guidelines for the DBMS_ASSERT package. (Choose three.) 

A. Prefix all calls to DBMS_ASSERT with the SYS schema name. 

B. Embed DBMS_ASSERT verification routines inside the injectable string. 

C. Escape single quotes when you use the ENQUOTE_LITERAL procedure. 

D. Define and raise exceptions explicitly to handle DBMS_ASSERT exceptions. 

E. Prefix all calls to DBMS_ASSERT with a schema name that owns the subprogram that uses the DBMS_ASSERT package. 

View Answer

Q6. To examine the dependencies between all PL/SQL objects and the tables and views they reference, you executed the following query as the user OE: 

SQL> SELECT owner || '.' || NAME refs_table 

, referenced_owner || '.' || referenced_name AS table_referenced 

FROM all_dependencies 

WHERE owner = USER 

AND TYPE IN ('PACKAGE', 'PACKAGE BODY','PROCEDURE', 'FUNCTION') 

AND referenced_type IN ('TABLE', 'VIEW') 

AND referenced_owner NOT IN ('SYS', 'SYSTEM') 

ORDER BY owner, NAME, referenced_owner, referenced_name; 

Which statement is true about the output of the query? 

A. It displays all PL/SQL code objects created by user OE that reference any table or view owned by other users except SYS and SYSTEM.. 

B. It displays no rows because this query needs to be executed as the user SYS for required results. 

C. It displays all PL/SQL code objects that reference a table or view directly for all the users in the database. 

D. It displays only those PL/SQL code objects created by the user OE that reference a table or view created by the user SYS. 

View Answer

Q7. Examine the following line of code that is part of a PL/S QL application: stmt:='SELECT session_id FROM sessions WHERE ' || p_where_stmt; Identify a solution for preventing SQL injection in the above code. 

A. Replace P_WHERE_STMT with a bind variable. 

B. Do not use APIs that allow arbitrary query parameters to be exposed. 

C. Use the RESTRICT_REFERENCES clause in the PL/SQL subprogram that contains the code. 

D. Use DBMS_SQL to detect that the expression provided for P_WHERE_STMT is free from SQL injection. 

View Answer

Q8. Examine the structure of the TEST_DETAILS table: Name Null? Type 

TEST_ID NUMBER 

DESCRIPTION CLOB 

DESCRIPTION data was entered earlier and saved for TEST_ID 12. 

You execute this PL/SQL block to add data to the end of the existing data in the DESCRIPTION column for TEST_ID 12: 

DECLARE 

clob_loc CLOB; 

buf CHAR(12); 

BEGIN 

SELECT description INTO clob_loc FROM test_details WHERE test_id = 12 ; 

buf := '0123456789' 

DBMS_LOB.WRITEAPPEND(clob_loc,DBMS_LOB.GETLENGTH(buf), buf); 

COMMIT; 

END; 

/ 

It generates an error on execution. 

What correction should you do to achieve the required result? 

A. WRITEAPPEND must be replaced with APPEND. 

B. The BUF variable data type must be changed to CLOB. C. FOR UPDATE must be added to the SELECT statement. 

D. The GETLENGTH routine must be replaced with the LENGTH built-in function in WRITEAPPEND. 

View Answer

Q9. Examine the structure of the PRINT_MEDIA table: Name Null? Type 

ADVT_ID NUMBER ADVT_SOURCE CLOB Examine the following PL/SQL block: 

DECLARE 

lobloc CLOB; 

buffer VARCHAR2(100); 

amount NUMBER; 

offset NUMBER :=1; 

BEGIN 

buffer :='This is the second line of a new document' 

amount := LENGTH(buffer); 

SELECT advt_source INTO lobloc FROM print_media WHERE advt_id=2 FOR UPDATE; 

DBMS_LOB.WRITE(lobloc,amount,offset,buffer); 

COMMIT; 

END; 

/ 

What must be the value in the ADVT_SOURCE column for the above code to execute 

successfully? 

A. null 

B. an empty locator 

C. a non-NULL value 

D. either null or any non-NULL values 

View Answer

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

View Answer