oracle 1z0-144 practice test
Oracle Database 11g: Program with PL/SQL
Last exam update: Jul 20 ,2024
Question 1
Which statements are true about the WHEN OTHERS exception handler? (Choose all that apply.)
- A. It can be the first exception handler.
- B. It can be the only exception handler for the code.
- C. It traps all the exceptions that are not already trapped.
- D. You can have multiple OTHERS clauses to trap all the multiple unhandled exceptions.
Answer:
B C
Question 2
View Exhibit 1 and examine the structure of the EMP table.
View Exhibit 2 and examine the code of the packages that you have created.
You issue the following command:
SQL> DROP PACKAGE manage_emp;
What is the outcome?
- A. It drops both the MANAGE_EMP AND EMP__DET packages because of the cascading effect.
- B. It drops the MANAGE_EMP package and invalidates only the body for the EMP_DET package.
- C. It returns an error and does not drop the MAMAGE_EMP package because of the cascading effect.
- D. It drops the MANAGE_EMP package and invalidates both the specification and body for the EMP_DET package.
Answer:
B
Question 3
View the exhibit and examine the structure of the products table.
Examine the following code:
Which statement is true when the procedure DELETE_DETAILS is invoked?
- A. It executes successfully but no error messages get recorded in the DEBUG_OUTPUT table
- B. It executes successfully and any error messages get recorded in the DEBUG_OUTPUT table.
- C. It gives an error because PRAGMA AUTONOMOUS_TRANSACTION can be used only in packaged procedures.
- D. It gives an error because procedures containing PRAGMA AUTONOMOUS_TRANSACTION cannot be called from the exception section.
Answer:
A
Explanation:
In this case, the debug output will only occur if there is an exception.
Question 4
Which two tasks should be created as functions instead of as procedures? (Choose two.)
- A. Reference host or bind variables in a PL/SQL block of code
- B. Tasks that compute and return multiple values to the calling environment
- C. Tasks that compute a value that must be returned to the calling environment
- D. Tasks performed in SQL that increase data independence by processing complex data analysis within the Oracle server, rather than by retrieving the data into an application
Answer:
C D
Question 5
Which type of exceptions is qualified as nonpredefined Oracle server errors?
- A. the exceptions that are explicitly raised by the program and can be caught by the exception handler
- B. the exceptions that are raised implicitly by the Oracle server and can be caught by the exception handler
- C. an exception that the developer determines as abnormal, are in the declarative section and raised explicitly
- D. an exception that is raised automatically when the PL/SQL program violates a database rule or exceeds a system- dependent limit
Answer:
B
Question 6
Which tasks must be performed during the installation of the UTL_MAIL package? (Choose all that apply.)
- 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
Answer:
B C
Question 7
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
Answer:
C
Question 8
Examine the following command:
SQL>ALTER SESSION
SET plsql_warnings *
'enable: severe',
'enable: performance', 'ERROR: 05003';
What is the implication of the above command?
- A. It issues a warning whenever ERROR: 05003 occur during compilation.
- B. It causes the compilation to fail whenever the warning ERROR.05003 occurs.
- C. It issues warnings whenever the code causes an unexpected action or wrong results performance problems.
- D. It causes the compilation to fail whenever the code gives wrong results or contains statements that are never executed.
Answer:
B
Explanation:
References:
Question 9
View the Exhibit and examine the structure of the SALGRADE table.
Examine the following code:
What is the outcome?
- A. It is created successfully.
- B. It gives an error because the return clause condition is invalid.
- C. It gives an error because the usage of the host variables is invalid.
- D. It gives an error because the data type of the return clause is invalid.
Answer:
C
Question 10
View the Exhibit to examine the PL/SQL code.
Which statement is true about the exception handlers in the PL/SQL code?
- A. All the exceptions in the code are trapped by the exception handler.
- B. All the "no data found" errors in the code are trapped by the exception handler.
- C. The PL/SQL program does not execute because an exception is not declared in the declare section.
- D. An exception handler in the code traps the "no data found" error after executing the handler code and the program flow returns to the next line of code.
Answer:
B
Question 11
Examine the following package specification.
SQL>CREATE OR REPLACE PACKAGE emp_pkf IS
PROCEDURE search_emp (empdet NUMBER);
PROCEDURE search_emp (empdet DATE);
PROCEDURE search_emp (empdet NUMBER); RETURN VERCHAR2
PROCEDURE search_emp (empdet NUMBER); RETURN DATE
END emp_pkg
/
The package is compiled successfully Why would it generate an error at run tune?
- A. Because function cannot be overload.
- B. Because function cannot differ only in return type.
- C. Because all the functions and procedures in the package cannot have the same number of parameters with the same parameter name.
- D. Because the search EMP (EMPDET NUMBER) procedure and the SEARCH_DEPT (EMPDET NUMBER) cannot have identical parameter names and data types.
Answer:
B
Question 12
View the Exhibit to examine the PL/SQL code.
Which statement is true about the execution of the code?
- A. The exception raised in the code is handled by the exception handler for the PAST_DUE exception.
- B. It does not execute because you cannot declare an exception with a similar name in the subblock.
- C. The PAST_DUE exception raised in the subblock causes the program to terminate abruptly because there is no exception handler in the subblock.
- D. The PAST_DUE exception raised by the enclosing block is not propagated to the outer block and it is handled by the WHEN OTHERS exception handler
Answer:
D
Question 13
Which statements are true about PL/SQL procedures? (Choose all that apply.)
- 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.
Answer:
B D
Question 14
Examine the following block of code:
Which line in the above code would result in errors upon execution?
- A. line 5
- B. line 8
- C. line 2
- D. line 7
Answer:
B
Question 15
View the Exhibit and examine the structure of the AUDIR_CUST table.
Exhibit Missing
CUST_ID and CUST_LIMIT are existing columns in the CUSTOMER table.
Examine the following trigger code:
Which statement is true about the above trigger?
- A. It gives an error on compilation because it should be a statement-level trigger.
- B. It compiles and fires successfully when the credit limit is updated in the customer table.
- C. It gives an error on compilation because of the commit command in the trigger code.
- D. It compiles successfully, but gives an error when the credit limit is updated in the CUSTOMER table because the PRAGMA AUTONOMOUS_TRANSACTION statement should be introduced in the trigger.
Answer:
D