Oracle Oracle Certifications 1Z0-148 Questions & Answers

• Question 201:

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

  A. Only local subroutines can be inlined.

  B. Inlining always reduces the execution time for a PL/SQL program unit.

  C. PLSQL_OPTIMIZE_LEVEL must be set to a value greater than or equal to 2.

  D. The PL/SQL programs that make use of relatively large helper subroutines are good candidates for inlining.

  Correct Answer: AC

• Question 202:

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

  A. Redundant SQL statements in PL/SQL code should be avoided.

  B. Implicit data type conversion in PL/SQL code can improve performance.

  C. Usage of the NOT NULL constraint in PL/SQL code can degrade performance.

  D. If you have one PL/SQL program unit instead of multiple smaller executable sections, performance can be improved.

  Correct Answer: AC

• Question 203:

  Which two statements are true about associative arrays and nested tables? (Choose two.)

  A. Only associative arrays can hold an arbitrary number of elements.

  B. Only associative arrays can use numbers and strings for subscripts.

  C. Both associative arrays and nested tables can hold an arbitrary number of elements.

  D. Both associative arrays and nested tables can use numbers and strings for subscripts.

  Correct Answer: BC

• Question 204:

  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.

  Correct Answer: DE

• Question 205:

  Which two queries' results cannot be cached? (Choose two.)

  A. queries having the GROUP BY clause

  B. queries having the ORDER BY clause

  C. the query on dictionary and temporary tables

  D. queries having SYSDATE and SYS_TIMESTAMP SQL functions

  Correct Answer: CD

• Question 206:

  To examine the dependencies between all PL/SQL objects and the tables and views they reference, you

  executed the following query as the user OE:

  SQL> SELECT owner || '.' || NAME refs_table

  , referenced_owner || '.' || referenced_name AS table_referenced

  FROM all_dependencies

  WHERE owner = USER

  AND TYPE IN ('PACKAGE', 'PACKAGE BODY','PROCEDURE', 'FUNCTION')

  AND referenced_type IN ('TABLE', 'VIEW')

  AND referenced_owner NOT IN ('SYS', 'SYSTEM')

  ORDER BY owner, NAME, referenced_owner, referenced_name;

  Which statement is true about the output of the query?

  A. It displays all PL/SQL code objects created by user OE that reference any table or view owned by other users except SYS and SYSTEM..

  B. It displays no rows because this query needs to be executed as the user SYS for required results.

  C. It displays all PL/SQL code objects that reference a table or view directly for all the users in the database.

  D. It displays only those PL/SQL code objects created by the user OE that reference a table or view created by the user SYS.

  Correct Answer: A

• Question 207:

  Examine the following line of code that is part of a PL/SQL application:

  stmt:='SELECT session_id FROM sessions WHERE ' || p_where_stmt;

  Identify a solution for preventing SQL injection in the above code.

  A. Replace P_WHERE_STMT with a bind variable.

  B. Do not use APIs that allow arbitrary query parameters to be exposed.

  C. Use the RESTRICT_REFERENCES clause in the PL/SQL subprogram that contains the code.

  D. Use DBMS_SQL to detect that the expression provided for P_WHERE_STMT is free from SQL injection.

  Correct Answer: B

• Question 208:

  You enabled PL/SQL tracing in a user session using the following command:

  SQL> EXECUTE DBMS_TRACE.SET_PLSQL_TRACE(DBMS_TRACE.TRACE_ALL_CALLS);

  View Exhibit1 to examine the output. After some time, the query produces a different result as shown in Exhibit2.

  What is the cause for the change?

  

  A. The FOO procedure has been executed more than once.

  B. The PLSQL_DEBUG parameter is set to FALSE for the user session.

  C. The FOO procedure has been compiled with the DEBUG option, and executed.

  D. Schema level statistics have been gathered by the database administrator (DBA).

  Correct Answer: C

• Question 209:

  Examine the structure of the PRINT_MEDIA table: Name Null? Type

  ADVT_ID NUMBER

  ADVT_SOURCE CLOB

  Examine the following PL/SQL block:

  DECLARE

  lobloc CLOB;

  buffer VARCHAR2(100);

  amount NUMBER;

  offset NUMBER :=1;

  BEGIN

  buffer :='This is the second line of a new document';

  amount := LENGTH(buffer);

  SELECT advt_source INTO lobloc FROM print_media WHERE advt_id=2 FOR UPDATE;

  DBMS_LOB.WRITE(lobloc,amount,offset,buffer);

  COMMIT;

  END;

  /

  What must be the value in the ADVT_SOURCE column for the above code to execute successfully?

  A. null

  B. an empty locator

  C. a non-NULL value

  D. either null or any non-NULL values

  Correct Answer: C

• Question 210:

  The database instance was recently started up. Examine the following parameter settings for the database instance:

  NAME TYPE VALUE

  .........

  result_cache_max_result integer 5

  result_cache_max_size big integer 0

  result_cache_mode string MANUAL

  result_cache_remote_expiration integer 0

  .........

  You reset the value for the result_cache_max_size parameter by issuing the following command:

  SQL> ALTER SYSTEM SET result_cache_max_size = 1056k SCOPE = BOTH;

  System altered.

  Which statement is true in this scenario?

  A. 1056 KB is allocated for the result cache and the result cache is enabled.

  B. 1056 KB is allocated for the result cache, but the result cache is disabled.

  C. The results for only the queries that have the RESULT_CACHE hint are cached.

  D. The results for all the queries except those having the NO_RESULT_CACHE hint are cached.

  Correct Answer: B