1Z0-149 Verified 2026 Edition

Oracle Database Program with PL/SQL Practice Test

1Z0-149 earns the Oracle Database PL/SQL Developer Certified Professional credential, replacing the older 1Z0-148 exam. It requires the Oracle Database SQL certification first and suits developers writing PL/SQL on Oracle Database 19c. PL/SQL variables and control structures, cursors, procedures and packages, and dynamic SQL are the main areas of the practice test.

320 Total Questions
2 Included Versions Get all versions for the price of one
English Edition
$80k–$125k Typical Salary An estimate of yearly pay in the United States for PL/SQL developers, Oracle database developers and database application developers.
All-In-One Bundle
$79.99
$75.99
  • Interactive Simulator & AI
  • Detailed Explanations
  • Study, Timed & Flashcard Mode
  • Lifetime Access & Updates

Instant lifetime access • Secure checkout

Why Study with PlanetCert?

The Latest Questions

Practice questions and exam topics aligned with the current exam objectives.

Detailed Explanations

Go beyond the answer. Master the material with comprehensive learning and professional explanations for every concept.

AI

AI-Powered Insights

Personalized preparation guidance that adapts to your performance and identifies weak spots automatically.

Exam Information

Official specifications published by Oracle

Exam Format

90 min
65
66%
Professional

Registration

$245 USD
Pearson VUE or online proctoring

Validity

Lifetime (no recertification required)
Not required - certification does not expire; Consider pursuing advanced Oracle certifications; Stay updated with Oracle Cloud certifications

Skills this practice test covers

  • Declare variables with %TYPE and %ROWTYPE so the code follows changes to table columns.
  • Fetch rows with explicit cursors, cursor FOR loops and cursors that take parameters.
  • Trap predefined and user-defined exceptions, and trace how they propagate through nested blocks.
  • Split a package into a public specification and a private body, with overloaded subprograms.
  • Process many rows at once with BULK COLLECT and FORALL.
  • Create triggers that fire on DDL statements and on system events such as a logon.

Explore related topics

1Z0-149 exam content

18 domains · 70 topics · 238 of 320 questions are written for 1Z0-149

Declaring PL/SQL Variables9 questions

This domain covers the declaration section of a block. Candidates decide which identifiers are legal, declare and initialize variables, use bind variables, and anchor data types with %TYPE and %ROWTYPE.

Skills tested

  • Tell valid PL/SQL identifiers from invalid ones.
  • Declare and initialize variables, and reference bind variables in a block.
  • Anchor a variable to a column with %TYPE, or a record to a row with %ROWTYPE.

Questions by topic

  • %TYPE Attribute5
  • PL/SQL Block Structure1
  • Subtypes and Data Type Constraints2
  • %ROWTYPE Attribute1
Writing Executable Statements8 questions

This domain covers the statements between BEGIN and END. Candidates name lexical units, use SQL functions and sequences in expressions, and predict when Oracle converts a data type by itself and when the code must convert it.

Skills tested

  • Identify the lexical units in a block: delimiters, identifiers, literals and comments.
  • Use built-in SQL functions and sequences in PL/SQL expressions.
  • Write nested blocks, and qualify a variable with a block label when two names clash.

Questions by topic

  • Implicit Conversions3
  • Explicit Conversions2
  • Lexical Units2
  • PL/SQL Block Structure1
Writing SQL in PL/SQL10 questions

This domain joins SQL and PL/SQL. Candidates change data with DML inside a block, end the transaction with COMMIT or ROLLBACK, and use the INTO clause to hold the values that a query returns.

Skills tested

  • Run INSERT, UPDATE and DELETE statements inside a PL/SQL block.
  • Select one row into variables with the INTO clause, and handle NO_DATA_FOUND and TOO_MANY_ROWS.

Questions by topic

  • RETURNING Clause5
  • Transaction Control3
  • DML in PL/SQL1
  • Implicit vs Explicit Cursors1
Writing Control Structures10 questions

This domain is about the flow of a program. Candidates choose between IF and CASE, tell a CASE statement from a CASE expression, and build loops that stop or skip ahead at the right time.

Skills tested

  • Write IF-THEN-ELSIF logic and CASE statements.
  • Use a CASE expression to return a value inside an assignment.
  • Build basic, WHILE and FOR loops, and control them with EXIT WHEN and CONTINUE WHEN.
  • Label nested loops so that an EXIT statement leaves the right one.

Questions by topic

  • CASE Statements and Expressions4
  • CASE Expressions1
  • Loop Labels and EXIT1
  • Loop Control with Labels2
  • Loop Control2
Working with Composite Data Types18 questions

This domain covers data types that hold more than one value. Candidates build user-defined records, %ROWTYPE records and INDEX BY tables, and explain how records, collections and collections of records differ.

Skills tested

  • Define a record type and read its fields with dot notation.
  • Create an INDEX BY table and an INDEX BY table of records.
  • Initialize collections, and use collection methods such as COUNT, EXISTS, FIRST and LAST.
Using Explicit Cursors14 questions

This domain shows how a program works through a query result row by row. Candidates tell implicit cursors from explicit ones, read the cursor attributes, and lock the rows they plan to change.

Skills tested

  • Control an explicit cursor with OPEN, FETCH and CLOSE, and end the loop with %NOTFOUND.
  • Replace manual cursor handling with a cursor FOR loop.
  • Declare a cursor with parameters, and open it with different values.
  • Lock rows with FOR UPDATE, and change the current row with WHERE CURRENT OF.
Handling Exceptions14 questions

This domain covers errors at run time. Candidates handle predefined, internally defined and user-defined exceptions, and trace how an unhandled exception passes out to the enclosing block.

Skills tested

  • Trap predefined exceptions such as NO_DATA_FOUND and DUP_VAL_ON_INDEX.
  • Give an internally defined error a name with PRAGMA EXCEPTION_INIT.
  • Declare a user-defined exception and raise it.
  • Predict where an exception goes when the current block has no handler for it.

Questions by topic

  • Predefined Exceptions (TOO_MANY_ROWS)2
  • Exception Propagation6
  • PRAGMA EXCEPTION_INIT2
  • SQLCODE and SQLERRM1
  • NO_DATA_FOUND vs %NOTFOUND3
Using PL/SQL Subprograms13 questions

This domain moves from anonymous blocks to named code stored in the database. Candidates explain how a subprogram differs from an anonymous block, and why stored subprograms make code easier to reuse.

Skills tested

  • Compare anonymous blocks with named subprograms.
  • Create a simple procedure and call it from an anonymous block.

Questions by topic

  • Local Subprograms5
  • Benefits of Subprograms4
  • Differentiating Anonymous Blocks and Subprograms2
  • Autonomous Transactions1
  • Invoker's Rights (AUTHID CURRENT_USER)1
Creating Procedures and Using Parameters13 questions

This domain covers procedures that take parameters. Candidates choose a parameter mode, call procedures with named notation, and handle exceptions inside them.

Skills tested

  • Choose the parameter mode for each value: IN, OUT or IN OUT.
  • Call a procedure with named notation, and rely on default parameter values.
  • Create, run and drop procedures, and display a procedure's information.

Questions by topic

  • Parameter Modes7
  • Named Notation1
  • NOCOPY Hint1
  • Default Parameter Values4
Creating Functions11 questions

This domain covers stored functions, which return one value to the caller. Candidates tell a function from a procedure, and call functions in PL/SQL expressions and in SQL statements.

Skills tested

  • Write a function with a RETURN clause in its heading and a RETURN statement in its body.
  • Call a function from a SQL statement, within the rules for functions used in SQL.
  • Create, invoke and drop functions.

Questions by topic

  • Differentiating Procedures and Functions2
  • Uses of Functions3
  • Function Purity and SQL Usage4
  • RETURN Statement1
  • Default Parameter Values1
Creating Packages15 questions

This domain introduces packages, which group related types, variables and subprograms. Candidates write a specification and a body, overload subprograms, and use forward declarations.

Skills tested

  • Write a package specification and a matching package body.
  • Keep helper subprograms private by leaving them out of the specification.
  • Overload subprograms, and add forward declarations inside a package body.

Questions by topic

  • Forward Declarations4
  • Overloading3
  • Benefits of Packages2
  • Package Specification vs Body2
  • Package Initialization Block2
  • Package State Management2
Working with Packages12 questions

This domain is about using packages in running code. Package variables keep their values for the whole session, so candidates must predict that package state. They also call packaged constants and functions from SQL, and limit callers with ACCESSIBLE BY.

Skills tested

  • Use package types and variables, and predict their values across calls in one session.
  • Use packaged constants and functions inside SQL statements.
  • Restrict which program units may call a package subprogram with the ACCESSIBLE BY clause.
Using Dynamic SQL12 questions

This domain covers SQL text that is built while the program runs. Candidates follow how Oracle parses, binds, executes and fetches a statement, and run dynamic statements with Native Dynamic SQL (NDS).

Skills tested

  • Run dynamic DDL and DML with EXECUTE IMMEDIATE.
  • Pass values into a dynamic statement through bind variables.
  • Compare Native Dynamic SQL with the DBMS_SQL package.

Questions by topic

  • Native Dynamic SQL (NDS) for DDL4
  • DBMS_SQL vs Native Dynamic SQL2
  • Bind Variables4
  • Native Dynamic SQL (NDS)2
Design Considerations for PL/SQL Code31 questions

This domain covers choices that make PL/SQL code safer and faster. Candidates set run-time privileges with the AUTHID clause, use autonomous transactions, and cut the number of calls between the PL/SQL and SQL engines with bulk binding.

Skills tested

  • Create standard constants and exceptions in a package that other code shares.
  • Choose definer rights or invoker rights for a subprogram with the AUTHID clause.
  • Write an autonomous transaction that commits on its own, such as an error log.
  • Fetch and change many rows at once with BULK COLLECT, FORALL and the RETURNING clause.
  • Decide when the NOCOPY hint, PARALLEL_ENABLE and the DETERMINISTIC clause help.

Questions by topic

  • Using DETERMINISTIC and Result Cache3
  • Using the NOCOPY Hint6
  • Autonomous Transactions3
  • Autonomous Transactions and Bulk Binding2
  • Bulk Binding4
  • Invoker's Rights (AUTHID CURRENT_USER)4
  • Bulk Binding with FORALL3
  • RETURNING Clause1
  • Result Cache3
  • Local Subprograms2
Creating Compound, DDL, and Event Database Triggers12 questions

This domain goes beyond DML triggers on tables. Candidates write triggers that fire on DDL statements and on system events, and describe each trigger type and its uses, including compound triggers.

Skills tested

  • Create a trigger that fires before or after a DDL statement such as CREATE or DROP.
  • Create a trigger on a system event, such as a logon or a database startup.
  • Describe what a compound trigger is and which timing points it joins in one unit.

Questions by topic

  • DML Triggers and User-Defined Exceptions3
  • DDL Triggers3
  • System Event Triggers5
  • Compound Triggers1
Using the PL/SQL Compiler10 questions

This domain covers how Oracle compiles PL/SQL. Candidates set compiler initialization parameters such as PLSQL_OPTIMIZE_LEVEL, and turn on compile-time warnings to catch weak code before it runs.

Skills tested

  • Set PLSQL_OPTIMIZE_LEVEL and other compiler initialization parameters.
  • Enable compile-time warnings with PLSQL_WARNINGS, and read the messages they produce.

Questions by topic

  • PL/SQL Compile-Time Warnings5
  • Conditional Compilation2
  • PLSQL_OPTIMIZE_LEVEL Parameter3
Managing PL/SQL Code15 questions

This domain covers keeping code under control across versions and callers. Candidates use conditional compilation, grant roles to program units, whitelist callers with ACCESSIBLE BY, and mark old code as deprecated.

Skills tested

  • Write conditional compilation with the $IF, $THEN, $ELSE and $END directives.
  • Grant a role to a program unit for code-based access control.
  • Mark a subprogram as deprecated with the deprecation pragma, so code that uses it can get a compile-time warning.

Questions by topic

  • Conditional Compilation4
  • Using the ACCESSIBLE BY Clause3
  • Deprecation Pragma6
  • ACCESSIBLE BY Clause2
Managing Dependencies11 questions

This domain covers what happens when an object that code relies on changes. Candidates track direct and indirect dependencies, and predict which program units become invalid.

Skills tested

  • Find an object's dependencies in data dictionary views such as USER_DEPENDENCIES.
  • Predict which procedures become invalid after a table change, and recompile them.

Questions by topic

  • Dependency Tracking and Invalidation6
  • Package Dependencies1
  • Dependency Invalidation and Recompilation4
Practise these questions in study mode

Requirements

  • Required for the certification: a pass in Oracle Database SQL too, before or after this exam.
  • Recommended: working SQL knowledge and a basic grasp of database concepts.
  • Recommended: hands-on practice writing PL/SQL programs before the exam.

About the 1Z0-149 exam

1Z0-149 tests whether a developer can write, store and manage PL/SQL programs in Oracle Database 19c. PL/SQL runs SQL statements inside blocks that add variables, conditions, loops and exception handlers. Oracle's topics run from declarations and control structures to packages, dynamic SQL, compiler settings and dependency tracking. Passing it together with Oracle Database SQL earns the title Oracle Database PL/SQL Developer Certified Professional.

The PlanetCert practice test gives the most questions to Design Considerations for PL/SQL Code, where NOCOPY, bulk binding, invoker's rights and autonomous transactions change how code runs. Each explanation shows why the right answer works. That matters most for questions that ask what a nested block prints or which handler catches an error. Study Mode suits the cursor and exception topics. Brainy AI Assistant can trace a block line by line, or explain why a change to a table left a procedure invalid.

How to earn the certification

Earns
Oracle Database PL/SQL Developer Certified ProfessionalProfessional

The certification does not expire.

Before

How to prepare for 1Z0-149

  1. Set up a free practice database

    Run every example in a real database, such as Oracle Live SQL in a browser or the free Express Edition. PL/SQL details, such as when an exception fires, are easiest to learn by running the code.

  2. Turn each Oracle bullet into code

    Oracle's exam page lists the topics as short bullet points, from identifiers to dependencies. Write one small block for each bullet, then change it and predict the new result before running it.

  3. Predict output before running blocks

    Read each code sample and predict its result first. Trace nested blocks to see which handler catches an error, and follow cursor loops fetch by fetch with %NOTFOUND and %ROWCOUNT.

  4. Give design topics extra time

    Design Considerations for PL/SQL Code has the most questions in this practice test. Build examples with AUTHID, autonomous transactions, BULK COLLECT, FORALL and NOCOPY, and compare what each one changes.

  5. End with timed practice runs

    Take the 1Z0-149 practice test in Timed Mode once every topic has a working example. For each missed question, ask Brainy AI Assistant to walk through the code line by line.

    Start a timed 1Z0-149run

Oracle University's learning path for this exam is Earn the Oracle Database PL/SQL Developer Certified Professional Credential. It is part of the Technology Learning Subscription.

Who 1Z0-149 is for

  • Developers who build Oracle Database back ends with procedures, functions and packages.
  • SQL developers who hold Oracle Database SQL and want the professional PL/SQL title.
  • Application developers who move business rules from the application into the database.
  • DBAs who write DDL and system event triggers, or fix PL/SQL objects left invalid by a change.

Typical job titles: PL/SQL developer · Oracle database developer · Database application developer · Back-end developer · Database analyst

Your Complete Exam Solution

Best-In-Class Practice Tests

Exam-style questions that match the format and difficulty of the real exam, updated regularly. Original material — not recycled dumps.

Topical Breakdown

Study by domain, pinpoint weak areas, and focus your time where it matters most. Every topic mapped to the official syllabus.

Flashcard Mode

Rapid-fire review to reinforce key concepts. Flip through questions and answers at your own pace before exam day.

See How You Compare Against Yourself

✕
✕
✕

Other Exam Prep

  • Outdated question dumpsRecycled, often inaccurate material
  • No explanationsMemorize answers without understanding
  • Static PDF filesNo interactive practice or feedback
  • Subscription feesRecurring charges, access expires
✓
✓
✓

PlanetCert

  • Verified, current questionsUpdated weekly to match live exam objectives
  • Expert-written rationalesUnderstand every concept, not just the answer
  • Session tracking & exam progressTrack every attempt, see your growth over time
  • Lifetime access, one pricePay once — updates included forever
CERTIFIED1Z0-149

Study Naturally, Study Responsibly

Practice with exam-style questions and detailed explanations until you're ready.

Try Free Demo
Secure Checkout Lifetime Access