Oracle 1Z0-146 Online Practice Questions and Exam Preparation

Oracle 1Z0-146 Online Questions & Answers

• Question 111:

  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 112:

  When do you use static SQL as a technique for avoiding SQL injection?

  A. when the WHERE clause values are unknown
  B. when the code contains data definition language (DDL) statements
  C. when all Oracle identifiers are known at the time of code compilation
  D. when the SET clause values are unknown at the time of code compilation
  C. when all Oracle identifiers are known at the time of code compilation

• Question 113:

  View the Exhibit and examine the procedure to create a trigger name based on the table name supplied to the procedure.

  

  Which three statements are appropriate for protecting the code in the procedure from SQL injection? (Choose three.)

  A. Explicitly validate the identifier length limit.
  B. Add AUTHID DEFINER to the definition of the procedure.
  C. Use PRAGMA RESTRICT_REFERENCES in the procedure.
  D. Filter out control characters in user-supplied identifier names.
  E. Use the object ID of the table from the data dictionary to build the trigger name.
  A. Explicitly validate the identifier length limit.
  D. Filter out control characters in user-supplied identifier names.
  E. Use the object ID of the table from the data dictionary to build the trigger name.

• Question 114:

  You execute the following command in the user session: SQL> ALTER SESSION SET PLSQL_DEBUG=true; Which statement is true about the effect of the command?

  A. All PL/SQL blocks that are executed subsequently in the session are traced.
  B. It enables all PL/SQL blocks that are compiled subsequently in the session for tracing.
  C. Only anonymous PL/SQL blocks that are executed subsequently in the session are traced.
  D. It enables only named PL/SQL blocks that are executed subsequently in the session for tracing.
  B. It enables all PL/SQL blocks that are compiled subsequently in the session for tracing.

• Question 115:

  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;

  You notice that results for the functions are not used effectively. What do you recommend for better utilization of the result cache? (Choose all that apply.)

  A. Set the RESULT_CACHE_MODE parameter to FORCE.
  B. Increase the value for the RESULT_CACHE_MAX_SIZE parameter.
  C. Add a format mask parameter, such as RETURN TO_CHAR(date_hired, fmt) to GET_HIRE_DATE.
  D. Change the return type of GET_HIRE_DATE to DATE and have each session invoke the TO_CHAR function.
  C. Add a format mask parameter, such as RETURN TO_CHAR(date_hired, fmt) to GET_HIRE_DATE.
  D. Change the return type of GET_HIRE_DATE to DATE and have each session invoke the TO_CHAR function.

• Question 116:

  Examine the following command to create the table EMPLOYEES_TEMP and the PL/SQL block.

  CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL,

  deptid NUMBER(6) CONSTRAINT c_emp_deptid CHECK (deptid BETWEEN 100 AND 200),

  salary Number(8),

  deptname VARCHAR2(30) DEFAULT 'Sales')

  /

  DECLARE

  SUBTYPE v_emprec_subtype IS employees_temp%ROWTYPE;

  v_emprec v_emprec_subtype;

  BEGIN

  v_emprec.empid := NULL; v_emprec.salary := 10000.002;

  v_emprec.deptid := 50;

  DBMS_OUTPUT.PUT_LINE('v_emprec.deptname: ' || v_emprec.deptname);

  END;

  /

  Which statements are true about the above PL/SQL block? (Choose two.)

  A. V_EMPREC.DEPTNAME would display a null value because the default value is not inherited.
  B. Assigning null to V_EMPREC.EMPID would generate an error because the null constraint is inherited.
  C. Assigning the value 1000.002 to V_EMPREC.SALARY would generate an error because of the decimal.
  D. Assigning the value 50 to V_EMPREC.DEPTID would work because the check constraint is not inherited.
  A. V_EMPREC.DEPTNAME would display a null value because the default value is not inherited.
  D. Assigning the value 50 to V_EMPREC.DEPTID would work because the check constraint is not inherited.

• Question 117:

  You set RESULT_CACHE_MAX_SIZE to a nonzero value to enable result caching. You executed the following command to check the status for the result cache:, SQL> select dbms_result_cache.status() from dual;

  You receive the following output:

  DBMS_RESULT_CACHE.STATUS()

  DISABLED

  Identify the reason for the output.

  A. The RESULT_CACHE_MODE parameter is set to FORCE.
  B. The RESULT_CACHE_MODE parameter is set to MANUAL.
  C. The database instance is not able to allocate memory for the result cache.
  D. The database instance was started with the RESULT_CACHE_MAX_SIZE parameter set to 0.
  D. The database instance was started with the RESULT_CACHE_MAX_SIZE parameter set to 0.

• Question 118:

  Examine the code in the following PL/SQL block:

  DECLARE

  TYPE NumList IS TABLE OF INTEGER;

  List1 NumList := NumList(11,22,33,44);

  BEGIN

  List1.DELETE(2);

  DBMS_OUTPUT.PUT_LINE

  ( 'The last element# in List1 is ' || List1.LAST ||

  ' and total of elements is '||List1.COUNT);

  List1.EXTEND(4,3);

  END;

  /

  Which two statements are true about the above code? (Choose two.)

  A. LAST and COUNT give different values.
  B. LAST and COUNT give the same values.
  C. The four new elements that are added contain the value 33.
  D. The four new elements that are added contain the value 44.
  A. LAST and COUNT give different values.
  C. The four new elements that are added contain the value 33.

• Question 119:

  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
  A. DDL report for all objects dependent on a table
  D. data definition language (DDL) report for all the tables in a schema

• Question 120:

  You issue this command to create a table called LOB_STORE:

  CREATE TABLE lob_store

  (lob_id NUMBER(3),

  photo BLOB DEFAULT EMPTY_BLOB(),

  cv CLOB DEFAULT NULL,

  ext_file BFILE DEFAULT NULL)

  /

  What is the outcome?

  A. The table is created successfully.
  B. It generates an error because DEFAULT cannot be set to EMPTY_BLOB() during table creation.
  C. It generates an error because DEFAULT cannot be set to null for a CLOB column during table creation.
  D. It generates an error because DEFAULT cannot be set to null for a BFILE column during table creation.
  A. The table is created successfully.