Free Oracle 1Z0-146 Exam Dumps Questions & Answers

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

• A.  If you have one PL/SQL program unit instead of multiple smaller executable sections, performance can be improved.
• B.  Usage of the NOT NULL constraint in PL/SQL code can degrade performance.
• C.  Implicit data type conversion in PL/SQL code can improve performance.
• D.  Redundant SQL statements in PL/SQL code should be avoided.

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.

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.  The contents of one cursor variable cannot be assigned to another cursor variable using the := operator.
• B.  The CLOSE statement closes both the cursor variables, therefore the last FETCH statement cannot execute.
• C.  Both the cursor variable arguments should have been passed in IN mode.
• D.  The name of the cursor variables defined and the name of the cursor variables passed as arguments must be the same.

Which two types of query results cannot be stored in the query result cache? (Choose two.)

• A.  subquery results
• B.  results of a query having the GROUP BY clause
• C.  results of a query having the DATE data type in the WHERE clause
• D.  results of a query having the SYSDATE function

View the Exhibit and examine the PL/SQL code.

Which statement is true about the execution of the PL/SQL code?

• A.  The ASSIGN_VAL function generates an error during compilation because the EXTEND method cannot be used with nested tables.
• B.  The SELECT statement generates an error because the nested table has not been initialized in the ASSIGN_VAL function.
• C.  It executes successfully and displays 101 and 200000 values.
• D.  The ASSIGN_VAL function generates an error during compilation because nested tables cannot be returned by functions.