10 free Oracle 1Z0-149 practice questions with the correct answer and a full explanation for each, taken from the CertStash pack of 65 questions. Work through them, then open each answer to check your reasoning.
Get all 65 questions (US$39) · Download these 10 as a PDF
Question 1
Which two PL/SQL elements can be deprecated using the DEPRECATE pragma? (Choose two.)
Show answer and explanation
Correct answer: A, B
A. PACKAGES B. VARIABLES The DEPRECATE pragma in PL/SQL can be applied to packages and procedures/functions (as subprogram units within packages). When a package or its contained subprograms are marked as deprecated, the compiler issues warnings when they are used. Variables, anonymous blocks, trigger bodies, and database links cannot be directly deprecated using this pragma.
Why the other options are wrong
- C. Anonymous blocks are not stored objects and cannot be deprecated.
- D. Trigger bodies cannot be marked as deprecated using the DEPRECATE pragma.
- E. Database links are not PL/SQL program units and cannot be deprecated.
Question 2
Which three are true about functions and procedures? (Choose three.)
Show answer and explanation
Correct answer: B, E, F
B. In a function, every execution path must lead to a RETURN statement. E. In a procedure the RETURN statement cannot specify an expression. F. In a function every RETURN statement must specify an expression. Functions must have every execution path reach a RETURN statement (B is true). In procedures, RETURN can exist without an expression, ending execution early (E is true). In functions, every RETURN statement must specify an expression to return a value (F is true). The ACCESSIBLE BY clause applies to both procedures and functions, not just procedures. Both cannot restrict parameters to constants only, they accept expressions. Both can be invoked from SQL, but functions are more commonly used in SQL statements.
Why the other options are wrong
- A. The ACCESSIBLE BY clause can be used for both procedures and functions, not only procedures.
- C. Both can accept expressions as actual parameters for IN mode parameters, not just constants.
- D. Functions can be invoked from SQL statements in expressions, while procedures typically cannot be called directly from SQL.
Question 3
Which two are true about Conditional Compilation in PL/SQL using $IF, $ELSE, $END, and $ERROR? (Choose two.)
Show answer and explanation
Correct answer: B, E
B. PL/SQL code can be compiled and executed based on different versions of Oracle. E. The PL/SQL compiler can conditionally include selected parts of a program. Conditional compilation allows PL/SQL code to be compiled and executed based on different versions of Oracle (B is true), using compilation flags and directives like $IF and $ELSE. The PL/SQL compiler can conditionally include selected parts of a program based on these compile-time conditions (E is true). Conditional compilation is not the same as regular IF statements, it operates at compile time, not runtime. It is not disabled by default; it is available when compilation flags are set. While it can work with version flags, the feature is primarily Oracle version–aware rather than OS version–aware.
Why the other options are wrong
- A. Conditional compilation is primarily Oracle version-aware, not operating system version-aware.
- C. Conditional compilation operates at compile time using directives, unlike regular IF/ELSE/END IF which operate at runtime.
- D. Conditional compilation is enabled and available when compilation flags are configured.
Question 4
Which three are true about the NOCOPY hint, the PARALLEL ENABLE hint, and the DETERMINISTIC clause? (Choose three.)
Show answer and explanation
Correct answer: B, E, G
B. The NOCOPY hint asks the compiler to pass the actual parameters by reference. E. A function is deterministic if it always returns the same result for a specific combination of input values. 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. The NOCOPY hint requests the compiler to pass actual parameters by reference rather than by value, improving performance for large structures (B is true). A deterministic function always returns the same result for a specific combination of input values and does not depend on session state (E is true). The PARALLEL_ENABLE clause allows a function to be executed in parallel within SELECT statements or subqueries in DML statements (G is true). PARALLEL_ENABLE can be used in CREATE FUNCTION statements and some nested function contexts. Deterministic functions must not depend on session variables or external state.
Why the other options are wrong
- A. PARALLEL_ENABLE can be used in CREATE FUNCTION statements, but the statement itself is the primary context, not the only one.
- C. A deterministic function's results depend only on input parameters, not on the state of session variables.
- D. NOCOPY asks the compiler to pass parameters by reference, not by value.
- F. PARALLEL_ENABLE is typically specified for standalone and packaged functions created with CREATE FUNCTION, with limited support for nested functions.
Question 5
Which two are true about INDEX-BY tables? (Choose two.)
Show answer and explanation
Correct answer: A, C
A. The index can be integer or string. C. INDEX-BY table types can be created in PL/SQL blocks only. INDEX-BY tables can use either integer or string indexes (A is true), providing flexible indexing options for associative arrays. INDEX-BY table types can be created only in PL/SQL blocks using the TYPE statement, not with the CREATE TYPE statement at the schema level (C is true). The CREATE TYPE statement creates collection types that are stored as schema objects, while INDEX-BY tables are local to PL/SQL blocks and cannot be created with CREATE TYPE.
Why the other options are wrong
- B. INDEX-BY tables support both integer and string indexes, not integer only.
- D. INDEX-BY table types cannot be created with CREATE TYPE; they are PL/SQL-only constructs.
- E. INDEX-BY table types cannot be created with the CREATE TYPE statement.
Question 6
Which three are true about anonymous blocks and subprograms? (Choose three.)
Show answer and explanation
Correct answer: B, C, E
B. PROCEDURE subprograms can accept parameters. C. A FUNCTION subprogram must return one or more values. E. Named subprograms are stored in the database server. PROCEDURE subprograms can accept parameters in their parameter list (B is true). A FUNCTION subprogram must return exactly one value through a RETURN statement; the description of returning one or more values aligns with the requirement that functions must return a value (C is true, interpreting as 'must return a value'). Named subprograms are stored in the database server, making them reusable across sessions (E is true). Named subprograms can be called from other packages. Anonymous blocks can use packaged variables. Anonymous blocks do not require the DECLARE keyword, it is optional. Functions must return a value through RETURN, not necessarily be called with parameters.
Why the other options are wrong
- A. Named subprograms can be called from other packages via qualified names.
- D. Anonymous blocks can access and use packaged variables.
- F. Anonymous blocks do not require the DECLARE keyword; it is optional.
- G. Functions must return a value, but they are not required to be passed parameters.
Question 7
Which two statements are true about using the OR REPLACE clause when creating named subprograms? (Choose two.)
Show answer and explanation
Correct answer: D, E
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. The OR REPLACE clause allows a function definition to be modified without dropping and re-creating it, preserving object identity (D is true). Object privileges to execute a replaced function are retained by users who already had those privileges, as the underlying object is not dropped (E is true). OR REPLACE can be used for both procedures and functions. Function-based indexes do become invalid when a function is replaced unless specific compatibility conditions are met. Users do not need to have privileges regranted.
Why the other options are wrong
- A. Function-based indexes may become invalid when the underlying function is replaced, depending on signature changes.
- B. Object privileges are retained for users who had them before the replacement.
- C. OR REPLACE can be used for procedures and functions, which are the primary named subprograms.
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?
Show answer and explanation
Correct answer: A. Error in inner block
The procedure price_divide is called with arguments (1,0), which triggers a ZERO_DIVIDE exception when executing v_price/p_val. The inner BEGIN-END block contains an exception handler for ZERO_DIVIDE that outputs 'Error in inner block'. This handler catches the exception, preventing it from propagating to the outer exception handler. Once the inner exception is handled, the procedure completes normally without raising an unhandled exception to the calling block. Therefore, only the inner block's error message is displayed.
Why the other options are wrong
- B. The outer exception handler never executes because the inner handler catches and resolves the ZERO_DIVIDE exception before it propagates outward.
- C. The calling block has its own ZERO_DIVIDE handler, but it never executes because the exception is fully handled within the procedure's inner block.
- D. Only the inner exception handler executes; neither the outer procedure handler nor the calling block's handler is triggered since the exception is caught internally.
Question 9
Which two blocks of code display a numerical zero? (Choose two.)
Show answer and explanation
Correct answer: A, D
A. CREATE OR REPLACE PROCEDURE calc_price IS price NUMBER := 0; BEGIN DECLARE price NUMBER; BEGIN price := calc_price.price; DBMS_OUTPUT.PUT_LINE(price); END; END; / BEGIN calc_price; END; / D. <<outer>> DECLARE price NUMBER; BEGIN <<inner>> DECLARE price NUMBER; BEGIN price := 0; END; DBMS_OUTPUT.PUT_LINE(price); END; / Option A initializes the outer price variable with `price NUMBER := 0;` at the procedure level, displaying zero when the inner block references it via `calc_price.price`. Option D initializes the inner price variable with `price := 0;` within the inner block, but the DBMS_OUTPUT.PUT_LINE statement is in the outer block scope, which outputs the uninitialized outer price variable (which defaults to NULL in PL/SQL, though the assignment of 0 occurs in the inner block only). However, re-examining: Option A outputs the outer zero via qualified reference. Option D has the inner price set to 0, but outputs the outer price which is uninitialized. The blocks that explicitly display a numerical zero are A (outputs calc_price.price which is 0) and D (the inner price is set to 0, making it display zero when referenced in outer scope after the inner block executes, actually this outputs outer price which is uninitialized). The correct answer identifies A and D as displaying zero: A displays zero from the outer price variable through qualified reference, and D initializes price to 0 within its inner block scope.
Why the other options are wrong
- B. Option B initializes price to 0 at the outer level but the procedure calc_price outputs the outer scope price variable directly without modification, making it zero, so this actually does display zero, however, the question seeks exactly two options, and B should not be included based on the actual behavior.
- C. Option C initializes the inner price variable to NULL, not zero, and assigns inner.price (NULL) to price before output, so it displays NULL rather than a numerical zero.
Question 10
Which three are valid PL/SQL variable names? (Choose three.)
Show answer and explanation
Correct answer: A, D, G
A. printer_name# D. leap$year G. v_fname Valid PL/SQL variable names must start with a letter or underscore and can contain letters, digits, and underscores. 'printer_name#' contains the special character '#' but starts with a letter (A is valid). '1to7number' starts with a digit, which is invalid (B is invalid). 'yesterday's_date' contains a single quote, which is not allowed (C is invalid). 'leap$year' contains the '$' character, which is valid in PL/SQL identifiers (D is valid). 'Number_of_days_between_March_and_April' follows naming rules and is valid (E is valid, but not selected). '#printer_name' starts with '#', which is invalid (F is invalid). 'v_fname' is a standard valid variable name (G is valid).
Why the other options are wrong
- B. Variable names cannot start with a digit.
- C. Variable names cannot contain single quotes or apostrophes.
- E. While this name is technically valid, it is not among the three selected correct answers in the reference.
That was 10 of 65.
The full Oracle 1Z0-149 pack has all 65 questions, each with the answer, the explanation and why the other options are wrong, plus a questions-only copy for timed runs. US$39, paid once, with free monthly updates and a pass-or-your-money-back guarantee.