What Does an Oracle PL/SQL Developer Actually Do?
PL/SQL is Oracle’s procedural extension of SQL, adding variables, loops, conditional logic, and exception handling to standard queries. A developer in this role spends the day declaring cursors to process result sets row by row, wrapping related routines into packages, and tuning code so that bulk operations replace slow one-row-at-a-time processing. For a fuller history of PL/SQL and its place among Oracle technologies, the language has been part of the platform since Oracle version 6.
Because the logic lives in the database, one well-written package can serve web apps, reporting tools, and batch jobs at once. That reuse is why organizations value certified developers who can prove they write maintainable, secure, high-performance code.
Table of Contents
- What Does an Oracle PL/SQL Developer Actually Do?
- What Does the 1Z0-149 Exam Cover?
- How Do Anonymous Blocks and Stored Procedures Work?
- Why Do Packages Matter for a PL/SQL Developer?
- How Do Triggers and Dynamic SQL Extend the Database?
- What Is the Best Way to Handle Cursors and Exceptions?
- How Should You Prepare for the 1Z0-149 Exam?
- What Career Paths Follow the 1Z0-149?
- Frequently Asked Questions
- Your Next Step Toward PL/SQL Mastery
What Does the 1Z0-149 Exam Cover?
Exam Format at a Glance
| Specification | Detail |
|---|---|
| Exam Code | 1Z0-149 |
| Exam Name | Oracle Database Program with PL/SQL |
| Duration | 90 minutes |
| Number of Questions | 65 |
| Passing Score | 66% |
| Exam Price | USD $245 |
| Format | Multiple Choice Questions (MCQ) |
Working through a realistic question set early tells you which objectives still feel shaky before you commit study hours. A focused bank of 1Z0-149 practice questions mirrors the phrasing and difficulty of the real test, so you learn to read Oracle’s answer choices as carefully as its documentation.
Syllabus Topics and Objectives
The blueprint groups objectives into the topic areas below. Every certified developer should be able to demonstrate each one hands-on, not just recognize it.
| Topic Area | Objectives |
|---|---|
| Declaring PL/SQL Variables | Recognize valid and invalid identifiers; list the uses of variables, declare and initialize variables, use bind variables; list and describe various data types using the %TYPE and %ROWTYPE attributes |
| Writing Executable Statements | Identify lexical units in a PL/SQL block; use built-in SQL functions in PL/SQL and sequences in PL/SQL expressions; describe when implicit conversions take place and when explicit conversions have to be dealt with; write nested blocks and qualify variables with labels; write readable code with appropriate indentation |
| Writing SQL in PL/SQL | Create PL/SQL executable blocks using DML and transaction control statements; make use of the INTO clause to hold the values returned by a SQL statement |
| Writing Control Structures | Identify the uses and types of control structures (IF, CASE statements and expressions); construct and identify loop statements; use EXIT and CONTINUE statements inside loops |
| Working with Composite Data Types | Create user-defined PL/SQL records; create a record with the %ROWTYPE attribute; create an INDEX BY table and INDEX BY table of records; describe the differences among records, collections, and collections of records; initialize collections and records |
| Using Explicit Cursors | Distinguish between implicit and explicit cursors and use SQL cursor attributes; declare and control explicit cursors, use simple loops and cursor FOR loops to fetch data; declare and use cursors with parameters; lock rows with the FOR UPDATE clause and reference the current row with the WHERE CURRENT OF clause |
| Handling Exceptions | Define PL/SQL exceptions; recognize unhandled exceptions; handle different types of exceptions (internally defined exceptions, predefined exceptions and user-defined exceptions); propagate exceptions |
| Using PL/SQL Subprograms | Differentiate between anonymous blocks and subprograms; create a simple procedure and invoke it from an anonymous block; identify benefits of subprograms |
| Creating Procedures and Using Parameters | Create a procedure with parameters; use named notation; work with procedures (create, invoke and remove procedures); handle exceptions in procedures and display a procedure’s information |
| Creating Functions | Differentiate between a procedure and a function; describe the uses of functions; work with functions (create, invoke and remove functions) |
| Creating Packages | Identify the benefits and the components of packages; work with packages (create package specification and body, invoke package subprograms, remove a package and display package information); overload package subprograms and use forward declarations |
| Working with Packages | Use package types and variables; use packaged constants and functions in SQL; use ACCESSIBLE BY to restrict access to package subprograms |
| Using Dynamic SQL | Describe the execution flow of SQL statements; use Native Dynamic SQL (NDS); bind PL/SQL types in SQL statements |
| Design Considerations for PL/SQL Code | Create standard constants and exceptions; write and call local subprograms; control the run-time privileges of a subprogram; perform autonomous transactions; use NOCOPY hint, PARALLEL ENABLE hint and DETERMINISTIC clause; use bulk binding and the RETURNING clause with DML |
| Creating Compound, DDL, and Event Database Triggers | Create triggers on DDL statements; create triggers on system events; describe different types of triggers and their uses |
| Using the PL/SQL Compiler | Describe the PL/SQL compiler and features; use the PL/SQL compiler initialization parameters; use the PL/SQL compile time warnings |
| Managing PL/SQL Code | Describe and use conditional compilation; code-based access control: granting roles to program units; whitelist code access with the ACCESSIBLE BY clause; mark code as deprecated |
| Managing Dependencies | Track and manage procedural dependencies |
How Do Anonymous Blocks and Stored Procedures Work?
Every PL/SQL unit follows the same shape: an optional declaration part, an executable part between BEGIN and END, and an optional exception section. Variables declared with %TYPE and %ROWTYPE inherit their datatype from a column or row, so your code stays correct even when the table definition changes. Solid command of SQL fundamentals underpins all of this, which is why many candidates first sit the Oracle Database SQL exam before moving into procedural code.
Procedures Versus Functions
The exam draws a firm line between the two subprogram types. A procedure performs an action and can return values through OUT parameters; a function computes and returns a single value and can be called inside a SQL statement. Oracle’s own procedures and functions reference details parameter modes, overloading, and invoker versus definer rights, all of which appear on the blueprint.
- Use IN, OUT, and IN OUT parameter modes deliberately, and prefer named notation for readability.
- Handle exceptions inside the subprogram so callers receive clean, predictable errors.
- Remove and recreate routines cleanly, and query the data dictionary to display their source and status.
Why Do Packages Matter for a PL/SQL Developer?
The specification declares what is available to callers; the body holds the logic. Because the body can change without recompiling everything that depends on the specification, packages reduce invalidation across a large codebase. Oracle’s PL/SQL packages guide covers instantiation, package state, and the SERIALLY_REUSABLE pragma that the exam references.
Package Skills the Exam Tests
- Create a specification and body, then invoke the packaged subprograms from SQL and PL/SQL.
- Overload subprograms so one name handles several parameter signatures, and use forward declarations for private routines.
- Expose package types, variables, and constants, and call packaged functions inside SQL statements.
- Restrict which units may call a subprogram with the ACCESSIBLE BY clause.
How Do Triggers and Dynamic SQL Extend the Database?
The blueprint focuses on compound, DDL, and event database triggers. DDL triggers fire on statements such as CREATE or ALTER, and system-event triggers respond to database events like logon or startup. Understanding when each type fires, and the difference between row-level and statement-level timing, is essential for the questions in this area.
Where Dynamic SQL Fits
Native Dynamic SQL, using EXECUTE IMMEDIATE, handles statements built as strings at run time and binds PL/SQL variables into them. It is the right tool when object names or whole clauses vary, but it demands care around SQL injection and correct bind-variable use. The exam pairs this topic with the broader execution flow of SQL statements so you understand what the database does before your code runs.
What Is the Best Way to Handle Cursors and Exceptions?
Explicit cursors give you full control: you declare them, open them, fetch rows in a loop, and close them, or you let a cursor FOR loop handle that lifecycle automatically. Cursors can take parameters, and the FOR UPDATE and WHERE CURRENT OF clauses let you lock and update the exact row you are reading. The exam expects fluency with SQL cursor attributes such as %FOUND, %NOTFOUND, and %ROWCOUNT.
Exception Types You Must Recognize
- Internally defined errors raised by the Oracle engine, which you can name with PRAGMA EXCEPTION_INIT.
- Predefined exceptions such as NO_DATA_FOUND and TOO_MANY_ROWS that already have names.
- User-defined exceptions you declare and raise for your own business rules.
Knowing how exceptions propagate outward through nested blocks, and how to trap or re-raise them, separates a passing answer from a near miss.
How Should You Prepare for the 1Z0-149 Exam?
A Practical Study Sequence
- Start with variables, executable statements, and control structures until basic blocks feel automatic.
- Move into composite types, explicit cursors, and exception handling, coding each objective by hand.
- Build procedures, functions, and packages, then refactor loose routines into well-structured packages.
- Finish with triggers, dynamic SQL, the PL/SQL compiler, conditional compilation, and dependency management.
- Cycle through timed question sets, reviewing every wrong answer against the official documentation.
Read the official language reference alongside your practice so terminology matches the exam wording. The PL/SQL language reference is the authoritative source for syntax, hints such as NOCOPY and DETERMINISTIC, and the compiler behavior the blueprint names. Treat weak areas as coding drills rather than reading assignments.
What Career Paths Follow the 1Z0-149?
Many professionals pair procedural development with broader database operations, and Oracle offers a clear next step: the MySQL database administrator path broadens your platform coverage while your PL/SQL depth keeps you valuable on Oracle-heavy teams.
Roles That Value This Credential
- PL/SQL Developer: builds and tunes stored logic for transactional and reporting systems.
- Database Application Developer: connects application layers to database packages and APIs.
- Data Engineer: uses procedural code and bulk operations for large-scale data movement.
- Database Administrator: combines development fluency with operational ownership of the database.
Frequently Asked Questions
What is the passing score for the 1Z0-149 exam?
You need 66% to pass the 1Z0-149 exam. With 65 multiple-choice questions in 90 minutes, that leaves roughly 83 seconds per question, so both accuracy and pace matter during preparation.
How many questions are on the Oracle 1Z0-149 exam?
The exam contains 65 multiple-choice questions. It costs USD $245 and runs for 90 minutes, testing everything from variable declaration to packages, triggers, dynamic SQL, and code management.
Is programming experience required to become an Oracle PL/SQL developer?
Prior SQL knowledge is strongly recommended before tackling PL/SQL. You do not need years of programming, but comfort with queries, joins, and DML makes the procedural concepts on the 1Z0-149 blueprint far easier to absorb.
What is the difference between a procedure and a function in PL/SQL?
A procedure performs an action and can return values through OUT parameters, while a function computes and returns a single value and can be used inside a SQL statement. The 1Z0-149 exam tests this distinction directly.
How long does it take to prepare for the 1Z0-149 exam?
Most candidates with basic SQL skills need four to eight weeks of steady, hands-on study. Because the exam is code-heavy, time spent writing and running PL/SQL is more valuable than time spent only reading.
Are packages heavily tested on the 1Z0-149 exam?
Yes. Two separate topic areas, Creating Packages and Working with Packages, cover specifications, bodies, overloading, forward declarations, packaged types, and the ACCESSIBLE BY clause, making packages one of the most important areas to master.
What tools should I install to practice PL/SQL?
A free Oracle Database edition paired with SQL Developer or SQLcl gives you everything needed to write, compile, and debug PL/SQL. Practicing on a real instance matches the hands-on nature of the 1Z0-149 objectives.
Does the 1Z0-149 exam expire?
Oracle certifications generally remain valid, though specific versions can be retired as the database evolves. Always confirm the current status of the 1Z0-149 exam on Oracle’s official certification catalog before you register.
Can the 1Z0-149 help with a database administrator career?
It can. Strong PL/SQL skills complement administration work, and many database administrators write procedural code daily. The credential is a solid foundation for developer and administrator tracks alike.
