O R A C L E C E R T I F I C A T I O N
1Z0-149 Oracle Database Program with PL/SQL Practice Exam
Exam Number: 4830 | Last updated April 19, 2026 | 700+ questions across 4 vendor-aligned objectives
The 1Z0-149 Oracle Database Program with PL/SQL exam targets database developers, application engineers, and DBAs who write and maintain server-side code in Oracle Database. Candidates validate command of PL/SQL block structure, stored procedures and functions, packages, triggers, dynamic SQL, bulk processing, and the tuning and security patterns that keep PL/SQL units fast and safe in production.
The heaviest content is Subprograms, Packages, and Triggers (roughly 35%), covering procedures, functions, package specs and bodies, overloading, forward declarations, and DML, DDL, and system triggers. Cursors, Collections, and Bulk Operations contributes another 25% with explicit and implicit cursors, cursor variables, records, associative arrays, and BULK COLLECT with FORALL.
Dynamic SQL and Error Handling sits near 20% and drills into EXECUTE IMMEDIATE, DBMS_SQL, exception propagation, and the SAVE EXCEPTIONS pattern. Performance, Security, and Tooling rounds out the remaining weight with result cache, compiler warnings, PL/SQL obfuscation, Invoker’s versus Definer’s Rights, and PL/Scope utilities.
Every answer links to the source. Each explanation below includes a hyperlink to the exact Oracle documentation page the question was derived from. PowerKram is the only practice platform with source-verified explanations. Learn about our methodology →
441
practice exam users
90.9%
satisfied users
88.2%
passed the exam
4.5/5
quality rating
Test your 1Z0 149 PL/SQL Programming knowledge
10 of 700+ questions
Question #1 - Subprograms, Packages, and Triggers
A PL/SQL developer at an e-commerce company is building a package with related procedures for order processing: PLACE_ORDER, CANCEL_ORDER, and RESCHEDULE_ORDER. She wants these grouped, with shared state (like an order counter) visible across calls from the same session.
Which PL/SQL construct fits this pattern?
A) A PL/SQL package with a spec exposing the procedures and a body holding session-level state in a package variable.
B) Three unrelated standalone procedures.
C) A single procedure with 1,000 parameters.
D) A trigger on a table with all logic inside.
Show solution
Correct answers: A – Explanation:
Packages group related subprograms and support session-level state through package-level variables — the designed construct. Option B loses cohesion. Option C is unwieldy. Option D mixes event-driven and procedural concerns. Source: Check Source
Question #2 - Subprograms, Packages, and Triggers
A developer wants two versions of a GET_CUSTOMER procedure: one accepting the customer ID as NUMBER and another accepting the email as VARCHAR2. Callers should invoke GET_CUSTOMER and PL/SQL should pick the matching version.
Which PL/SQL feature supports this pattern?
A) A trigger that dispatches on type.
B) Overloading in a package spec with two signatures.
C) Two unrelated packages.
D) A single procedure accepting ANYDATA and casting internally.
Show solution
Correct answers: B – Explanation:
Package overloading allows same-name procedures with different signatures; PL/SQL resolves the call at compile time by parameter types. Option D is cumbersome and slower. Option C loses the shared name. Option A is the wrong construct. Source: Check Source
Question #3 - Cursors, Collections, and Bulk Operations
A developer processes 2 million rows, reading a row, transforming it, and inserting into another table. Row-by-row processing takes 45 minutes. She wants to batch the operation for speed.
Which PL/SQL pattern dramatically improves throughput?
A) A single INSERT of all 2 million rows in one call without bulk binding.
B) A cursor FOR loop that processes one row at a time.
C) BULK COLLECT with a LIMIT clause combined with FORALL for bulk DML.
D) DBMS_OUTPUT to print each row as it processes.
Show solution
Correct answers: C – Explanation:
BULK COLLECT with LIMIT controls memory and FORALL performs bulk DML, massively reducing context switches between PL/SQL and SQL engines. Option B is the slow baseline. Option A may exceed memory and not use bulk binding efficiently. Option D is output, not DML. Source: Check Source
Question #4 - Cursors, Collections, and Bulk Operations
A developer uses FORALL with SAVE EXCEPTIONS to bulk-insert 500 rows. Three rows violate a constraint. She wants to capture which rows failed and why while letting the other 497 inserts succeed.
Which mechanism captures the per-row exceptions?
A) Rolling back the entire batch immediately.
B) Ignoring errors and continuing silently.
C) EXCEPTION WHEN OTHERS that halts processing on the first error.
D) SQL%BULK_EXCEPTIONS to iterate the failed row indexes and error codes after the FORALL.
Show solution
Correct answers: D – Explanation:
SAVE EXCEPTIONS collects row-level failures, which you then iterate via SQL%BULK_EXCEPTIONS to log index and error code — the designed pattern. Option C halts. Option A discards the 497 successes. Option B loses audit trail. Source: Check Source
Question #5 - Dynamic SQL and Error Handling
A developer receives a table name at runtime and needs to SELECT COUNT(*) from that table. The table is unknown at compile time.
Which construct safely runs this dynamic query?
A) EXECUTE IMMEDIATE with a bind variable substituted into the dynamic SQL string and input validation.
B) Concatenating the table name into the SQL without validation and running via EXECUTE IMMEDIATE.
C) A compile-time SQL statement only.
D) Hard-coding every possible table name in a giant CASE.
Show solution
Correct answers: A – Explanation:
Dynamic SQL with EXECUTE IMMEDIATE is the intended approach, with whitelisting/validating the table name to prevent SQL injection via identifiers. Option B is a SQL-injection pattern. Option D does not scale. Option C cannot handle runtime identifiers. Source: Check Source
Question #6 - Dynamic SQL and Error Handling
A procedure calls a lower-level procedure that raises NO_DATA_FOUND. The business wants the outer procedure to log the exception with the line number and procedure name where it originated, then re-raise.
Which PL/SQL facility captures the call stack for logging?
A) Swallowing the exception silently.
B) DBMS_UTILITY.FORMAT_ERROR_BACKTRACE (and/or UTL_CALL_STACK) to log the source location, then RAISE to re-raise.
C) SQLERRM only.
D) A print statement in the outer procedure only.
Show solution
Correct answers: B – Explanation:
FORMAT_ERROR_BACKTRACE (or UTL_CALL_STACK) returns the PL/SQL call stack with line numbers — needed for precise diagnostics — after which RAISE re-propagates the exception. Option C gives the message but not the stack. Option A loses information. Option D is not a stack. Source: Check Source
Question #7 - Subprograms, Packages, and Triggers
A developer wants an audit trigger that fires whenever a user issues CREATE TABLE at the schema level, capturing the schema, object name, and timestamp.
Which trigger type fits?
A) A DML trigger on a user table.
B) A BEFORE SERVERERROR trigger.
C) A schema-level DDL trigger on CREATE using ora_dict_obj_name and ora_dict_obj_owner attributes.
D) A statement-level logon trigger.
Show solution
Correct answers: C – Explanation:
DDL triggers fire on schema or database-level DDL events (CREATE/ALTER/DROP), and event attributes expose object metadata — the designed mechanism. Option A fires on DML. Option B fires on errors. Option D fires at login. Source: Check Source
Question #8 - Performance, Security, and Tooling
A package executes a read-only, expensive computation invoked thousands of times per second with the same inputs and outputs.
Which PL/SQL feature caches the results across sessions?
A) A PL/SQL package variable (session-only).
B) DBMS_OUTPUT.PUT_LINE for each call.
C) A random generator returning similar values.
D) PL/SQL Function Result Cache with appropriate RELIES_ON clauses.
Show solution
Correct answers: D – Explanation:
The PL/SQL function result cache stores function results in the SGA, shared across sessions; RELIES_ON ensures invalidation on data changes. Option A is per-session only. Option B is output, not caching. Option C loses correctness. Source: Check Source
Question #9 - Performance, Security, and Tooling
A package contains sensitive business logic. The developer wants to grant EXECUTE on the package only to a specific application schema, and additionally restrict call-site visibility so only a specific other package can compile against it.
Which PL/SQL 12c feature restricts call-site visibility?
A) The ACCESSIBLE BY clause on the package spec.
B) Wrapping the package source only.
C) Granting EXECUTE to PUBLIC.
D) A role granted to all schemas.
Show solution
Correct answers: A – Explanation:
The ACCESSIBLE BY clause lets a package, procedure, or function declare an explicit white list of callers — a compile-time gate beyond EXECUTE privileges. Option C over-grants. Option B only obfuscates source. Option D is broad. Source: Check Source
Question #10 - Subprograms, Packages, and Triggers
A developer writing a utility package wants its procedures to run with the privileges of the caller (the invoker), not of the package owner. This is to support multi-schema deployments where the same package operates on each caller’s objects.
Which PL/SQL option supports invoker’s rights?
A) Using a trigger for all calls.
B) Declaring with AUTHID DEFINER (the default).
C) Declaring the package with AUTHID CURRENT_USER.
D) Granting DBA to the package.
Show solution
Correct answers: C – Explanation:
AUTHID CURRENT_USER makes calls run with the invoker’s privileges and resolve schema references against the caller’s schema — the designed pattern. Option B is the default (definer’s rights). Option D is a security violation. Option A is not the correct construct. Source: Check Source
Get 700+ more questions with source-linked explanations
Every answer traces to the exact Oracle documentation page — so you learn from the source, not just memorize answers.
Exam mode & learn mode · Score by objective · Updated April 19, 2026
Learn more...
What the 1Z0 149 PL/SQL Programming exam measures
- Subprograms, packages, and triggers (35%) — design procedures, functions, packages with specs and bodies, overloaded subprograms, forward declarations, and DML, DDL, and system triggers.
- Cursors, collections, and bulk operations (25%) — use explicit and implicit cursors, cursor variables, records, associative arrays, nested tables, and BULK COLLECT with FORALL.
- Dynamic SQL and error handling (20%) — execute dynamic SQL with EXECUTE IMMEDIATE and DBMS_SQL, propagate exceptions, and batch DML with SAVE EXCEPTIONS.
- Performance, security, and tooling (20%) — apply result cache, compiler warnings, obfuscation, Invoker’s and Definer’s Rights, accessible_by, and PL/Scope.
How to prepare for this exam
- Review the official 1Z0-149 exam page to confirm the objectives.
- Complete the Oracle University Oracle Database PL/SQL Fundamentals and Developer learning path on MyLearn.
- In an OCI always-free Autonomous Database, rebuild a sample application as a packaged PL/SQL API — use BULK COLLECT, handle exceptions with SAVE EXCEPTIONS, and set Invoker’s Rights on the package.
- Apply the skills at work: replace a slow loop with a FORALL bulk insert, refactor a cursor for cursor, or add PL/Scope diagnostics to a package that needs tuning.
- Master one objective at a time, starting with subprograms, packages, and triggers since it carries the most weight.
- Run PowerKram learn mode to see feedback after every question with sourced links back to Oracle documentation.
- Finish with PowerKram exam mode across all objectives until you pass three back-to-back full-length attempts.
Career paths and salary outlook
PL/SQL skills remain core to Oracle shops and enterprise-data teams:
- PL/SQL Developer — $90,000–$130,000 (Glassdoor).
- Senior Oracle Database Developer — $110,000–$150,000 (PayScale).
- Database Software Engineer — $115,000–$160,000 (U.S. Bureau of Labor Statistics).
Official resources
Follow the Oracle PL/SQL Developer Learning Path on Oracle MyLearn. Reinforce with the Oracle Database PL/SQL Language Reference and the PL/SQL Packages and Types Reference.