Question #1Topic 1

Which two PL/SQL elements can be deprecated using the DEPRECATE

pragma? (Choose two.)

 A. PACKAGES Most Voted

 B. VARIABLES Most Voted
 C. ANONYMOUS BLOCK
 D. TRIGGER BODY
 E. DATABASE LINKS

Hide Solution Discussion 5

Correct Answer: AB 🗳️

Community vote distribution

AB (100%)

Question #2Topic 1
Which three are true about functions and procedures? (Choose three.)

 A. The ACCESSIBLE BY clause can be used only for procedures.

 B. In a function, every execution path must lead to a RETURN statement.
 C. Both can have only constants as actual parameters for IN mode
parameters.
 D. Both can be invoked from within SQL statements.
 E. In a procedure the RETURN statement cannot specify an expression.
 F. In a function every RETURN statement must specify an expression.

Hide Solution Discussion 2

Correct Answer: BEF 🗳️

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

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

the operating system.
 B. PL/SQL code can be compiled and executed based on different versions of
Oracle.
 C. It is newer syntax that works the same way as 'IF , ELSEIF , ELSE, and
END IF'.
 D. Conditional compilation is disabled by default.
 E. The PL/SQL compiler can conditionally include selected parts of a
program.

Hide Solution Discussion 2

 Correct Answer: BE 🗳️

Question #4Topic 1
Which three are true about the NOCOPY hint, the PARALLEL ENABLE hint,
and the DETERMINISTIC clause? (Choose three.)

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

statement.
 B. The NOCOPY hint asks the compiler to pass the actual parameters by
reference.
 C. A deterministic function's results always depend on the state of session
variables.
 D. The NOCOPY hint asks the compiler to pass the actual parameters by
value.
 E. A function is deterministic if it always returns the same result for a
specific combination of input values.
 F. The PARALLEL_ENABLE clause can be specified for a nested function.
 G. A function defined with the PARALLEL_ENABLE clause may be executed in
parallel in a SELECT statement or a subquery in a DML statement.

Hide Solution Discussion 2

Correct Answer: BEG 🗳️

Next Questions

Question #5Topic 1
Which two are true about INDEX-BY tables? (Choose two.)

 A. The index can be integer or string.

 B. The index can be integer only.
 C. INDEX-BY table types can be created in PL/SQL blocks only.
 D. INDEX-BY table types can be created both with the CREATE TYPE
statement and in PL/SQL blocks.
 E. INDEX-BY table types can be created with the CREATE TYPE statement.

Hide Solution Discussion 8

Correct Answer: AC 🗳️

Community vote distribution

AC (80%)
AD (20%)
 Question #6Topic 1
Which three are true about anonymous blocks and subprograms? (Choose
three.)

 A. Named subprograms cannot be called from other packages.

 B. PROCEDURE subprograms can accept parameters. Most Voted
 C. A FUNCTION subprogram must return one or more values. Most Voted
 D. Anonymous blocks cannot use packaged variables.
 E. Named subprograms are stored in the database server. Most Voted
 F. Anonymous blocks must always start with the Declare keyword.
 G. FUNCTION subprograms must be called and passed through one or
more parameters.

Hide Solution Discussion 4

Correct Answer: BCE 🗳️

Community vote distribution

BCE (100%)

Question #7Topic 1
Which two statements are true about using the OR REPLACE clause when
creating named subprograms? (Choose two.)

 A. Function based indexes remain usable when replacing the function on

which the index depends.
 B. Object privileges to execute a replaced function must be regranted to
those users who had the privilege.
 C. This clause can be used only for procedures and functions.
 D. A function definition can be modified without dropping and re-creating
it. Most Voted
 E. Object privileges to execute a replaced function are retained by those
users who had the privileges. Most Voted

Hide Solution Discussion 3

Correct Answer: DE 🗳️

Community vote distribution

DE (100%)

Question #8Topic 1
Examine the [Link] table:

A row exists in [Link] with PDT_ID = 1.

Now, examine this code and output executed by SH:

Now, examine this block of code:

 Which error message(s) does it display on execution by user SH?

 A. Error in inner block

 B. Error in inner block Error in outer block
 C. Error in inner block Error in calling block
 D. Error in inner block Error in outer block Error in calling block

Hide Solution Discussion 1

Correct Answer: A 🗳️

Community vote distribution

Question #9Topic 1
Which two blocks of code display a numerical zero? (Choose two.)

 A.
 B.

 C.

 D.

Hide Solution Discussion 2

 Correct Answer: AB 🗳️

Community vote distribution

AB (100%)

Question #10Topic 1
Which three are valid PL/SQL variable names? (Choose three.)

 A. printer_name# Most Voted

 B. 1to7number
 C. yesterday's_date
 D. leap$year Most Voted
 E. Number_of_days_between_March_and_April
 F. #printer_name
 G. v_fname Most Voted

Hide Solution Discussion 5

Correct Answer: ADG 🗳️

Community vote distribution

ADG (100%)

Question #11Topic 1
Which two are true about collections and RECORD types? (Choose two.)

 A. A variable of RECORD type can contain fields of another RECORD type

or any collection type. Most Voted
 B. Only associative arrays and nested tables can have elements of
RECORD type.
 C. All collections and RECORD types can be defined in PL/SQL blocks,
packages, or at the schema level.
 D. Collections and RECORD types are always dense.
 E. All collections and RECORD types can be stored in table columns.
 F. VARRAYS, nested tables and each field in %ROWTYPE type variables
have a default value of null. Most Voted

Hide Solution Discussion 5

Correct Answer: BF 🗳️

Community vote distribution

AF (100%)

Question #12Topic 1
 Examine this table in the SH schema:

User SH executes this code:

The program must terminate with a user-defined message and no rows

displayed if more than one product's price is 1000.
With which option must “---placeholder” be replaced?

 A.

 B.
 C.

 D.

 E.

Hide Solution Discussion 3

Correct Answer: A 🗳️

Community vote distribution

A (100%)

Question #13Topic 1
Examine this row of data from the EMPLOYEES table:

Now, examine this block of code which executes successfully:

 What is the value of v_commission?

 A. 5000
 B. 15000
 C. 2500
 D. 10000

Hide Solution Discussion 1

Correct Answer: C 🗳️

Community vote distribution

 C (100%)

Question #14Topic 1
Examine this table in the SH schema:

Now, examine this code:

Which two changes are required to ensure that PDT_REPORT executes

successfully? (Choose two.)

 A. In line 1, change IN OUT mode to IN mode. Most Voted

 B. In line 2, change IN OUT mode to IN mode. Most Voted
 C. In line 3, replace CUR_PRICE with P_PDT_PRICE in the query condition.
 D. In line 1, add the default parameter DEFAULT 2000.
 E. In line 6, replace P_PDT_PRICE parameter name with CUR_PRICE.
 F. In line 2, add the default parameter DEFAULT 2000.

Hide Solution Discussion 7

Correct Answer: BD 🗳️

Community vote distribution

AB (100%)

Question #15Topic 1
User ORA41 executes these statements successfully:

Now, examine this statement which is executed successfully by user

ORA61 after a successful login:
EXECUTE ora41.update_emp_proc(100,25000);
Which two are true? (Choose two.)

 A. The salary will be changed for employee 100 in the EMPLOYEES table
owned by ORA41.
 B. No update happens even though the procedure executes successfully.
 C. The salary will be changed for employee 100 in the EMPLOYEES table
owned by ORA61.
 D. The UPDATE privilege on [Link] is not inherited by ORA61
through the procedure.
 E. ORA61 will have been granted the UPDATE privilege explicitly on
[Link] before executing the statement.

Reveal Solution Discussion 5

 Question #16Topic 1
Which two are true about exception handling? (Choose two.)

 A. Internally defined exceptions can be handled only by the OTHERS

exception handler.
 B. All declared exceptions are raised implicitly by the runtime system.
 C. User-defined exceptions can be defined in the declarative part of any
PL/SQL anonymous block, subprogram, or package. Most Voted
 D. Only predefined exceptions and user-defined exceptions can have a
user-declared name associated with them.
 E. Predefined exceptions are globally declared in the standard
package. Most Voted

Hide Solution Discussion 5

Correct Answer: CE 🗳️

Community vote distribution

CE (86%)
14%

Question #17Topic 1
Examine these statements:

Which is true?

 A. It will result in a compilation error for protected_proc because

calling_proc does not exist.
 B. It will result in a compilation error for protected_proc because
calling_proc must be prefixed with the schema name.
 C. It will result in a successful compilation because objects referenced in
an ACCESSIBLE BY clause are not checked at compile time. Most Voted
 D. With adequate privileges, PROTECTED_PROC procedure can be called
by other programs apart from CALLING_PROC.

Hide Solution Discussion 7

Correct Answer: A 🗳️

Community vote distribution

C (100%)

Question #18Topic 1
Examine the EMPLOYEES table structure:

Now, examine this code:

Which statement is true about the result of executing this block?

 A. It will execute successfully provided the salary of EMP_ID 200 does not
exceed the value 99999.
 B. It will return an error at line 2.
 C. It will return an error at line 3.
 D. It will return an error at line 8.
 E. It will execute successfully by rounding up the salary of EMP_ID 200 to
the appropriate value.

Hide Solution Discussion 6

 Correct Answer: A 🗳️

Community vote distribution

A (100%)

Question #19Topic 1
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.)

 A. line 1
 B. line 6
 C. line 8
 D. line 2
 E. line 3
 F. line 7
 Hide Solution Discussion 2
Correct Answer: AB 🗳️

Community vote distribution

AB (100%)

Question #20Topic 1
For which three SYSTEM EVENTS can triggers be created? (Choose three.)

 A. DDL
 B. AFTER AUDIT
 C. BEFORE ANALYZE
 D. SHUTDOWN Most Voted
 E. SERVERERROR Most Voted
 F. STARTUP Most Voted
 G. BEFORE GRANT

Hide Solution Discussion 5

Correct Answer: DEF 🗳️

Community vote distribution

DEF (86%)
14%

Question #21Topic 1
Which three statements are true about Implicit Cursor and Explicit Cursor?
(Choose three.)

 A. Implicit cursor returns only one record.

 B. Explicit cursor can return more than one record. Most Voted
 C. %Isopen, %Rowtype, %Notfound, and %Found are the attributes of
Explicit Cursor.
 D. %Isopen, %Rowcount, %Notfound, and %Found are the attributes of
Explicit Cursor. Most Voted
 E. %Isopen, %Type, %Notfound, and %Found are the attributes of Explicit
Cursor.
 F. %Isopen is always false in Explicit Cursor.
 G. %Isopen is always false in Implicit Cursor. Most Voted

Hide Solution Discussion 8

Correct Answer: BCG 🗳️

Community vote distribution

BDG (100%)

Question #22Topic 1
Which three are true about DDL triggers? (Choose three.)
 A. They cannot include the WHEN clause.
 B. They must be created in an enabled state.
 C. They can be fired when a table is truncated.
 D. They fire only when a DDL statement is executed by the owner of the
trigger.
 E. They can be fired either before or after a DDL statement executes.
 F. They can be fired when a privilege is granted to a user.
 G. They must be created in a disabled state.

Hide Solution Discussion 5

Correct Answer: CDE 🗳️

Community vote distribution

ACE (100%)

Question #23Topic 1
Which code will successfully create a BODILESS PACKAGE to standardize
CONSTANTS and EXCEPTIONS declarations?

 A.
 B.

 C.
 D.

Hide Solution Discussion 1

Correct Answer: C 🗳️

Question #24Topic 1
In which type of trigger can :OLD and :NEW identifiers be used?

 A. ROW
 B. AFTER SUSPEND
 C. AFTER STATEMENT
 D. BEFORE STATEMENT

Hide Solution Discussion

Correct Answer: A 🗳️

Question #25Topic 1
Which three are true about PL/SQL subprograms? (Choose three.)

 A. Results of a subprogram can be cached in the SGA such that sessions

connected to the same instance can reuse these results when available.
 B. 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.
 C. Subprograms are cached by default and shared among users, thereby
reducing memory requirements.
 D. Reuse of parsed PL/SQL code from the shared SQL area reduces
parsing overhead.
 E. A subprogram's session state is retained even if any of the session’s
instantiated subprograms are invalidated and revalidated.
 F. Host variables can be referenced inside any PL/SQL subprogram.
 G. A PL/SQL procedure can invoke an external code block written in a
different programming language.

Hide Solution Discussion 3

Correct Answer: CDG 🗳️

Question #26Topic 1
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. a record declared in an anonymous block

 B. a record declared in a procedure
 C. an associative array indexed by PLS_INTEGER
 D. a record declared in a package specification
 E. a predefined PL/SQL-only data type like BOOLEAN
 F. an associative array indexed by VARCHAR2

Hide Solution Discussion

Correct Answer: CDF 🗳️

Question #27Topic 1
Which three are true about user-defined functions? (Choose three.)

 A. They can be used in ORDER BY and GROUP BY clauses.

 B. They can be executed as standalone commands.
 C. They must be defined with at least one parameter.
 D. They need not return any values.
 E. They can appear in the select list of a SELECT statement.
 F. Functions can call only other functions.
 G. They can be used in CONNECT BY and START WITH clauses.

Hide Solution Discussion 3

Correct Answer: AEG 🗳️

Community vote distribution

AEG (100%)

Question #28Topic 1
Which two are true about implicit data type conversion? (Choose two.)
 A. RAW data types are always implicitly converted to a CLOB when used in
a query.
 B. Collections can be implicitly converted to records.
 C. ROWIDS are always implicitly converted to a number when used in a
query.
 D. Comparison between character value and a number value always
implicitly converts the character value to the number data type.
 E. Implicit data type conversion can negatively impact performance.

Hide Solution Discussion

Correct Answer: DE 🗳️

Question #29Topic 1
Examine these facts:
Table EMP exists in schema USERA with columns SALARY and EMP_ID.
EMP_ID is the primary key with values ranging from 1 to 100.
USERA now executes these statements successfully:

USERA then grants execute privilege on procedure MYPROC to USERB.

USERB exists in the database identified by pdb1 but does not have select
privilege on [Link].
USERB now executes these statements:
conn userB/userB@pdb1
execute [Link];
Which is true?

 A. It results in an error because Authid Current_User is missing from

MYPROC.
 B. It results in an error because Authid Definer is missing from MYPROC.
 C. It results in an error because USERB doesn't have select privilege on
[Link].
 D. It executes successfully.

Hide Solution Discussion 2

Correct Answer: D 🗳️

Question #30Topic 1
Which two are true about named notations in a PL/SQL subprogram?
(Choose two.)

 A. The actual parameters must be specified in the same order as the

formal parameters are declared.
 B. Specifying actual parameters in the wrong order results in the
subprogram terminating with an exception.
 C. Only trailing optional parameters can be omitted in the invocation call.
 D. The subprogram invocations must be changed when the formal
parameter list acquires new required parameters. Most Voted
 E. Any optional parameters can be omitted in the invocation call. Most
Voted

Hide Solution Discussion 6

Correct Answer: AE 🗳️

Community vote distribution

DE (100%)

Question #31Topic 1
Examine this DECLARE section:

Which line will cause an error upon execution?

 A. line 5
 B. line 3
 C. line 2
 D. line 4 Most Voted
 E. line 6

Hide Solution Discussion 5

Correct Answer: E 🗳️

Community vote distribution

D (100%)

Question #32Topic 1
Examine this DECLARE section:

Which two lines are valid? (Choose two.)

 A. line 5
 B. line 6
 C. line 7
 D. line 4
 E. line 3
 F. line 2

Hide Solution Discussion 4

Correct Answer: AF 🗳️

Community vote distribution

AF (100%)