Oracle 1Z0-146 Online Practice Questions and Exam Preparation

Oracle 1Z0-146 Online Questions & Answers

• Question 71:

  Which two statements are true about REF CURSOR types? (Choose two.)

  A. REF CURSOR types cannot be defined inside a package.
  B. SYS_REFCURSOR can be used to declare cursor variables in stored procedures and functions.
  C. A REF CURSOR return type can be declared using %TYPE, or %ROWTYPE, or a userdefined record.
  D. Only a weak REF CURSOR type can be used as a formal parameter of a stored procedure or function.
  B. SYS_REFCURSOR can be used to declare cursor variables in stored procedures and functions.
  C. A REF CURSOR return type can be declared using %TYPE, or %ROWTYPE, or a userdefined record.

• Question 72:

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

  Identify two strategies against SQL injection. (Choose two.)

  A. Using parameterized queries with bind arguments.
  B. Use subprograms that are run with the definer's right.
  C. Use RESTRICT_REFERENCE clauses in functions that use dynamic SQLs.
  D. Validate user inputs to functions that use dynamic SQLs built with concatenated values.
  A. Using parameterized queries with bind arguments.
  D. Validate user inputs to functions that use dynamic SQLs built with concatenated values.

• Question 74:

  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.
  A. Redundant SQL statements in PL/SQL code should be avoided.
  C. Usage of the NOT NULL constraint in PL/SQL code can degrade performance.

• Question 75:

  Which two statements are true about the usage of the DBMS_DESCRIBE.DESCRIBE_PROCEDURE procedure? (Choose two.)

  A. You can describe remote objects.
  B. You can describe anonymous PL/SQL blocks.
  C. You can describe a stored procedure, stored function, packaged procedure, or packaged function.
  D. You can obtain information about the position, name, and data type of the arguments of a procedure.
  C. You can describe a stored procedure, stored function, packaged procedure, or packaged function.
  D. You can obtain information about the position, name, and data type of the arguments of a procedure.

• Question 76:

  View the Exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

  

  Examine the PL/SQL block that you execute to find the average salary for employees in the 'Sales' department:

  DECLARE

  TYPE emp_sal IS TABLE OF employees.salary%TYPE INDEX BY VARCHAR2(20);

  v_emp_sal emp_sal;

  PROCEDURE get_sal(p_dept_name VARCHAR2, p_arr OUT emp_sal) IS

  BEGIN

  SELECT AVG(salary) INTO p_arr(p_dept_name)

  FROM employees WHERE department_id=

  (SELECT department_id FROM departments

  WHERE department_name=p_dept_name);

  END get_sal;

  BEGIN

  get_sal('Sales',v_emp_sal);

  DBMS_OUTPUT.PUT_LINE( v_emp_sal('Sales'));

  END;

  /

  What is the outcome?

  A. It executes successfully and gives the correct output.
  B. It generates an error because the associative array definition is not valid.
  C. It generates an error because an associative array cannot be passed to a procedure in OUT mode.
  D. It generates an error because an associative array cannot be used with the SELECT INTO statement.
  A. It executes successfully and gives the correct output.

• Question 77:

  You executed the following command:

  SQL> ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';

  You create a new package called PACK1. View Exhibit1 to examine the PL/SQL code for the PACK1 package specification and body.

  You issue the following query to see all unique identifiers with a name, such as %1:

  SQL> SELECT NAME, SIGNATURE, TYPE

  FROM USER_IDENTIFIERS

  WHERE NAME LIKE '%1' AND USAGE='DECLARATION'

  ORDER BY OBJECT_TYPE, USAGE_ID;

  View Exhibit2 to examine the output of the query.

  

  Which two statements are true about the output of the query? (Choose two.)

  A. The SIGNATURE column has a unique value for an identifier except for identifiers with the same name.
  B. The TYPE column has the value of packages, function or procedures, object types, PL/SQL types, triggers, or exceptions.
  C. The query shows the output for only those identifiers for PL/SQL objects, which are created by the user and are compiled after the ALTER SESSION command.
  D. The ALTER SESSION command automatically collects identifier data and the query shows the output for all the identifiers for PL/SQL objects, which are created by the user.
  B. The TYPE column has the value of packages, function or procedures, object types, PL/SQL types, triggers, or exceptions.
  C. The query shows the output for only those identifiers for PL/SQL objects, which are created by the user and are compiled after the ALTER SESSION command.

• Question 78:

  A procedure is created in the SYS schema to allow users to change the password as follows:

  CREATE OR REPLACE

  PROCEDURE change_password(p_username VARCHAR2 DEFAULT NULL,

  p_new_password VARCHAR2 DEFAULT NULL)

  IS

  v_sql_stmt VARCHAR2(500);

  BEGIN

  v_sql_stmt := 'ALTER USER '||p_username ||' IDENTIFIED BY '

  || p_new_password;

  EXECUTE IMMEDIATE v_sql_stmt;

  END change_password;

  The SYS user has granted EXECUTE privilege on the procedure to the OE user. But OE is able to change the password for SYS by using this procedure. How would you protect this?

  A. by using the procedure as part of a PL/SQL package
  B. by using a bind argument with dynamic SQL in the procedure
  C. by using AUTHID DEFINER in the procedure to implement the definer's right
  D. by using AUTHID CURRENT_USER in the procedure to implement the invoker's right
  D. by using AUTHID CURRENT_USER in the procedure to implement the invoker's right

• Question 79:

  Which three actions can be performed by using the DBMS_ASSERT package to prevent SQL injection? (Choose three.)

  A. Detect a wrong user.
  B. Check input string length.
  C. Verify qualified SQL names.
  D. Validate TNS connect strings.
  E. Verify an existing schema name.
  F. Enclose string literals within double quotation marks.
  C. Verify qualified SQL names.
  E. Verify an existing schema name.
  F. Enclose string literals within double quotation marks.

• Question 80:

  You designed a CardValidation.java Java source file. You also have the corresponding CardValidation.class file. As part of invoking a Java class method, you executed this command at the command prompt:

  loadjava -user oe/oe CardValidation.java

  Which statement is true about the command?

  A. It loads the Java code into the database.
  B. It publishes Java methods in CardValidation.java.
  C. It loads the metadata related to the Java class file into the database.
  D. It loads the Java class file into the Java pool in the database instance.
  A. It loads the Java code into the database.