Validate your 1z1-149 Exam Preparation with 1z1-149 Practice Test (Online & Offline) [Q31-Q49]

4.5/5 - (2 votes)

Validate your 1z1-149 Exam Preparation with 1z1-149 Practice Test (Online & Offline)

Get all the Information About Oracle 1z1-149 Exam 2023 Practice Test Questions

Oracle 1Z0-149 exam consists of 80 multiple-choice questions that need to be completed in 120 minutes. 1z1-149 exam measures a candidate’s ability to write efficient and effective PL/SQL code to solve real-world problems. Candidates need to have a good understanding of basic database concepts, SQL, and PL/SQL syntax before taking the exam. Additionally, candidates should also have practical experience in developing PL/SQL code and be familiar with the Oracle Database 19c environment.

NO.31 Which three PL/SQL-only data types can be used in queries and native dynamic SQL issued from PL/SQL in the server? (Choose three.)

a record declared in an anonymous block

a record declared in a procedure

an associative array indexed by PLS_INTEGER

a record declared in a package specification

a predefined PL/SQL-only data type like BOOLEAN

an associative array indexed by VARCHAR2

NO.32 Examine this code:

What will be the outcome?

It will execute successfully and will display the user-defined error message.

It will result in an error as the range of the error code can only be from “-20000 to -20999.”

It will result in an error as the range of the error code can only be from “-1000 to -2000.”

It will result in an error as the range of the error code can only be from “-2000 to -2999.”

NO.33 Examine this code:
ALTER SESSION SET plsql_warnings=’ENABLE:ALL’;
/
You compile this function:

What happens when the function is created with PLSQL_WARNINGS set to ‘ENABLE: ALL’?

It fails compilation.

There are no compilation warnings or errors.

A severe compilation warning is generated.

A performance compilation warning is generated.

An information compilation warning is generated.

NO.34 Which three SQL statements, embedded in PL/SQL, typically benefit from using variables declared with %ROWTYPE? (Choose three.)

CREATE

DROP

UPDATE

SELECT

DELETE

ALTER

INSERT

NO.35 Which is true about counter variables in a FOR loop?

It is accessible outside the body of the loop.

It cannot be NULL.

It must explicitly be declared.

It can be modified in the body of the loop.

NO.36 Which two statements are true about using the OR REPLACE clause when creating named subprograms? (Choose two.)

Function based indexes remain usable when replacing the function on which the index depends.

Object privileges to execute a replaced function must be regranted to those users who had the privilege.

This clause can be used only for procedures and functions.

A function definition can be modified without dropping and re-creating it.

Object privileges to execute a replaced function are retained by those users who had the privileges.

NO.37 Which three are true about PL/SQL subprograms? (Choose three.)

Results of a subprogram can be cached in the SGA such that sessions connected to the same instance can reuse these results when available.

Users granted execute privilege on a procedure compiled with definer’s rights require grants to access objects belonging to the definer that are referenced in the procedure.

Subprograms are cached by default and shared among users, thereby reducing memory requirements.

Reuse of parsed PL/SQL code from the shared SQL area reduces parsing overhead.

A subprogram’s session state is retained even if any of the session’s instantiated subprograms are invalidated and revalidated.

Host variables can be referenced inside any PL/SQL subprogram.

A PL/SQL procedure can invoke an external code block written in a different programming language.

NO.38 Examine this DECLARE section:

Which two lines are valid? (Choose two.)

line 5

line 6

line 7

line 4

line 3

line 2

NO.39 Which three statements can process a dynamic multi-row query? (Choose three.)

DECLARE

OPEN

INTO

OPEN-FOR

FETCH

WHEN

NO.40 Which two are true about exception handling? (Choose two.)

Internally defined exceptions can be handled only by the OTHERS exception handler.

All declared exceptions are raised implicitly by the runtime system.

User-defined exceptions can be defined in the declarative part of any PL/SQL anonymous block, subprogram, or package.

Only predefined exceptions and user-defined exceptions can have a user-declared name associated with them.

Predefined exceptions are globally declared in the standard package.

NO.41 Examine this statement which executes successfully:
SQL> SET SERVEROUTPUT ON;
Now, examine this code which is executed:

What is true about the result?

It returns an error in line 2.

It returns an error in line 4.

It returns an error in line 9.

It executes and displays output.

NO.42 In one of your databases, table HR.EMPLOYEES includes the columns FIRST_NAME and EMPLOYEE_ID.
A row exists with EMPLOYEE_ID 201.
Examine these packages created by user HR:

EXECUTE privilege is granted to user SH, on the HR.API and HR.HELPER packages.
Which two will execute successfully? (Choose two.)

Call HR.HELPER.H1 from the HR schema.

Call HR.HELPER.H1 from the SH schema.

Call HR.API.P1 from the HR schema.

Call HR.API.P1 from the SH schema.

Create and call a package procedure API.H1 in the SH schema, which calls HR.HELPER.H1.

NO.43 Examine these statements:

Which is true?

It will result in a compilation error for protected_proc because calling_proc does not exist.

It will result in a compilation error for protected_proc because calling_proc must be prefixed with the schema name.

It will result in a successful compilation because objects referenced in an ACCESSIBLE BY clause are not checked at compile time.

With adequate privileges, PROTECTED_PROC procedure can be called by other programs apart from CALLING_PROC.

NO.44 Which two are true about lexical units? (Choose two.)

Character literals are case-sensitive.

Spaces can be embedded in delimiters.

Identifiers include labels.

A character literal with zero characters has the BOOLEAN value NULL.

All user-defined identifiers must start with an alphabet.

NO.45 Which two statements are true about the RETURNING clause when used with DML? (Choose two.)

When using the RETURNING INTO clause, the data returned can be only single column or expression.

The RETURNING INTO clause and bulk binds may not be used together.

The RETURNING INTO clause can be used with DML that affects multiple rows, if the BULK COLLECT clause is used.

The RETURNING INTO clause returns column values for rows affected by DML statements.

The RETURNING clause can be used for remote or parallel deletes.

NO.46 Which is the correct method to implement a local subprogram in an anonymous block?

NO.47 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.

NO.48 Which two are true about the PLSQL_CODE_TYPE parameter? (Choose two.)

Changing the parameter setting automatically changes the setting for existing PL/SQL library units.

The default value is NATIVE.

If set to NATIVE, programs are stored in platform dependent machine code.

It can use the REUSE SETTINGS clause to recompile a program unit without changing to the current session settings.

If set to NATIVE, programs are stored in a PL/SQL bytecode format.

NO.49 Sequence S and table PRODUCTS exist in your schema.
Examine the table description:

Now, examine this block of code:

Which two lines each result in a compilation error? (Choose two.)