Oracle database 11g:advanced pl/sql - 1Z0-146 Exam Questions [2026]

QUESTION NO: 1
Which three statements are true about hierarchical profiling? (Choose three.)

A. It can be used to identify hotspots and tuning opportunities in PL/SQL applications. B. It provides function-level summaries. C. It is useful for understanding the structure and control flow of complex programs. D. It produces an aggregated report of the SQL and PL/SQL execution times. E. It generates trace information for PL/SQL programs in the PLSQL_TRACE_EVENTS table.

Correct Answer: A,B,C

QUESTION NO: 2
When do you use static SQL as a technique for avoiding SQL injection?

A. when the code contains data definition language (DDL) statements B. when the SET clause values are unknown at the time of code compilation C. when the WHERE clause values are unknown D. when all Oracle identifiers are known at the time of code compilation

Correct Answer: D

QUESTION NO: 3
Which two statements are true about BFILES? (Choose two.)

A. BFILES support only sequential reads. B. The RMAN backup automatically backs up the BFILE locators and the corresponding physical files. C. The physical file corresponding to a BFILE locator must reside on the file system that is accessible from the server where the database exists. D. When LOB is deleted, the corresponding physical file is automatically deleted. E. BFILES can be used as attributes in an object type.

Correct Answer: C,E

QUESTION NO: 4
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. The BUF variable data type must be changed to CLOB. B. FOR UPDATE must be added to the SELECT statement. C. WRITEAPPEND must be replaced with APPEND. D. The GETLENGTH routine must be replaced with the LENGTH built-in function in WRITEAPPEND.

Correct Answer: B

QUESTION NO: 5
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 the same values. B. The four new elements that are added contain the value 33. C. LAST and COUNT give different values. D. The four new elements that are added contain the value 44.

Correct Answer: B,C

QUESTION NO: 6
Which two statements are true about cursor variables? (Choose two.)

A. The OPEN FOR statement executes the query associated with a cursor variable and identifies the result set. B. Cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) can be applied to a cursor variable. C. The FETCH statement executes the query associated with a cursor variable and identifies the result set. D. Cursor variables can be parameterized like cursors. E. The query associated with a cursor variable cannot reference host variables and PL/SQL variables.

Correct Answer: A,B

QUESTION NO: 7
Examine the section of code taken from a PL/SQL program:
PROCEDURE p1 (x PLS_INTEGER) IS
... ...
PRAGMA INLINE (p1, 'NO');
x:= p1(1) + p1(2) + 17; -- Call 1
...
x:= p1(3) + p1(4) + 17; -- Call 2
Call 1 and Call 2 are the comments for distinguishing the code. The PLSQL_OPTIMIZE_LEVEL
parameter is set to 3. Which two statements are true in this scenario? (Choose two.)

A. The calls to the P1 procedure are inlined in both the sections commented as Call 1 and Call 2. B. The calls to the P1 procedure are never inlined in both the sections commented as Call 1 and Call 2. C. The calls to the P1 procedure might be inlined in the section commented as Call 2. D. The calls to the P1 procedure are not inlined in the section commented as Call 1.

Correct Answer: C,D

QUESTION NO: 8
Which statements are true about the SecureFile storage paradigm? (Choose two.)

A. SecureFile options enabled for a LOB column can be overridden on a per-LOB basis within the column. B. SecureFile is the default storage paradigm for all LOBs that are stored in locally managed tablespaces if the DB_SECUREFILE parameter is set to ALWAYS. C. Automatic Segment Space Management must be enabled for a tablespace to store SecureFile LOBs. D. SecureFile storage can be used for internal and external LOBs.

Correct Answer: A,C

QUESTION NO: 9
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.)

A. tb5 B. tb2 C. tb4 D. tb1 E. tb3

Correct Answer: B,E

QUESTION NO: 10
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.)

A. an identifier passed to a subprogram in IN OUT mode B. an assignment made to VARIABLE C. declaration of a variable or formal parameter D. execution of the GOTO statement or raise of an exception

Correct Answer: B,C