oracle 1z0-144 practice test

Oracle Database 11g: Program with PL/SQL

Last exam update: May 17 ,2024
Page 1 out of 7. Viewing questions 1-15 out of 115

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

User Votes:
A
50%
B 1 votes
50%
C 1 votes
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B 1 votes
50%
C 1 votes
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A 1 votes
50%
B
50%
C
50%
D
50%

Explanation:
In this case, the debug output will only occur if there is an exception.

Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B 1 votes
50%
C 1 votes
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B
50%
C 1 votes
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A 1 votes
50%
B 1 votes
50%
C 1 votes
50%
D
50%
E 1 votes
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000

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

User Votes:
A
50%
B
50%
C 1 votes
50%
D
50%
E 1 votes
50%
Discussions
vote your answer:
A
B
C
D
E
0 / 1000

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

User Votes:
A 1 votes
50%
B 1 votes
50%
C
50%
D
50%

Explanation:
References:

Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B 1 votes
50%
C 1 votes
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B 1 votes
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B 1 votes
50%
C
50%
D 1 votes
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A 1 votes
50%
B
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A 1 votes
50%
B 1 votes
50%
C
50%
D 1 votes
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B 1 votes
50%
C
50%
D
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000

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

User Votes:
A
50%
B
50%
C
50%
D 1 votes
50%
Discussions
vote your answer:
A
B
C
D
0 / 1000
To page 2