1Z0-146 | What 100% Correct 1Z0-146 practice test Is?
Q51. View the Exhibit and examine the PL/SQL code.
Identify the sections of the code that need to be modified for better performance gains. (Choose all that apply.)
A. Use cursors to fetch values.
B. Remove redundant SQL statements in the procedures.
C. Avoid the NOT NULL constraint when declaring the variable.
D. Define and compile procedures independently, and not as part of a package.
Answer: B,C
Q52. Which two reports can be retrieved by using the various procedures in the DBMS_METADATA PL/SQL package? (Choose two.)
A. DDL report for all objects dependent on a table
B. DDL report for all the objects stored in a tablespace
C. DDL report for all the invalidated objects in a schema
D. data definition language (DDL) report for all the tables in a schema
Answer: A,D
Q53. Examine the following error:
SQL> DECLARE
v_runid NUMBER;
BEGIN
v_runid := DBMS_HPROF.ANALYZE (LOCATION => 'PROFILE_DATA',
FILENAME => 'pd_cc_pkg.txt');
DBMS_OUTPUT.PUT_LINE('Run ID: ' || v_runid);
END;
DECLARE
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_HPROF", line 299
ORA-06512: at line 4
What would you do to execute the above block successfully?
A. Start the PL/SQL profiler before executing the block.
B. Run the tracetab.sql script located at ORACLE_HOMERDBMSADMIN.
C. Run the dbmshptab.sql script located at ORACLE_HOMERDBMSADMIN.
D. Grant READ and WRITE privileges to the current user on the PROFILE_DATA directory object.
Answer: C
Q54. Which two statements are true about cursor variables? (Choose two.)
A. Cursor variables can be parameterized like cursors.
B. The query associated with a cursor variable cannot reference host variables and PL/SQL variables.
C. The FETCH statement executes the query associated with a cursor variable and identifies the result set.
D. Cursor attributes (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT) can be applied to a cursor variable.
E. The OPEN FOR statement executes the query associated with a cursor variable and identifies the result set.
Answer: D,E
Q55. Examine the structure of the EMPLOYEES table that exists in your schema. Name Null? Type
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
DEPARTMENT_ID NUMBER(4)
You successfully create a GET_MAX procedure to find the maximum salary in the department of a specified employee.
You then code a PL/SQL block to display the maximum salary in the departments of the first five employees in the EMPLOYEES table.
View the Exhibit. Examine the procedure and the block of PL/SQL code.
What is the outcome of executing the block of PL/SQL code?
A. It executes successfully and gives the required output.
B. It gives an error because ROWNUM cannot be used in cursor definitions.
C. It gives an error because usage of the %ROWCOUNT attribute is not valid.
D. It executes successfully, but does not give the required output because the procedure call resets the %ROWCOUNT value.
Answer: A
Q56. Which two statements are true about the DBMS_LOB.CREATETEMPORARY procedure that is used to create a temporary LOB? (Choose two.)
A. It can be used for transforming data in permanent internal LOBs.
B. It is used only for the migration of BasicFile to the SecureFile format.
C. It is used only for the migration of the LONG column to the LOB column.
D. It creates a LOB variable that is not associated with any table and is stored in the user's temporary tablespace.
E. It creates a LOB variable that is associated with a specific table and is temporarily stored in the user's default tablespace.
Answer: A,D
Q57. 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?
A. It allows tracing of exceptions raised by all subprograms executed in the session.
B. It allows tracing of user-defined exceptions raised by all subprograms executed in the session.
C. It allows tracing of system-defined exceptions raised by all subprograms executed in the session.
D. It allows tracing of exceptions raised by subprograms compiled with the DEBUG option and executed in the session.
Answer: D
Q58. Which three statements are true about hierarchical profiling? (Choose three.)
A. It provides function-level summaries.
B. It produces an aggregated report of the SQL and PL/SQL execution times.
C. It is useful for understanding the structure and control flow of complex programs.
D. It can be used to identify hotspots and tuning opportunities in PL/SQL applications.
E. It generates trace information for PL/SQL programs in the PLSQL_TRACE_EVENTS table.
Answer: A,C,D
Q59. 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 SYSDATE function
C. results of a query having the GROUP BY clause
D. results of a query having the DATE data type in the WHERE clause
Answer: A,B
Q60. Which two statements are true about REF CURSOR types? (Choose two.)
A. REF CURSOR types cannot be defined inside a package.
B. SYS_REFCURSOR can be used to declare cursor variables in stored procedures and functions.
C. A REF CURSOR return type can be declared using %TYPE, or %ROWTYPE, or a userdefined record.
D. Only a weak REF CURSOR type can be used as a formal parameter of a stored procedure or function.
Answer: B,C