Oracle 1Z0-146 Online Practice Questions and Exam Preparation

Oracle 1Z0-146 Online Questions & Answers

• Question 1:

  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.
  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.

• Question 2:

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

  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.
  B. Remove redundant SQL statements in the procedures.
  C. Avoid the NOT NULL constraint when declaring the variable.

• Question 4:

  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.
  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..

• Question 5:

  The database instance was started up with the following initialization

  parameter values:

  MEMORY_TARGET = 500M

  RESULT_CACHE_MODE = FORCE

  RESULT_CACHE_MAX_SIZE = 0

  After the database startup, to enable the result cache, you issued the following command:

  SQL> ALTER SYSTEM SET result_cache_max_size = 2M SCOPE = MEMORY;

  Which is the effect of this command?

  A. The query result cache is enabled and 2 MB of the memory target is allocated to the result cache.
  B. The query result cache is enabled and 0.25% of the memory target is allocated to the result cache.
  C. The command produces an error because the database instance is started up with the RESULT_CACHE_MAX_SIZE parameter set to 0.
  D. The query result cache is not enabled because the database instance is started up with the RESULT_CACHE_MAX_SIZE parameter set to 0.
  D. The query result cache is not enabled because the database instance is started up with the RESULT_CACHE_MAX_SIZE parameter set to 0.

• Question 6:

  Which guidelines should be considered when designing and using cursors in a PL/SQL block? ? (Choose all that apply.)

  A. When fetching from a cursor, fetch into a record.
  B. Use parameters with cursors so that the result set for the cursor is not tied to a specific variable in a program.
  C. Use the %NOTFOUND attribute in combination with the SELECT INTO statement to check for non existent values.
  D. Whenever possible, explicitly declare the cursor and use the OPEN, FETCH and CLOSE statements to manipulate the cursor instead of using cursor FOR loop.
  E. When using data manipulation language statements, (DML) reference a SQL cursor attribute immediately after the DML statement executes in the same block.
  A. When fetching from a cursor, fetch into a record.
  B. Use parameters with cursors so that the result set for the cursor is not tied to a specific variable in a program.
  E. When using data manipulation language statements, (DML) reference a SQL cursor attribute immediately after the DML statement executes in the same block.

• Question 7:

  Which two guidelines should be considered when designing and using cursors in a PL/SQL block? (Choose two.)

  A. When fetching from a cursor, fetch into a record.
  B. When fetching from a cursor, fetch required values into individually declared variables.
  C. Whenever possible, explicitly declare the cursor and use the OPEN, FETCH, and CLOSE statements to manipulate the cursor instead of using the cursor FOR loop.
  D. Whenever possible, use the cursor FOR loop instead of explicitly declaring the cursor and using the OPEN, FETCH, and CLOSE statements to manipulate the cursor.
  A. When fetching from a cursor, fetch into a record.
  D. Whenever possible, use the cursor FOR loop instead of explicitly declaring the cursor and using the OPEN, FETCH, and CLOSE statements to manipulate the cursor.

• Question 8:

  Identify three guidelines for the DBMS_ASSERT package. (Choose three.)

  A. Prefix all calls to DBMS_ASSERT with the SYS schema name.
  B. Embed DBMS_ASSERT verification routines inside the injectable string.
  C. Escape single quotes when you use the ENQUOTE_LITERAL procedure.
  D. Define and raise exceptions explicitly to handle DBMS_ASSERT exceptions.
  E. Prefix all calls to DBMS_ASSERT with a schema name that owns the subprogram that uses the DBMS_ASSERT package.
  A. Prefix all calls to DBMS_ASSERT with the SYS schema name.
  C. Escape single quotes when you use the ENQUOTE_LITERAL procedure.
  D. Define and raise exceptions explicitly to handle DBMS_ASSERT exceptions.

• Question 9:

  You created a procedure as follows:

  CREATE OR REPLACE PROCEDURE query_prod(twhr VARCHAR2)

  IS

  stmt VARCHAR2(100);

  pname VARCHAR2(20);

  BEGIN

  stmt:='SELECT product_name FROM products WHERE product_id=:2';

  EXECUTE IMMEDIATE stmt INTO pname USING twhr;

  DBMS_OUTPUT.PUT_LINE(pname);

  END;

  /

  View the Exhibit to examine the structure of PRODUCTS table.

  

  Which statement is true about the procedure?

  A. It produces an error when invoked.
  B. It can be invoked only from a PL/SQL block.
  C. It reduces the chances of SQL injection by using bind arguments.
  D. The values for bind arguments remain persistent in the session after the execution of the procedure.
  C. It reduces the chances of SQL injection by using bind arguments.

• Question 10:

  Examine the structure of the PRODUCTS table. Name Null? Type

  PRODUCT_ID NOT NULL NUMBER(6)

  PRODUCT_NAME VARCHAR2(50)

  CATEGORY_ID NUMBER(2)

  SUPPLIER_ID NUMBER(6)

  LIST_PRICE NUMBER(8,2)

  View the Exhibit and examine the PL/SQL block.

  On execution, the PL/SQL block generates the following error:

  

  ORA-01001: invalid cursor What could be the reason?

  A. Both the cursor variable arguments should have been passed in IN mode.
  B. The contents of one cursor variable cannot be assigned to another cursor variable using the := operator.
  C. The CLOSE statement closes both the cursor variables, therefore the last FETCH statement cannot execute.
  D. The name of the cursor variables defined and the name of the cursor variables passed as arguments must be the same.
  C. The CLOSE statement closes both the cursor variables, therefore the last FETCH statement cannot execute.