Oracle 1Z0-146 Online Practice Questions and Exam Preparation

Oracle 1Z0-146 Online Questions & Answers

• Question 21:

  View the Exhibit and examine the structure of the EMPLOYEES table.

  

  Examine the following PL/SQL block:

  DECLARE

  TYPE EmpList

  IS VARRAY(2) OF employees.employee_id%TYPE NOT NULL;

  v_employees EmpList := EmpList();

  BEGIN

  DBMS_OUTPUT.PUT_LINE(v_employees.COUNT);

  v_employees.EXTEND;

  v_employees(1) := 30;

  END;

  /

  Which statement is true about the outcome on executing the above PL/SQL block?

  A. It executes successfully and displays the value 2.
  B. It executes successfully and displays the value 0.
  C. It generates an error because EXTEND cannot be used for varrays.
  D. It generates an error because the declaration of the varray is not valid.
  B. It executes successfully and displays the value 0.

• Question 22:

  Which two statements are true about the query results stored in the query result cache? (Choose two.)

  A. If any of the tables used to build a query is modified by an ongoing transaction in the current session, the query result is not cached.
  B. A query result based on a read-consistent snapshot of data that is older than the latest committed version of the data is not cached.
  C. Adding the RESULT_CACHE hint to inline views enables optimizations between the outer query and the inline view, and the query result is cached.
  D. A query result for a query that has a bind variable is stored in the cache and is reused if the query is equivalent even when the bind variable has a different value.
  A. If any of the tables used to build a query is modified by an ongoing transaction in the current session, the query result is not cached.
  B. A query result based on a read-consistent snapshot of data that is older than the latest committed version of the data is not cached.

• Question 23:

  Examine the PL/SQL code for the GET_TABLE_MD function given below:

  CREATE OR REPLACE FUNCTION get_table_md RETURN CLOB IS

  h NUMBER;

  th NUMBER;

  doc CLOB;

  BEGIN

  h := DBMS_METADATA.OPEN('TABLE');

  DBMS_METADATA.SET_FILTER(h,'SCHEMA','HR');

  DBMS_METADATA.SET_FILTER(h,'NAME','TIMECARDS');

  th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL');

  doc := DBMS_METADATA.FETCH_CLOB(h);

  DBMS_METADATA.CLOSE(h);

  RETURN doc;

  END;

  Which statement is true about the compilation and execution of the function?

  A. The function retrieves the metadata in Extensible Markup Language (XML) format for creating the TIMECARDS table in the HR schema.
  B. The compilation produces an error because DBMS_METADATA.SET_FILTER(h,'SCHEMA','HR')is not placed in the correct order.
  C. The function retrieves the metadata as a data definition language (DDL) statement for creating the TIMECARDS table in the HR schema.
  D. The execution of the function produces an error because multiple objects are fetched and DBMS_METADATA.FETCH_CLOB is not called in a LOOP.
  C. The function retrieves the metadata as a data definition language (DDL) statement for creating the TIMECARDS table in the HR schema.

• Question 24:

  Which statements are true about temporary LOBs? (Choose all that apply.)

  A. They can be created only for CLOB and NCLOB data.
  B. They can be accessed only by the user who creates them.
  C. They generate more redo information than persistent LOBs.
  D. They exist for the duration of the session in which they are created.
  E. They are stored temporarily in the default tablespace of the user who creates them.
  B. They can be accessed only by the user who creates them.
  D. They exist for the duration of the session in which they are created.

• Question 25:

  Which two are major approaches that can be used to reduce the SQL injection by limiting user input? (Choose two.)

  A. Restrict users accessing specified web page.
  B. Use NUMBER data type if only positive integers are needed.
  C. Use dynamic SQL and construct it through concatenation of input values.
  D. In PL/SQL API, expose only those routines that are intended for customer use.
  A. Restrict users accessing specified web page.
  D. In PL/SQL API, expose only those routines that are intended for customer use.

• Question 26:

  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.
  A. If sessions have different NLS_DATE_FORMAT settings, cached results have different formats.

• Question 27:

  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 not inlined in the section commented as Call 1.
  B. The calls to the P1 procedure might be inlined in the section commented as Call 2.
  C. The calls to the P1 procedure are inlined in both the sections commented as Call 1 and Call 2.
  D. The calls to the P1 procedure are never inlined in both the sections commented as Call 1 and Call 2.
  A. The calls to the P1 procedure are not inlined in the section commented as Call 1.
  B. The calls to the P1 procedure might be inlined in the section commented as Call 2.

• Question 28:

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

  A. Only varrays must have consecutive numbers as subscripts.
  B. Only nested tables can be used as column types in database tables.
  C. Both nested tables and varrays must have consecutive numbers as subscripts.
  D. Both nested tables and varrays can be used as column types in database tables.
  A. Only varrays must have consecutive numbers as subscripts.
  D. Both nested tables and varrays can be used as column types in database tables.

• Question 29:

  View Exhibit1 and examine the structure of the EMPLOYEES table.

  

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

  The PL/SQL block fails to execute.

  What could be the reason?

  A. Nested tables cannot be returned by a function.
  B. The NEWNAMES nested table has not been initialized.
  C. The assignment operator cannot be used to transfer all the element values from GROUP1 to GROUP2.
  D. The third element of OLDNAMES cannot be assigned to the third element of GROUP1 because they are of inconsistent data types.
  E. LAST_NAME values cannot be assigned to the V_LAST_NAMES nested table because local collection types are not allowed in SQL statements.
  E. LAST_NAME values cannot be assigned to the V_LAST_NAMES nested table because local collection types are not allowed in SQL statements.

• Question 30:

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