Oracle 11g: Advanced PL/SQL v5.0 (1z0-146)

In which two situations is the body of a result-cached function executed? (Choose two.)

Identify the component of the PL/SQL hierarchical profiler that uploads the result of profiling into database tables.

Examine the code snippet from the declarative section of a PL/SQL block:

DECLARE -
TYPE va1 IS VARRAY(10) OF VARCHAR2(20);
SUBTYPE scale IS NUMBER(1,0);
TYPE tb1 IS TABLE OF departments.department_name%TYPE INDEX BY departments.department_id%TYPE;
TYPE tb2 IS TABLE OF va1 INDEX BY PLS_INTEGER;
TYPE tb3 IS TABLE OF scale INDEX BY VARCHAR2(10);
TYPE tb4 IS TABLE OF DATE INDEX BY DATE;
TYPE tb5 IS TABLE OF NUMBER INDEX BY CHAR(2);
....
Which of the above are valid definitions for associative arrays? (Choose all that apply.)

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?

Which two statements correctly describe the features of SecureFiles? (Choose two.)

You issue the following command to create the PRINT_MEDIA table.

CREATE TABLE print_media -
(product_id NUMBER(3),
ad_sourcetext CLOB,
ad_photo BLOB);
Evaluate the following INSERT statements:
INSERT INTO print_media VALUES (1, empty_clob(),empty_blob());
INSERT INTO print_media VALUES (2,'This is a One Line Story',null);
INSERT INTO print_media VALUES (3,'This is another One Line Story',empty_blob());
INSERT INTO print_media VALUES (4,empty_clob(),to_blob('This is new Story'));
Which of the above INSERT statements are valid?

Which two statements are true about the inlining of PL/SQL subprograms? (Choose two.)

View Exhibit1 and examine the structure of the EMPLOYEES table.
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.

Which two statements are true about the usage of the
DBMS_DESCRIBE.DESCRIBE_PROCEDURE procedure? (Choose two.)

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

Examine the following structure:

SQL> DESCRIBE user_identifiers -
Name Null? Type
----------------------------------------- -------- -----------------------
NAME VARCHAR2(30)
SIGNATURE VARCHAR2(32)
TYPE VARCHAR2(18)
OBJECT_NAME NOT NULL VARCHAR2(30)
OBJECT_TYPE VARCHAR2(13)
USAGE VARCHAR2(11)
USAGE_ID NUMBER
LINE NUMBER
COL NUMBER
USAGE_CONTEXT_ID NUMBER
Identify two scenarios in which information is stored in the USAGE column. (Choose two.)

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?

The following command is executed to start tracing in a user session:

SQL> EXECUTE -
DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ENABLED_EXCEPTIONS)
;
Which statement is true about the effect of the command?

You executed this command to gather information about the memory allocation for storing query results:
SQL> execute dbms_result_cache.memory_report
View the Exhibit and examine the output for the execution of the
DBMS_RESULT_CACHE.MEMORY_REPORT procedure.
Which two statements are true about the output in the Exhibit? (Choose two.)

The user OE is working on an application that needs to call an external C program multiple times in a single session. However, the extproc.exe file on the server gets accidentally deleted after the OE user connected and made calls to the external C program. Which statement is true about the current session by the OE user?