Oracle 1Z0-146 Online Practice Questions and Exam Preparation

Oracle 1Z0-146 Online Questions & Answers

• Question 41:

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

  

  Examine the following PL/SQL block for storing the salary of all sales representatives from the EMPLOYEES table in an associative array:

  1 DECLARE

  2 emp_cv SYS_REFCURSOR;

  3 TYPE list IS TABLE OF emp_cv;

  4 sals list;

  5 BEGIN

  6 OPEN emp_cv FOR SELECT salary FROM employees

  7 WHERE job_id = 'SA_REP';

  8 FETCH emp_cv BULK COLLECT INTO sals;

  9 CLOSE emp_cv;

  10 END;

  What should you correct in the above code to ensure that it executes successfully?

  A. Replace EMP_CV in line 3 with employees.salary%TYPE.
  B. Replace line 2 with TYPE refcur IS REF CURSOR; emp_cv refcur;.
  C. Replace BULK COLLECT in line 8 with the OPEN, FETCH, LOOP, and CLOSE statements.
  D. Replace line 2 with TYPE refcur IS REF CURSOR RETURN employees.salary%TYPE; emp_cv refcur;.
  A. Replace EMP_CV in line 3 with employees.salary%TYPE.

• Question 42:

  You have an OE_ACCESS_ORDERS_POLICY security policy implemented on the ORDERS table in the OE schema. The user sessions are able to access only the desired rows. The database administrator (DBA) uses the following

  command:

  SQL> EXECUTE

  DBMS_RLS.ENABLE_POLICY('OE','ORDERS','OE_ORDERS_ACCESS_POLICY',FALSE);

  Which statement is true about user sessions that are connected currently?

  A. The security policy remains in effect till the end of the current session.
  B. The subsequent queries on the ORDERS table produce an ORA-01031: insufficient privileges error.
  C. The subsequent queries on the ORDERS table within the current session are not controlled by the security policy.
  D. The subsequent queries on the ORDERS table produce an ORA-28112: failed to execute policy function error.
  C. The subsequent queries on the ORDERS table within the current session are not controlled by the security policy.

• Question 43:

  The database instance was started up using the automatic memory management feature. No value was set for the RESULT_CACHE_MAX_SIZE parameter. Examine the following initialization parameter settings for your database: MEMORY_TARGET = 500M RESULT_CACHE_MODE = MANUAL You execute a query by using the result_cache hint. Which statement is true in this scenario?

  A. The query results are not stored because no memory is allocated for the result cache.
  B. The query results are stored and 0.5% of the memory target is allocated to the result cache.
  C. The query results are stored and 0.25% of the memory target is allocated to the result cache.
  D. The query results are not stored because the RESULT_CACHE_MODE parameter is not set to FORCE.
  C. The query results are stored and 0.25% of the memory target is allocated to the result cache.

• Question 44:

  Examine the settings for a user session given below:

  RESULT_CACHE_MODE= FORCE

  What would be the implications of this setting on query execution? (Choose all that apply.)

  A. All query results are stored in the result cache if possible.
  B. Query results that are bigger than the available space in the result cache are not cached.
  C. Query results are stored only when you explicitly use the /*+ result_cache */ hint in your query.
  D. Query results are stored even when you explicitly use the /*+ no_result_cache */ hint in your query.
  A. All query results are stored in the result cache if possible.
  B. Query results that are bigger than the available space in the result cache are not cached.

• Question 45:

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

• Question 46:

  Match the following external C procedure components with their descriptions:

  1.

  External procedure a. a process that starts the extproc process

  2.

  Shared library b. a session-specific process that executes the external procedure

  3.

  Alias library c. schema object that represents the operating system (OS) shared library

  4.

  The extproc process d. operating system file that stores the external procedure

  5.

  Listener process e. a unit of code written in C

  A. 1-e; 2-d; 3-c; 4-b; 5-a
  B. 1-c; 2-d; 3-e; 4-b; 5-a
  C. 1-e; 2-c; 3-d; 4-b; 5-a
  D. 1-a; 2-d; 3-e; 4-c; 5-b
  A. 1-e; 2-d; 3-c; 4-b; 5-a

• Question 47:

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

  You created a PL/SQL subprogram that successfully invokes an external C procedure. After a while, the database administrator (DBA) drops the alias library schema object. The shared library exists in the system. Which statement is true in this scenario?

  A. The corresponding shared library is also removed from the system.
  B. PL/SQL subprograms can be used to invoke the external C procedure.
  C. The existing extproc process is terminated and a new extproc is started.
  D. The PL/SQL subprogram that depends on the external C program becomes invalid.
  D. The PL/SQL subprogram that depends on the external C program becomes invalid.

• Question 49:

  Examine the structure of the DEPARTMENTS table.

  

  Name Null? Type

  DEPARTMENT_ID NOT NULL NUMBER(4)

  DEPARTMENT_NAME NOT NULL VARCHAR2(30)

  LOCATION_ID NUMBER(4)

  View the Exhibit and examine the code that you plan to use for creating a package to obtain the

  details of an employee using a host variable on the client side.

  In SQL*Plus, you plan to use the following commands:

  SQL> VARIABLE x REFCURSOR

  SQL> EXECUTE emp_data.get_emp(195,:x)

  SQL> PRINT x

  Which statement is true about the above scenario?

  A. The package executes successfully and passes the required data to the host variable.
  B. The package specification gives an error on compilation because cursor variable types cannot be defined in the specification.
  C. The package specification gives an error on compilation because the cursor variable parameter was specified before you defined it.
  D. The package executes successfully, but does not pass the required data to the host variable because the cursor is closed before the PRINT statement runs.
  D. The package executes successfully, but does not pass the required data to the host variable because the cursor is closed before the PRINT statement runs.

• Question 50:

  The PLSQL_OPTIMIZE_LEVEL parameter is set to 2 for the session.

  Examine the section of code given:

  FUNCTION p2 (p boolean) return PLS_INTEGER IS ...

  FUNCTION p2 (x PLS_INTEGER) return PLS_INTEGER IS

  ... ...

  PRAGMA INLINE(p2, 'YES');

  x := p2(true) + p2(3);

  ...

  Which statement is true about the INLINE pragma procedure calls?

  A. Only the call to the P2 function with BOOLEAN as the argument is inlined.
  B. INLINE pragma affects both the functions named P2 and is called inline.
  C. Only the call to the P2 function with PLS_INTEGER as the argument is inlined.
  D. None of the functions are inlined because inlining is not supported for overloaded functions.
  B. INLINE pragma affects both the functions named P2 and is called inline.