Oracle Oracle Certifications 1Z0-144 Questions & Answers

• Question 51:

  Examine the following partial code:

  

  Which statement is correct about the unnamed block of code at the end of a package body?

  A. It generates an error because all the blocks of code in a package body must be named.

  B. It generates an error because V_TAXRATE is a public variable that is already initialized in the package specification.

  C. It acts as a package initialization block that executes once, when the package is first invoked within the user session.

  D. It acts as a package initialization block that executes each time a package subprogram is invoked within the user session and refreshes the initialized variable's value.

  Correct Answer: C

• Question 52:

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

  

  Examine the following block of code:

  

  Which stages are performed when the above block is executed? (Choose three.)

  A. Bind

  B. Parse

  C. Fetch

  D. Execute

  Correct Answer: BCD

  Reference: https://www.ibm.com/support/knowledgecenter/en/SS6NHC/ com.ibm.swg.im.dashdb.apdv.plsql.doc/doc/c0053844.html

• Question 53:

  Which two statements are true about the handling of internally defined or user-defined PL/SQL exceptions? (Choose two.)

  A. Add exception handlers whenever errors occur.

  B. An exception handler should commit the transaction.

  C. Handle named exceptions whenever possible instead of using when others in exception handlers.

  D. Instead of adding exception handlers to your PL/SQL block, check for errors at every point where they may occur.

  Correct Answer: AC

• Question 54:

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

  

  View Exhibit 2 and examine the code created by the user SCOTT:

  

  SCOTT grants the necessary privileges to green to access the EMP table and execute the package.

  Examine the following sequence of activities:

  SCOTT starts a session and issues the SQL>EXEC CURS_PKG.OPEN command.

  SCOTT then issues the SQL>EXEC CURS_PKG.NEXT command.

  green starts a session while SCOTT's session is running and issues THE SQL>EXEC CURS_PKG.NEXT

  command.

  SCOTT issues the SQI>>EXEC SCOTT.CURS_PKG.NEXT command.

  The EMP table contains sequential EMPNOS from 100 through 108.

  Which statement correctly describes the output?

  A. SCOTT's session shows the EMPNO 100, GREEN'S session shows an error, and SCOTT's session shows an error.

  B. SCOTT's session shows the EMPNO 100, GREEN'S session shows EMPNO 100, and SCOTT's session shows the EMPNO 101.

  C. SCOTT's session shows the EMPNO 100, GREEN'S session shows an error, and SCOTT's session shows the second EMPNO 101.

  D. SCOTT's session shows the EMPNO 100, GREEN'S session shows EMPNO 101, and SCOTT's session shows the second EMPNO 102.

  Correct Answer: C

• Question 55:

  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

  Correct Answer: C

• Question 56:

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

  A. The exception reproduces itself in successive enclosing blocks until a handler is found.

  B. Exception- can propagate across the remote subprograms that are called through database links.

  C. If you declare a local exception in a subblock and a global exception in the outer block, the local declaration overrides the global exception.

  D. If you declare a local exception in a subblock and a global exception in the outer block, the global declaration overrides the local exception.

  Correct Answer: AC

• Question 57:

  Which tasks must be performed during the installation of the UTL_MAIL package? (Choose two.)

  A. setting the UTL_FILE_DIR initialization parameter

  B. running the utlmail.sql and prvtmail.plb scripts

  C. setting the SMTP_OUT_SERVER initialization parameter

  D. using the CREATE DIRECTORY statement to associate an alias with an operating system directory

  E. granting read and WRITE privileges to control the type of access to files in the operating system

  Correct Answer: BC

• Question 58:

  You want to maintain an audit of the date and time when each user of the database logs off.

  Examine the following code:

  

  Which two clauses should be used to fill in the blanks and complete the above code? (Choose two.)

  A. ON SCHEMA

  B. ON QRXABASE

  C. AFTER LOGOFF

  D. BEFORE LOGOFF

  Correct Answer: BD

• Question 59:

  View the Exhibit to examine the PL/SQL block.

  

  Which statement is true about the output of the PL/SQL block?

  A. It executes and the output is emprec.deptname:.

  B. It executes and the output is emprec.deptname: Sales.

  C. It produces an error because NULL is assigned to the emprec.empid field in the record.

  D. It produces an error because the CHECK constraint is violated while assigning a value to the emprec.deptid field in the record.

  Correct Answer: A

• Question 60:

  Examine the following snippet of code from the DECLARE section of PL/SQL

  DECLARE

  Cust_name VERCHAR2 (20) NOT NULL : = `Tom Jones':

  Same_name cust_name%TYPE:

  Which statement is correct about the above snippets of code?

  A. The SAME_NAME variable inherits only the data type from the CUST_NAME variable.

  B. The SAME_NAME variable inherits only the data type and default value from the CUST_NAME variable.

  C. The SAME_NAME variable inherits the data type, constraint, and default value from the CUST_NAME variable.

  D. The SAME_NAME variable inherits only the data type and constraint from the CUST_NAME variable resulting in an error

  Correct Answer: D