Oracle 1Z0-144 Online Practice Questions and Exam Preparation

Oracle 1Z0-144 Online Questions & Answers

• Question 91:

  View the Exhibit and examine the package code created by SCOTT. The execute privilege on this package is granted to GREEN.

  

  Examine the following sequence of commands issued by SCOTT:

  

  What is the outcome?

  A. SCOTT'S session displays 5, and then 0, GREEN'S session displays 0.
  B. SCOTT'S session displays 5, and then 0, GREEN'S session displays 5.
  C. SCOTT'S session displays 5, and then 5, again, GREEN'S session displays 0.
  D. SCOTT'S session displays 5, and then 5, again; GREEN'S session displays 5.
  A. SCOTT'S session displays 5, and then 0, GREEN'S session displays 0.

• Question 92:

  ORDER_TOTAL is a column in the ORDERS table with the data type and size as NUMBER (8, 2). Examine the following code:

  

  Which statement is correct about the above code?

  A. It gives an error in line 3.
  B. It gives an error in line 4.
  C. It gives an error in line 6.
  D. It executes successfully and displays the output.
  B. It gives an error in line 4.

• Question 93:

  View Exhibit 1 and examine the structure of the product table.

  

  View Exhibit 2 and examine the procedure you created. The procedure uses the prod id to determine whether the list price is within a given range.

  

  You then create the following trigger on the product table.

  CREATE OR REPLACE TRIGGER check_price__trg BEFORE INSERT OR UPDATE OF prod_id, prod_list_price ON product FOR EACH ROW WHEN (nev.prod_id <> NVX(old.prod_id,0) OR New.prod__list_price <> NVL(old.prod_list_price, 0) ) BEGIN check_price (: new.prod_id) ; END /

  Examine the following update command for an existing row in the product table.

  SQL> UPDATE produce SET prod_list_price = 10 WHERE prod_id=115;

  Why does it generate an error?

  A. Because the procedure call in the trigger is not valid
  B. Because the condition specified in the when clause is not valid
  C. Because both the procedure and trigger access the same table
  D. Because the WHEN clause cannot be used with a row-level trigger
  E. Because the column list specified with UPDATE in the trigger is not valid
  C. Because both the procedure and trigger access the same table

• Question 94:

  Which two statements correctly differentiate functions and procedures? (Choose two.)

  A. A function can be called only as part of a SQL statement, whereas a procedure can be called only as a PL/SQL statement.
  B. A function must return a value to the calling environment, whereas a procedure can return zero or more values to its calling environment.
  C. A function can be called as part of a SQL statement or PL/SQL expression, whereas a procedure can be called only as a PL/SQL statement.
  D. A function may return one or more values to the calling environment, whereas a procedure must return a single value to its calling environment.
  B. A function must return a value to the calling environment, whereas a procedure can return zero or more values to its calling environment.
  C. A function can be called as part of a SQL statement or PL/SQL expression, whereas a procedure can be called only as a PL/SQL statement.

• Question 95:

  In which of the following scenarios would you recommend using associative arrays?

  A. When you want to retrieve an entire row from a table and perform calculations
  B. When you know the number of elements in advance and the elements are usually accessed sequentially
  C. When you want to create a separate lookup table with multiple entries for each row of the main table, and access it through join queries
  D. When you want to create a relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked.
  D. When you want to create a relatively small lookup table, where the collection can be constructed in memory each time a subprogram is invoked.

• Question 96:

  Examine the following code:

  

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

  A. It can be used only in a parallelized query.
  B. It can be used in both a parallelized query and a parallelized DML statement.
  C. It can be used only in a parallelized data manipulation language (DML) statement.
  D. It can have a separate copy run in each of the multiple processes when called from a SQL statement that is run in parallel.
  E. It requires a PRAGMA RESTRICT_REFERENCES declaration with RNDS, WNDS, RNPS, and WNPS specified in order to use parallel optimization.
  B. It can be used in both a parallelized query and a parallelized DML statement.
  D. It can have a separate copy run in each of the multiple processes when called from a SQL statement that is run in parallel.

• Question 97:

  You create a procedure to handle the processing of bank current accounts which rolls back payment transactions if the overdraft limit is exceeded.

  The procedure should return an "error" condition to the caller in a manner consistent with other Oracle server errors.

  Which construct should be used to handle this requirement?

  A. The SQLERRM function
  B. The PRAGMA EXCEPTION_INIT function
  C. The RAISE_APPLICATION_ERROR procedure
  D. A user-defined exception used with a RAISE statement
  C. The RAISE_APPLICATION_ERROR procedure

• Question 98:

  Which statements are true about PL/SQL procedures? (Choose two.)

  A. Users with definer's rights who are granted access to a procedure that updates a table must be granted access to the table itself.
  B. Reuse of parsed PL/SQL code that becomes available in the shared SQL area of the server avoids the parsing overhead of SQL statements at run time.
  C. Depending on the number of calls, multiple copies of the procedure are loaded into memory for execution by multiple users to speed up performance.
  D. A PL/SQL procedure executing on the Oracle database can call an external procedure or function that is written in a different programming language, such as C or Java.
  B. Reuse of parsed PL/SQL code that becomes available in the shared SQL area of the server avoids the parsing overhead of SQL statements at run time.
  D. A PL/SQL procedure executing on the Oracle database can call an external procedure or function that is written in a different programming language, such as C or Java.

• Question 99:

  Which two statements are correct about PL/SQL package components? (Choose two.)

  A. A package must have both specification and body.
  B. A package body can exist without the package specification.
  C. A package specification can exist without the package body.
  D. When a packaged public variable is called for the first time in a session, the entire package is loaded into memory.
  C. A package specification can exist without the package body.
  D. When a packaged public variable is called for the first time in a session, the entire package is loaded into memory.

• Question 100:

  Identify the scenario in which you would use the CURRENT OF clause for an UPDATE or DELETE statement to rows fetched from a cursor.

  A. when you want to lock the rows fetched by the cursor
  B. when you want to update or delete the result set without affecting the rows in the table
  C. when you want the database not to wait if the requested rows are locked by another user
  D. when you want to ensure that the current rows fetched by the cursor are updated or deleted
  D. when you want to ensure that the current rows fetched by the cursor are updated or deleted