Oracle 11g: Advanced PL/SQL
Question No: 21 – (Topic 0)
Which two statements are true about associative arrays and nested tables? (Choose two.)
Only associative arrays can hold an arbitrary number of elements.
Only associative arrays can use numbers and strings for subscripts.
Both associative arrays and nested tables can hold an arbitrary number of elements.
Both associative arrays and nested tables can use numbers and strings for subscripts.
Question No: 22 – (Topic 0)
You executed the following command to alter the session parameter:
SQLgt; ALTER SESSION SET PLSCOPE_SETTINGS = #39;IDENTIFIERS:ALL#39;;
Which two statements are true in this scenario? (Choose two.)
If the SYSAUX tablespace is unavailable, and you compile a program unit, PL/Scope does not collect data for the compiled object.
All the identifiers declared in compiled program units before altering the parameter settings appear in the *_IDENTIFIER static data dictionary
All the identifiers declared in compiled program units before altering the parameter settings do not appear in the *_IDENTIFIER static data
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
Question No: 23 – (Topic 0)
Examine the following line of code that is part of a PL/SQL application: stmt:=#39;SELECT session_id FROM sessions WHERE #39; || p_where_stmt;
Identify a solution for preventing SQL injection in the above code.
Replace P_WHERE_STMT with a bind variable.
Do not use APIs that allow arbitrary query parameters to be exposed.
Use the RESTRICT_REFERENCES clause in the PL/SQL subprogram that contains the
Use DBMS_SQL to detect that the expression provided for P_WHERE_STMT is free from SQL injection.
Question No: 24 – (Topic 0)
Which two conditions must be true for a PL/SQL function to be result cached? (Choose two.)
It must be part of a package.
It must be a pipelined table function.
It must not be defined in an anonymous block.
It must have at least one OUT or IN OUT parameter.
Question No: 25 – (Topic 0)
You created a PL/SQL subprogram that successfully invokes an external C procedure. After a while, the database administrator (DBA) drops the alias library schema object. The shared library exists in the system. Which statement is true in this scenario?
The corresponding shared library is also removed from the system.
PL/SQL subprograms can be used to invoke the external C procedure.
The existing extproc process is terminated and a new extproc is started.
The PL/SQL subprogram that depends on the external C program becomes invalid.
Question No: 26 – (Topic 0)
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?
The L_OUT variable must be initialized to an empty locator.
The L_OUT variable has to be declared as a temporary LOB.
The A_CLOB variable has to be declared as a temporary LOB.
The clause RETURNING col2 INTO a_clob should be added to the INSERT statement to correctly initialize the locator.
Question No: 27 – (Topic 0)
Which two types of query results cannot be stored in the query result cache? (Choose two.)
results of a query having the SYSDATE function
results of a query having the GROUP BY clause
results of a query having the DATE data type in the WHERE clause
Question No: 28 – (Topic 0)
Examine the structure of the PRINT_MEDIA table:
Name Null? Type
——– —- —– ADVT_ID NUMBER ADVT_SOURCE CLOB
Examine the following PL/SQL block:
buffer VARCHAR2(100); amount NUMBER; offset NUMBER :=1; BEGIN
buffer :=#39;This is the second line of a new document#39;; amount := LENGTH(buffer);
SELECT advt_source INTO lobloc FROM print_media WHERE advt_id=2 FOR UPDATE; DBMS_LOB.WRITE(lobloc,amount,offset,buffer);
What must be the value in the ADVT_SOURCE column for the above code to execute successfully?
an empty locator
a non-NULL value
either null or any non-NULL values
Question No: 29 – (Topic 0)
View the Exhibit and examine the PL/SQL code.
The code takes a long time to execute. What would you recommend to improve performance?
using NOT NULL constraint when declaring the variables
using the BULK COLLECT option for query instead of cursor
using WHILE.. END LOOP instead of FOR .. END LOOP
using the SIMPLE_INTEGER data type instead of the NUMBER data type
Question No: 30 – (Topic 0)
Which statements are true about internal LOBs? (Choose all that apply.)
They cannot use redo logging.
They can be used as attributes of a user-defined data type.
They cannot be passed as parameters to PL/SQL subprograms.
They can be stored in a tablespace that is different from the tablespace that stores the table containing the LOB column.
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|