oracle 1z0-149 practice test

Question 1

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

• A. PACKAGES
• B. VARIABLES
• C. ANONYMOUS BLOCK
• D. TRIGGER BODY
• E. DATABASE LINKS

Question 2

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.

Question 3

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.

Question 4

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.

Question 5

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.

Question 6

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.
• C. A FUNCTION subprogram must return one or more values.
• D. Anonymous blocks cannot use packaged variables.
• E. Named subprograms are stored in the database server.
• F. Anonymous blocks must always start with the Declare keyword.
• G. FUNCTION subprograms must be called and passed through one or more parameters.

Question 7

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.
• E. Object privileges to execute a replaced function are retained by those users who had the privileges.

Question 8

Examine the SH.PRODUCTS table:

A row exists in SH.PRODUCTS 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

Question 9

Which two blocks of code display a numerical zero? (Choose two.)
A.

B.

C.

D.

Question 10

Which three are valid PL/SQL variable names? (Choose three.)

• A. printer_name#
• B. 1to7number
• C. yesterday's_date
• D. leap$year
• E. Number_of_days_between_March_and_April
• F. #printer_name
• G. v_fname

Question 11

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.
• 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.

Question 12

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.

Question 13

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

Question 14

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.
• B. In line 2, change IN OUT mode to IN mode.
• 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.

Question 15

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 ORA41.EMPLOYEES is not inherited by ORA61 through the procedure.
• E. ORA61 will have been granted the UPDATE privilege explicitly on ORA41.EMPLOYEES before executing the statement.