Oracle 1Z0-146 Online Practice Questions and Exam Preparation

Oracle 1Z0-146 Online Questions & Answers

• Question 101:

  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 assignment made to VARIABLE
  B. declaration of a variable or formal parameter
  C. an identifier passed to a subprogram in IN OUT mode
  D. execution of the GOTO statement or raise of an exception
  A. an assignment made to VARIABLE
  B. declaration of a variable or formal parameter

• Question 102:

  View the Exhibit and examine the code in the PL/SQL block.

  

  The PL/SQL block generates an error on execution.

  What is the reason?

  A. The DELETE(n) method cannot be used with varrays.
  B. The DELETE(n) method cannot be used with nested tables.
  C. The NEXT method cannot be used with an associative array with VARCHAR2 key values.
  D. The NEXT method cannot be used with a nested table from which an element has been deleted.
  A. The DELETE(n) method cannot be used with varrays.

• Question 103:

  Which two types of metadata can be retrieved by using the various procedures in the DBMS_METADATA PL/SQL package? (Choose two.)

  A. report of invalidated objects in a schema
  B. report of statistics of an object in the database
  C. DDL for all object grants on a table in the database
  D. data definition language (DDL) for all objects in a schema
  C. DDL for all object grants on a table in the database
  D. data definition language (DDL) for all objects in a schema

• Question 104:

  DATA_FILES is a directory object that contains the DETAILS.TXT text file.

  You have the required permissions to access the directory object.

  You create a table using the following command:

  CREATE TABLE clob_tab(col2 CLOB);

  View the Exhibit and examine the PL/SQL block that you execute for loading the external text file into the table that currently has no rows. The PL/SQL block results in an error.

  

  What correction must be done to ensure the PL/SQL block executes successfully?

  A. The L_OUT variable must be initialized to an empty locator.
  B. The L_OUT variable has to be declared as a temporary LOB.
  C. The A_CLOB variable has to be declared as a temporary LOB.
  D. The clause RETURNING col2 INTO a_clob should be added to the INSERT statement to correctly initialize the locator.
  D. The clause RETURNING col2 INTO a_clob should be added to the INSERT statement to correctly initialize the locator.

• Question 105:

  Which two statements are true about the OPEN and FILEOPEN routines in the DBMS_LOB package? (Choose two.)

  A. OPEN can be used to open only internal LOBs in the indicated mode.
  B. FILEOPEN can be used to open only external LOBs in the indicated mode.
  C. OPEN can be used to open internal and external LOBs in the indicated mode.
  D. FILEOPEN can be used to open internal and external LOBs in the indicated mode.
  B. FILEOPEN can be used to open only external LOBs in the indicated mode.
  C. OPEN can be used to open internal and external LOBs in the indicated mode.

• Question 106:

  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?

  

  A. The FUNC1 function would be called inline because PRAGMA INLINE forces a specific call to be inlined.
  B. The FUNC1 function would be inlined because the value set for the PLSQL_CODE_TYPE parameter is set to NATIVE.
  C. The FUNC1 function would be called inline irrespective of the value set for the PLSQL_OPTIMIZE_LEVEL parameter.
  D. The FUNC1 function would not be called inline because the value for the PLSQL_OPTIMIZE_LEVEL parameter is set to a lower value.
  D. The FUNC1 function would not be called inline because the value for the PLSQL_OPTIMIZE_LEVEL parameter is set to a lower value.

• Question 107:

  Which two statements are true about the SQL Query Result Cache? (Choose two.)

  A. It can store the query results for temporary tables.
  B. It can be set at the system, session, or query level.
  C. It is used only across statements in the same session.
  D. Cached query results become invalid when the data accessed by the query is modified.
  B. It can be set at the system, session, or query level.
  D. Cached query results become invalid when the data accessed by the query is modified.

• Question 108:

  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.
  A. It provides function-level summaries.
  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.

• Question 109:

  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_HOME\RDBMS\ADMIN.
  C. Run the dbmshptab.sql script located at ORACLE_HOME\RDBMS\ADMIN.
  D. Grant READ and WRITE privileges to the current user on the PROFILE_DATA directory object.
  C. Run the dbmshptab.sql script located at ORACLE_HOME\RDBMS\ADMIN.

• Question 110:

  Which two conditions must be true for a PL/SQL function to be result cached? (Choose two.)

  A. It must be part of a package.
  B. It must be a pipelined table function.
  C. It must not be defined in an anonymous block.
  D. It must have at least one OUT or IN OUT parameter.
  C. It must not be defined in an anonymous block.
  D. It must have at least one OUT or IN OUT parameter.