Oracle Database Program with PL/SQL

Here you have the best Oracle 1z0-149 practice exam questions

You have 54 total questions to study from
Each page has 5 questions, making a total of 11 pages
You can navigate through the pages using the buttons at the bottom
This questions were last updated on November 20, 2024

Question 1 of 54

Which two PL/SQL elements can be deprecated using the DEPRECATE pragma? (Choose two.)

PACKAGES

VARIABLES

ANONYMOUS BLOCK

TRIGGER BODY

DATABASE LINKS

Correct Answer: A, B

The DEPRECATE pragma in PL/SQL can be used to mark certain elements as deprecated. According to official documentation, the elements that can be deprecated include Subprograms, Packages, Variables, Constants, Types, Subtypes, Exceptions, and Cursors. Therefore, Packages and Variables are correct as they fit into these categories.

Question 2 of 54

Which three are true about functions and procedures? (Choose three.)

The ACCESSIBLE BY clause can be used only for procedures.

In a function, every execution path must lead to a RETURN statement.

Both can have only constants as actual parameters for IN mode parameters.

Both can be invoked from within SQL statements.

In a procedure the RETURN statement cannot specify an expression.

In a function every RETURN statement must specify an expression.

Correct Answer: B, E, F

In a function, every execution path must lead to a RETURN statement to ensure that the function returns a value, making B true. In a procedure, the RETURN statement cannot specify an expression; it is used to exit the procedure early, making E true. In a function, every RETURN statement must specify an expression because the purpose of a function is to return a value, making F true.

Question 3 of 54

Which two are true about Conditional Compilation in PL/SQL using $IF, $ELSE, $END, and $ERROR? (Choose two.)

PL/SQL code can be compiled and executed based on different versions of the operating system.

PL/SQL code can be compiled and executed based on different versions of Oracle.

It is newer syntax that works the same way as 'IF , ELSEIF , ELSE, and END IF'.

Conditional compilation is disabled by default.

The PL/SQL compiler can conditionally include selected parts of a program.

Correct Answer: B, E

Conditional Compilation in PL/SQL using $IF, $ELSE, $END, and $ERROR allows different code to be compiled and executed based on specific conditions. This feature enables the creation of code that adapts to different environments, such as different versions of Oracle. Additionally, the PL/SQL compiler can conditionally include or exclude certain parts of code based on specified conditions, making it possible to tailor the compilation process.

Question 4 of 54

Which three are true about the NOCOPY hint, the PARALLEL ENABLE hint, and the DETERMINISTIC clause? (Choose three.)

The PARALLEL_ENABLE clause can be used only in the CREATE FUNCTION statement.

The NOCOPY hint asks the compiler to pass the actual parameters by reference.

A deterministic function's results always depend on the state of session variables.

The NOCOPY hint asks the compiler to pass the actual parameters by value.

A function is deterministic if it always returns the same result for a specific combination of input values.

The PARALLEL_ENABLE clause can be specified for a nested function.

A function defined with the PARALLEL_ENABLE clause may be executed in parallel in a SELECT statement or a subquery in a DML statement.

Correct Answer: B, E, G

The NOCOPY hint asks the compiler to pass actual parameters by reference, which can enhance performance. A function is considered deterministic if it always returns the same result given the same input values, which means its output is predictable and consistent. A function defined with the PARALLEL_ENABLE clause can be executed in parallel when used within a SELECT statement or subquery in a DML statement, which can also improve performance and efficiency in processing large datasets.

Question 5 of 54

Which two are true about INDEX-BY tables? (Choose two.)

The index can be integer or string.

The index can be integer only.

INDEX-BY table types can be created in PL/SQL blocks only.

INDEX-BY table types can be created both with the CREATE TYPE statement and in PL/SQL blocks.

INDEX-BY table types can be created with the CREATE TYPE statement.

Correct Answer: A, B

INDEX-BY tables in PL/SQL, also known as associative arrays, must use an integer index. They cannot be created with the CREATE TYPE statement; they can only be defined within PL/SQL blocks. The index must be an integer, and not a string, making the options that state so incorrect.