Reading study notes is one thing; sitting a timed exam is another. 2Pass4sure's desktop and online test engines recreate the pressure of the 1Z0-146 testing environment, so the Oracle database 11g:advanced pl/sql exam feels familiar long before exam day arrives.

Oracle 1Z0-146 Exam Overview:

Oracle 1Z0-146 Exam Syllabus Topics:

Oracle 1Z0-146 Exam FAQ — What Candidates Ask Most

Oracle database 11g:advanced pl/sql Sample Questions:

Question 1

You created a procedure as follows:
CREATE OR REPLACE PROCEDURE query_prod(twhr VARCHAR2)
IS
stmt VARCHAR2(100);
pname VARCHAR2(20);
BEGIN
stmt:='SELECT product_name FROM products WHERE product_id=:2';
EXECUTE IMMEDIATE stmt INTO pname USING twhr;
DBMS_OUTPUT.PUT_LINE(pname);
END;
/
View the Exhibit to examine the structure of PRODUCTS table.

Which statement is true about the procedure?

A. It reduces the chances of SQL injection by using bind arguments.
B. It can be invoked only from a PL/SQL block.
C. The values for bind arguments remain persistent in the session after the execution of the procedure.
D. It produces an error when invoked.

Question 2

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. The GETLENGTH routine must be replaced with the LENGTH built-in function in WRITEAPPEND.
D. FOR UPDATE must be added to the SELECT statement.

Question 3

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 generates trace information for PL/SQL programs in the PLSQL_TRACE_EVENTS table.
D. It produces an aggregated report of the SQL and PL/SQL execution times.
E. It is useful for understanding the structure and control flow of complex programs.

Question 4

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

A. The result cache can be increased by 65628 bytes.
B. Currently 52 KB is allocated to the result cache.
C. Currently 32 KB is allocated to the result cache.
D. In total, four blocks are used by the result cache.

Question 5

Examine the structure of the TEXT_TAB table. Name Null? Type
TEXT_ID NUMBER
DOC1 CLOB
DOC2 CLOB
You issue the following INSERT commands:
INSERT INTO text_tab VALUES (1, 'This is line 1',null);
INSERT INTO text_tab VALUES (2, 'This is line 1','This is line 2');
Then you execute the following block of the PL/SQL code:
DECLARE
vc1 VARCHAR2(1000):= 'This is the preface';
lb1 CLOB;
lb2 CLOB;
BEGIN
SELECT doc1 INTO lb1 FROM text_tab WHERE text_id=1;
SELECT doc1 || doc2 INTO lb1 FROM text_tab WHERE text_id=2;
lb2 := vc1|| lb1;
UPDATE text_tab SET doc2 = lb2 WHERE text_id = 1;
END;
/
What is the outcome?

A. It gives an error because the concatenation operator cannot be used with the CLOB data type.
B. It gives an error because CLOB variables should be initialized to EMPTY_CLOB().
C. It executes successfully.
D. It gives an error because VARCHAR2 should be explicitly converted to CLOB.

Solutions: