Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 121:

  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.

  Correct Answer: D

• Question 122:

  View the Exhibit and examine the PL/SQL code.

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

  

  A. It executes successfully and displays 101 and 200000 values.

  B. The ASSIGN_VAL function generates an error during compilation because nested tables cannot be returned by functions.

  C. The SELECT statement generates an error because the nested table has not been initialized in the ASSIGN_VAL function.

  D. The ASSIGN_VAL function generates an error during compilation because the EXTEND method cannot be used with nested tables.

  Correct Answer: C

• Question 123:

  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.

  Correct Answer: BD

• Question 124:

  Examine the following parameter values for a session:

  PLSQL_CODE_TYPE = NATIVE

  PLSQL_OPTIMIZE_LEVEL = 2

  Which two statements are true in this scenario? (Choose two.)

  A. The compiler automatically inlines subprograms even if you specify that they not be inlined.

  B. The compiler optimizes PL/SQL programs by elimination of unnecessary computations and exceptions.

  C. PL/SQL statements in a PL/SQL program unit are compiled into machine-readable code, and stored in the SYSTEM tablespace.

  D. PL/SQL statements in a PL/SQL program unit are compiled into an intermediate form, machine-readable code, which is stored in the database dictionary.

  Correct Answer: BC

• Question 125:

  You created a PL/SQL function with the RESULT_CACHE and RELIES_ON clauses. In which scenarios is the cached result bypassed? (Choose all that apply.)

  A. when the size for the memory allocated for the result cache is increased

  B. when the function is executed in a session frequently with the same parameter value

  C. when the database administrator has disabled the use of the result cache during application patching

  D. when a data manipulation language (DML) statement is executed in a session on a table or view that was specified in the RELIES_ON clause of a result-cached function

  Correct Answer: CD

• Question 126:

  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. FOR UPDATE must be added to the SELECT statement.

  D. The GETLENGTH routine must be replaced with the LENGTH built-in function in WRITEAPPEND.

  Correct Answer: C

• Question 127:

  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.

  Correct Answer: BC

• Question 128:

  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

  Correct Answer: AB

• Question 129:

  The result cache is enabled for the database instance.

  Examine the following code for a PL/SQL function:

  CREATE OR REPLACE FUNCTION get_hire_date (emp_id NUMBER) RETURN

  VARCHAR

  RESULT_CACHE RELIES_ON (HR.EMPLOYEES)

  IS

  date_hired DATE;

  BEGIN

  SELECT hire_date INTO date_hired

  FROM HR.EMPLOYEES

  WHERE EMPLOYEE_ID = emp_id;

  RETURN TO_CHAR(date_hired);

  END;

  Which statement is true in this scenario?

  A. If sessions have different NLS_DATE_FORMAT settings, cached results have different formats.

  B. The function results are not cached because the query used in the function returns the DATE data type.

  C. If sessions have different NLS_DATE_FORMAT settings, cached results have same formats because the function's return type is VARCHAR.

  D. If a function is executed with same argument value but different NLS_DATE_FORMAT for the session, the cached result is overwritten with the new function result.

  Correct Answer: A

• Question 130:

  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.

  Correct Answer: AD