2 packs: 15% off with code BUY2. 3 or more: 20% off with code BUY3.

Free Oracle 1Z0-149 Practice Questions

16 free Oracle 1Z0-149 practice questions from the CertStash pack of 65, each with the correct answer and a full explanation. The rule is the same for every exam: ten free on the product page, 16 free here, all 65 in the pack, and each step includes the one before it.

Get all 65 questions (US$39) · Download the first 10 as a PDF

Question 1

Which two are true about collections and RECORD types? (Choose two.)

  1. A variable of RECORD type can contain fields of another RECORD type or any collection type.
  2. Only associative arrays and nested tables can have elements of RECORD type.
  3. All collections and RECORD types can be defined in PL/SQL blocks, packages, or at the schema level.
  4. Collections and RECORD types are always dense.
  5. All collections and RECORD types can be stored in table columns.
  6. VARRAYS, nested tables and each field in %ROWTYPE type variables have a default value of null.
Show answer and explanation

Correct answer: A, F. A variable of RECORD type can contain fields of another RECORD type or any collection type; VARRAYS, nested tables and each field in %ROWTYPE type variables have a default value of null.

Option A is correct: RECORD type variables can contain fields of other RECORD types (nested records) or any collection type (varrays, nested tables, or associative arrays). Option F is correct: VARRAYs and nested tables have a default value of null for their elements, and fields in %ROWTYPE variables default to null. Option B is wrong because all three collection types can contain RECORD types. Option C is wrong because associative arrays cannot be defined at the schema level. Option D is wrong because associative arrays are sparse collections. Option E is wrong because only nested tables and varrays can be stored in table columns, not associative arrays or RECORD types as top-level column types.

Why the other options are wrong

  • B. All three collection types (varrays, nested tables, and associative arrays) can contain elements of RECORD type.
  • C. Associative arrays cannot be defined at the schema level; only varrays and nested tables can be.
  • D. Associative arrays are sparse collections and do not require contiguous index values.
  • E. Associative arrays cannot be stored in table columns; only nested tables and varrays can be.

Question 2

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

  1. Internally defined exceptions can be handled only by the OTHERS exception handler.
  2. All declared exceptions are raised implicitly by the runtime system.
  3. User-defined exceptions can be defined in the declarative part of any PL/SQL anonymous block, subprogram, or package.
  4. Only predefined exceptions and user-defined exceptions can have a user-declared name associated with them.
  5. Predefined exceptions are globally declared in the standard package.
Show answer and explanation

Correct answer: C, E. User-defined exceptions can be defined in the declarative part of any PL/SQL anonymous block, subprogram, or package; Predefined exceptions are globally declared in the standard package.

Option C is correct: user-defined exceptions can be declared in the declarative section of any PL/SQL construct including anonymous blocks, subprograms, and packages. Option E is correct: predefined exceptions such as NO_DATA_FOUND and ZERO_DIVIDE are globally declared in the STANDARD package and available throughout PL/SQL. Option A is wrong because internally defined (predefined) exceptions can be handled by specific exception handlers before falling back to OTHERS. Option B is wrong because user-defined exceptions must be raised explicitly by the programmer. Option D is wrong because only predefined and user-defined exceptions can have names; internally caught conditions may not have explicit names available to the programmer.

Why the other options are wrong

  • A. Predefined exceptions can be caught by their specific exception handlers, not only by OTHERS.
  • B. User-defined exceptions must be explicitly raised by the programmer, not implicitly by the runtime system.
  • D. Internally defined exceptions can exist without user-declared names in some contexts.

Question 3

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

  1. PACKAGES
  2. VARIABLES
  3. ANONYMOUS BLOCK
  4. TRIGGER BODY
  5. DATABASE LINKS
Show answer and explanation

Correct answer: A, B. PACKAGES; VARIABLES

The DEPRECATE pragma in PL/SQL can be applied to packages and procedures/functions (which are typically organized within packages). This pragma marks code elements as deprecated to warn developers that they should migrate away from using them. Packages are explicitly deprecable, as are the procedures and functions they contain. Variables, anonymous blocks, trigger bodies, and database links cannot be deprecated using this pragma.

Why the other options are wrong

  • C. Anonymous blocks cannot be deprecated with the DEPRECATE pragma.
  • D. Trigger bodies cannot be deprecated with the DEPRECATE pragma.
  • E. Database links cannot be deprecated with the DEPRECATE pragma.

Question 4

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

  1. The ACCESSIBLE BY clause can be used only for procedures.
  2. In a function, every execution path must lead to a RETURN statement.
  3. Both can have only constants as actual parameters for IN mode parameters.
  4. Both can be invoked from within SQL statements.
  5. In a procedure the RETURN statement cannot specify an expression.
  6. In a function every RETURN statement must specify an expression.
Show answer and explanation

Correct answer: B, E, F. In a function, every execution path must lead to a RETURN statement; In a procedure the RETURN statement cannot specify an expression; In a function every RETURN statement must specify an expression.

In PL/SQL, every execution path within a function must reach a RETURN statement (B is true). A procedure can use RETURN without an expression to exit early, but in a function, every RETURN must specify an expression to return a value (E and F are true). The ACCESSIBLE BY clause can be used for both procedures and functions, not just procedures. Both can accept IN parameters of any data type, not just constants. Functions can be invoked in SQL queries while procedures typically cannot be called directly from SQL statements.

Why the other options are wrong

  • A. The ACCESSIBLE BY clause can be used for both procedures and functions.
  • C. Both functions and procedures can have variables and expressions as actual parameters, not just constants.
  • D. Both functions and procedures cannot be invoked from within SQL statements; only functions can be called in SQL queries.

Question 5

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

  1. PL/SQL code can be compiled and executed based on different versions of the operating system.
  2. PL/SQL code can be compiled and executed based on different versions of Oracle.
  3. It is newer syntax that works the same way as 'IF , ELSEIF , ELSE, and END IF'.
  4. Conditional compilation is disabled by default.
  5. The PL/SQL compiler can conditionally include selected parts of a program.
Show answer and explanation

Correct answer: B, E. PL/SQL code can be compiled and executed based on different versions of Oracle; 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). The PL/SQL compiler can conditionally include selected parts of a program based on compilation flags and conditions (E is true). This is a compile-time feature that works differently from regular IF statements, it operates during compilation, not execution. Conditional compilation is enabled by default in modern Oracle versions and is not syntax-equivalent to IF/ELSE/END IF statements. It does not depend on operating system versions.

Why the other options are wrong

  • A. Conditional compilation is based on Oracle database versions, not operating system versions.
  • C. Conditional compilation is fundamentally different from IF/ELSEIF/ELSE/END IF; it operates at compile time, not runtime.
  • D. Conditional compilation is enabled by default in Oracle databases, not disabled.

Question 6

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

  1. The PARALLEL_ENABLE clause can be used only in the CREATE FUNCTION statement.
  2. The NOCOPY hint asks the compiler to pass the actual parameters by reference.
  3. A deterministic function's results always depend on the state of session variables.
  4. The NOCOPY hint asks the compiler to pass the actual parameters by value.
  5. A function is deterministic if it always returns the same result for a specific combination of input values.
  6. The PARALLEL_ENABLE clause can be specified for a nested function.
  7. A function defined with the PARALLEL_ENABLE clause may be executed in parallel in a SELECT statement or a subquery in a DML statement.
Show answer and explanation

Correct answer: B, E, G. The NOCOPY hint asks the compiler to pass the actual parameters by reference; A function is deterministic if it always returns the same result for a specific combination of input values; 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 asks the compiler to pass actual parameters by reference rather than by value, improving performance for large data 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 only be specified in CREATE FUNCTION statements for standalone functions, not nested functions. A deterministic function's results depend exclusively on input parameters, not session variables.

Why the other options are wrong

  • A. PARALLEL_ENABLE can be used in CREATE FUNCTION for standalone functions; it cannot be used for nested functions.
  • C. A deterministic function's results depend on input parameters only, not on session variable state.
  • D. NOCOPY passes parameters by reference, not by value.
  • F. PARALLEL_ENABLE cannot be specified for nested functions, only for standalone functions created with CREATE FUNCTION.

Question 7

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

  1. The index can be integer or string.
  2. The index can be integer only.
  3. INDEX-BY table types can be created in PL/SQL blocks only.
  4. INDEX-BY table types can be created both with the CREATE TYPE statement and in PL/SQL blocks.
  5. INDEX-BY table types can be created with the CREATE TYPE statement.
Show answer and explanation

Correct answer: A, C. The index can be integer or string; INDEX-BY table types can be created in PL/SQL blocks only.

INDEX-BY tables can have either integer or string indexes, providing flexible indexing options (A is true). INDEX-BY table types can only be created within PL/SQL blocks using the TYPE statement; they cannot be created with the CREATE TYPE statement at the schema level (C is true). This limitation exists because INDEX-BY tables are PL/SQL-specific structures designed for use within procedural code rather than as persistent database types.

Why the other options are wrong

  • B. INDEX-BY tables can use both integer and string indexes, not integer only.
  • D. INDEX-BY table types cannot be created with the CREATE TYPE statement; they are PL/SQL-specific.
  • E. INDEX-BY table types cannot be created with the CREATE TYPE statement.

Question 8

Which three are true about anonymous blocks and subprograms? (Choose three.)

  1. Named subprograms cannot be called from other packages.
  2. PROCEDURE subprograms can accept parameters.
  3. A FUNCTION subprogram must return one or more values.
  4. Anonymous blocks cannot use packaged variables.
  5. Named subprograms are stored in the database server.
  6. Anonymous blocks must always start with the Declare keyword.
  7. FUNCTION subprograms must be called and passed through one or more parameters.
Show answer and explanation

Correct answer: B, C, E. PROCEDURE subprograms can accept parameters; A FUNCTION subprogram must return one or more values; Named subprograms are stored in the database server.

PROCEDURE subprograms can accept parameters of various modes (IN, OUT, IN OUT), enabling flexible data passing (B is true). A FUNCTION subprogram must return exactly one value of a specified datatype (C is true). Named subprograms are stored in the database server and can be called from other packages, providing reusable code (E is true). Anonymous blocks can reference and use packaged variables. FUNCTION subprograms do not require parameters to be passed; parameters are optional. Anonymous blocks do not require the DECLARE keyword if they contain only executable statements.

Why the other options are wrong

  • A. Named subprograms can be called from other packages.
  • D. Anonymous blocks can use packaged variables.
  • F. Anonymous blocks do not require the DECLARE keyword; they can start with BEGIN if there are no declarations.
  • G. FUNCTION subprograms do not require parameters to be passed; they can be called without parameters.

Question 9

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

  1. Function based indexes remain usable when replacing the function on which the index depends.
  2. Object privileges to execute a replaced function must be regranted to those users who had the privilege.
  3. This clause can be used only for procedures and functions.
  4. A function definition can be modified without dropping and re-creating it.
  5. Object privileges to execute a replaced function are retained by those users who had the privileges.
Show answer and explanation

Correct answer: D, E. 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.

Using OR REPLACE allows a function definition to be modified without dropping and re-creating it (D is true). When a function is replaced with OR REPLACE, object privileges to execute the function are retained by users who previously had them, avoiding the need to regranted privileges (E is true). The OR REPLACE clause can be used for procedures, functions, and packages. Function-based indexes remain valid when the function is replaced only if the new function is compatible with the old one. This clause streamlines the process of updating stored code while maintaining security privileges.

Why the other options are wrong

  • A. Function-based indexes may become invalid if the replaced function has incompatible changes.
  • B. Object privileges are retained and do not need to be regranted when using OR REPLACE.
  • C. The OR REPLACE clause can be used for procedures, functions, and packages, not just procedures and functions exclusively.

Question 10

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

  1. printer_name#
  2. 1to7number
  3. yesterday's_date
  4. leap$year
  5. Number_of_days_between_March_and_April
  6. #printer_name
  7. v_fname
Show answer and explanation

Correct answer: A, D, G. printer_name#; leap$year; v_fname

Valid PL/SQL variable names must start with a letter or underscore, followed by letters, digits, underscores, or dollar signs. printer_name# is valid as it starts with a letter and uses allowed characters (A). leap$year is valid as it starts with a letter and contains an allowed dollar sign (D). v_fname is valid as it starts with a letter and uses underscores (G). The number 1 cannot start a variable name, so 1to7number is invalid. An apostrophe is not allowed in variable names, making yesterday's_date invalid. A hash symbol cannot begin a variable name, so #printer_name is invalid.

Why the other options are wrong

  • B. Variable names cannot start with a digit.
  • C. Variable names cannot contain apostrophes.
  • F. Variable names cannot start with a hash symbol.

What people say about the full 1Z0-149 pack 5.0 from 1

This material kept everything straight to the point. It didn’t waste time with extra talk, which helped me cover more in less time.
Aayush Chaturvedi1Z0-149

Question 11

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

  1. Results of a subprogram can be cached in the SGA such that sessions connected to the same instance can reuse these results when available.
  2. 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.
  3. Subprograms are cached by default and shared among users, thereby reducing memory requirements.
  4. Reuse of parsed PL/SQL code from the shared SQL area reduces parsing overhead.
  5. A subprogram's session state is retained even if any of the session’s instantiated subprograms are invalidated and revalidated.
  6. Host variables can be referenced inside any PL/SQL subprogram.
  7. A PL/SQL procedure can invoke an external code block written in a different programming language.
Show answer and explanation

Correct answer: A, D, G. Results of a subprogram can be cached in the SGA such that sessions connected to the same instance can reuse these results when available; Reuse of parsed PL/SQL code from the shared SQL area reduces parsing overhead; A PL/SQL procedure can invoke an external code block written in a different programming language.

Subprogram results can be cached in the Result Cache component of the SGA when the RESULT_CACHE clause is used, allowing reuse across sessions on the same instance, making A correct. Subprograms are cached in the shared SQL area by default and shared among users, reducing memory requirements, making D correct. PL/SQL procedures can invoke external code written in different programming languages through external procedures and Java integration, making G correct. With definer's rights, the definer's privileges are used, so grantees need not have direct object grants. Session state is not retained if subprograms are invalidated and revalidated. Host variables cannot be referenced inside PL/SQL subprograms at the server; they are client-side constructs.

Why the other options are wrong

  • B. With definer's rights, the procedure executes with the definer's privileges, so callers do not need grants to objects referenced in the procedure
  • C. Subprograms are cached but not mandatory by default; caching behavior depends on configuration and explicit RESULT_CACHE use
  • E. Session state is not retained after invalidation and revalidation; session-specific data is lost during this process
  • F. Host variables are client-side constructs and cannot be referenced inside server-side PL/SQL subprograms

Question 12

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

  1. The salary will be changed for employee 100 in the EMPLOYEES table owned by ORA41.
  2. No update happens even though the procedure executes successfully.
  3. The salary will be changed for employee 100 in the EMPLOYEES table owned by ORA61.
  4. The UPDATE privilege on ORA41.EMPLOYEES is not inherited by ORA61 through the procedure.
  5. ORA61 will have been granted the UPDATE privilege explicitly on ORA41.EMPLOYEES before executing the statement.
Show answer and explanation

Correct answer: A, B. The salary will be changed for employee 100 in the EMPLOYEES table owned by ORA41; No update happens even though the procedure executes successfully.

When user ORA61 executes the procedure update_emp_proc owned by ORA41 with AUTHID CURRENT_USER, the procedure runs with the privileges of the invoker (ORA61), not the definer (ORA41). Since ORA61 was not granted explicit UPDATE privileges on the EMPLOYEES table owned by ORA41, and definer's rights are not invoked here, the procedure executes successfully without error but the UPDATE statement within it fails silently due to lack of privileges. Therefore, no actual update occurs to the table even though the EXECUTE command completes successfully. The AUTHID CURRENT_USER clause is the key, it causes the procedure to execute in the security context of the caller rather than the owner.

Why the other options are wrong

  • C. The update would target ORA41's EMPLOYEES table (as specified in the procedure), not ORA61's table, and moreover no update occurs due to insufficient privileges.
  • D. Privileges are not inherited through procedure execution with AUTHID CURRENT_USER; the caller must have direct privileges, which is why the update fails.
  • E. ORA61 was not granted explicit UPDATE privileges; if they had been, the update would succeed, but the question indicates it executes successfully without such a grant, meaning the UPDATE silently fails.

Question 13

Which three are true about DDL triggers? (Choose three.)

  1. They cannot include the WHEN clause.
  2. They must be created in an enabled state.
  3. They can be fired when a table is truncated.
  4. They fire only when a DDL statement is executed by the owner of the trigger.
  5. They can be fired either before or after a DDL statement executes.
  6. They can be fired when a privilege is granted to a user.
  7. They must be created in a disabled state.
Show answer and explanation

Correct answer: A, C, E. They cannot include the WHEN clause; They can be fired when a table is truncated; They can be fired either before or after a DDL statement executes.

DDL triggers cannot include a WHEN clause, making A correct. They fire when a table is truncated because TRUNCATE is a DDL statement, making C correct. DDL triggers can fire either BEFORE or AFTER a DDL statement executes, making E correct. Triggers are created in an enabled state by default unless explicitly disabled. DDL triggers fire on any DDL statement within their scope, regardless of who executes it, not just the owner. DDL triggers do not fire on privilege grants; those would require system event triggers on GRANT operations.

Why the other options are wrong

  • B. DDL triggers are created in an enabled state by default, not a disabled state
  • D. DDL triggers fire on DDL statements executed by any user with appropriate privileges, not only the trigger owner
  • F. DDL triggers do not fire on GRANT statements; those require system event triggers, not DDL triggers
  • G. DDL triggers are created in an enabled state by default, not disabled

Question 14

For which three SYSTEM EVENTS can triggers be created? (Choose three.)

  1. DDL
  2. AFTER AUDIT
  3. BEFORE ANALYZE
  4. SHUTDOWN
  5. SERVERERROR
  6. STARTUP
  7. BEFORE GRANT
Show answer and explanation

Correct answer: D, E, F. SHUTDOWN; SERVERERROR; STARTUP

Database triggers can be created on three categories of system events: STARTUP (fires when the database starts), SHUTDOWN (fires when the database shuts down), and SERVERERROR (fires when a server error occurs). These are valid system events at the database level. DDL events like ANALYZE and GRANT can trigger DDL triggers, but BEFORE ANALYZE and BEFORE GRANT are not valid system event trigger types, DDL triggers fire on the DDL statement itself, not on specific operations like ANALYZE or GRANT as separate system events. AFTER AUDIT is not a recognized system event trigger type.

Why the other options are wrong

  • A. DDL is not a system event for trigger creation; DDL triggers exist but DDL itself is not listed as a system event category
  • B. AFTER AUDIT is not a valid system event trigger type
  • C. BEFORE ANALYZE is not a valid system event trigger type; ANALYZE is a DDL statement but not a standalone system event
  • G. BEFORE GRANT is not a valid system event trigger type; GRANT is a DDL statement but not a standalone system event

Question 15

In which type of trigger can :OLD and :NEW identifiers be used?

  1. ROW
  2. AFTER SUSPEND
  3. AFTER STATEMENT
  4. BEFORE STATEMENT
Show answer and explanation

Correct answer: A. ROW

The :OLD and :NEW identifiers can only be used in row-level triggers (ROW type). These identifiers reference the old and new values of columns in the specific row being affected by the DML statement. Statement-level triggers do not have access to :OLD and :NEW because they fire once for the entire SQL statement, not once per row. AFTER SUSPEND is not a standard trigger type.

Why the other options are wrong

  • B. AFTER SUSPEND is not a standard trigger type, and :OLD and :NEW cannot be used in it
  • C. AFTER STATEMENT triggers fire at the statement level and cannot reference :OLD and :NEW
  • D. BEFORE STATEMENT triggers fire at the statement level and cannot reference :OLD and :NEW

Question 16

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

  1. a record declared in an anonymous block
  2. a record declared in a procedure
  3. an associative array indexed by PLS_INTEGER
  4. a record declared in a package specification
  5. a predefined PL/SQL-only data type like BOOLEAN
  6. an associative array indexed by VARCHAR2
Show answer and explanation

Correct answer: C, D, F. an associative array indexed by PLS_INTEGER; a record declared in a package specification; an associative array indexed by VARCHAR2

Associative arrays indexed by PLS_INTEGER can be used in queries and native dynamic SQL from PL/SQL in the server, making C correct. Records declared in package specifications are accessible from anywhere in the session and can be used in queries and native dynamic SQL, making D correct. Associative arrays indexed by VARCHAR2 can also be used in queries and native dynamic SQL from PL/SQL in the server, making F correct. Records declared in anonymous blocks or procedures are local to their scope and cannot be used in server-side queries or native dynamic SQL. Predefined PL/SQL-only types like BOOLEAN cannot be used in SQL queries.

Why the other options are wrong

  • A. Records declared in anonymous blocks are local to that block and cannot be used in queries or native dynamic SQL
  • B. Records declared in procedures are local to that procedure and cannot be used in queries or native dynamic SQL
  • E. Predefined PL/SQL-only data types like BOOLEAN cannot be used in SQL queries or native dynamic SQL

That was 16 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.

Get the full pack

Not ready yet? Get the first 33 free as one PDF by email. Get the Half Pack →