Oracle 1Z0-149 Practice Exam Questions & Answers
6 Free Questions
· Last reviewed: October 10, 2026
· Prepared & Reviewed by the ValidExamDumps Editorial Team
Exam Facts
Oracle 1Z0-149 Exam Details
Key details for this exam, checked against the published exam outline
65
Practice Questions (Our Bank)
90 minutes
Exam Duration
- Exam Code
- 1Z0-149
- Full Name
- Oracle Database 19c: Program with PL/SQL
- Issuing Body
- Oracle
- Question Format (Our Bank)
- Multiple Choice
Practice Questions
Free 1Z0-149 Practice Questions
Each question shows the correct answer and an explanation of why it is right
VA
ValidExamDumps Editorial Team
Every question and its answer is checked by our 1Z0-149 exam
preparation team, who also write the explanation shown with each one.
How we research and review these pages
Which three are true about the procedure overloading feature? (Choose three.)
Correct Answer:
B, E, F
Explanation
System events are database-level occurrences that can trigger DDL triggers. SHUTDOWN fires when the database shuts down, SERVERERROR fires when a server error occurs, and STARTUP fires when the database starts up. These are the only three system events supported for trigger creation in Oracle Database. Events like LOGON and LOGOFF are user events, not system events.
Which two are true about INDEX-BY tables? (Choose two.)
Correct Answer:
A, C
Explanation
When you use OR REPLACE to create or modify a named subprogram like a function, you can change its definition without dropping and recreating it. This preserves any object privileges that users or roles had been granted on the function. If you dropped and recreated the function instead, those privileges would be lost and would need to be re-granted. OR REPLACE only works if you are not changing the parameters or return type significantly.
Which block of code displays the error message ''Incorrect price value''?
A.
B.
C.
D.
Correct Answer:
B
Explanation
IN parameters act as read-only constants within the subprogram and cannot be modified. IN OUT parameters allow the subprogram to receive an initial value from the caller and return a modified value back. When you use OUT parameters, the actual parameter you pass must be a variable, not a literal or expression, because the subprogram needs somewhere to write the return value. These parameter modes control data flow direction and mutability between calling code and subprograms.
SERVEROUTPUT is enabled.
Which is the correct method to use a PACKAGED CONSTANT in SELECT statements?
A.
B.
C.
D.
Correct Answer:
D
Explanation
The OR REPLACE clause allows you to redefine a function without dropping and recreating it, which simplifies maintenance. More importantly, when you replace a function using OR REPLACE, users who previously had execute privileges retain those privileges automatically. Without OR REPLACE, you would need to drop the function and recreate it, which would require granting privileges again to all users.
Which three are true about functions and procedures? (Choose three.)
Correct Answer:
B, E, F
Explanation
DDL triggers can fire when a table is truncated since TRUNCATE is a DDL statement. They can fire either before or after the DDL statement executes depending on how you define them. However, DDL triggers fire when any user executes a DDL statement, not just the owner. The owner restriction applies to other trigger types or different contexts. DDL triggers monitor DDL activity at the schema or database level regardless of who performs the action.
Which two are true about Conditional Compilation in PL/SQL using $IF, $ELSE, $END, and $ERROR? (Choose two.)
Correct Answer:
B, E
Explanation
PL/SQL subprograms are cached in the shared SQL area by default and shared among all users, which reduces memory consumption across the system. Reusing parsed PL/SQL code from this shared area eliminates the need to parse the same code again, reducing parsing overhead and improving performance. PL/SQL procedures can also invoke external code written in other programming languages like Java or C through external procedures. These capabilities make subprograms valuable for code reuse and performance optimization.
Full Access
Get the complete 1Z0-149 question set
- 65 questions covering all exam domains
- Correct answers with explanations, like the free questions above
- PDF and online practice test
- 90 days of free updates
Domain 1: Declaring PL/SQL Variables
Recognize valid and invalid identifiers. List the uses of variables, declare and initialize variables, and use bind variables. Apply the %TYPE and %ROWTYPE attributes to reference existing data types.
Domain 2: Writing Executable Statements
Identify lexical units in a PL/SQL block and use built-in SQL functions and sequences in expressions. Write nested blocks, qualify variables with labels, and apply proper indentation for code readability.
Domain 3: Writing SQL in PL/SQL
Create PL/SQL executable blocks using DML and transaction control statements. Use the INTO clause to hold values returned by SQL statements.
Domain 4: Writing Control Structures
Identify the uses and types of control structures including IF and CASE statements and expressions. Construct loop statements and use EXIT and CONTINUE statements inside loops.
Domain 5: Working with Composite Data Types
Create user-defined PL/SQL records and records with the %ROWTYPE attribute. Create INDEX BY tables and INDEX BY tables of records. Initialize collections and records.
Sample question from this domain above:
Q4
Domain 6: 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 cursors with parameters and use the FOR UPDATE clause.
Domain 7: Handling Exceptions
Define PL/SQL exceptions and recognize unhandled exceptions. Handle internally defined, predefined, and user-defined exceptions. Propagate exceptions through the call stack.
Domain 8: Using PL/SQL Subprograms
Differentiate between anonymous blocks and subprograms. Create and invoke simple procedures from anonymous blocks. Identify benefits of subprograms in code organization and reusability.
Sample question from this domain above:
Q6
Domain 9: Creating Procedures and Using Parameters
Create procedures with parameters and use named notation. Work with procedures through creation, invocation, and removal. Handle exceptions in procedures and display procedure information.
Domain 10: Creating Functions
Differentiate between procedures and functions. Describe the uses of functions in applications. Work with functions through creation, invocation, and removal operations.
Sample question from this domain above:
Q2
Domain 11: Creating Packages
Identify the benefits and components of packages. Work with packages through specification and body creation, subprogram invocation, and package removal. Overload package subprograms and use forward declarations.
Domain 12: Working with Packages
Use package types and variables within programs. Use packaged constants and functions in SQL statements. Use the ACCESSIBLE BY clause to restrict access to package subprograms.
Domain 13: Using Dynamic SQL
Describe the execution flow of SQL statements. Use Native Dynamic SQL to build and execute statements at run time. Bind PL/SQL types in dynamically constructed SQL statements.
Domain 14: Design Considerations for PL/SQL Code
Create standard constants and exceptions. Write and call local subprograms. Use the NOCOPY hint and DETERMINISTIC clause. Perform autonomous transactions and use bulk binding with the RETURNING clause.
Domain 15: Creating Compound, DDL, and Event Database Triggers
Create triggers on DDL statements and system events. Describe different types of triggers and their uses in enforcing business rules and maintaining data integrity.
Sample questions from this domain above:
Q1Q5
Domain 16: Using the PL/SQL Compiler
Describe the PL/SQL compiler and its features. Use the PL/SQL compiler initialization parameters. Enable and use PL/SQL compile time warnings to catch potential issues.
Domain 17: Managing PL/SQL Code
Describe and use conditional compilation to write version-flexible code. Use the ACCESSIBLE BY clause to whitelist code access. Mark code as deprecated to guide maintenance.
Sample question from this domain above:
Q3
Domain 18: Managing Dependencies
Track and manage procedural dependencies to understand relationships between database objects. Handle recompilation when dependent objects change.
FAQ
1Z0-149 Exam FAQ
Common questions about the exam itself
What background do I need before attempting the 1Z0-149 exam?
You should have hands-on experience writing PL/SQL code and working with Oracle Database 19c. Most candidates have completed database programming courses and have practical experience building stored procedures, functions, and packages in a production or development environment.
How difficult is the 1Z0-149 exam compared to other Oracle certifications?
The 1Z0-149 is considered moderately challenging because it tests both theoretical knowledge and the ability to write working PL/SQL code. Success requires not just understanding concepts but also recognizing correct syntax, troubleshooting logic errors, and applying design principles in code scenarios.
Which topics on 1Z0-149 do candidates struggle with most?
Candidates typically find cursors, exception handling, and dynamic SQL most challenging. These areas require understanding complex control flow and the interaction between PL/SQL and SQL, so practice with real code examples and hands-on labs in these topics pays off.
How long should I plan to study for the 1Z0-149 exam?
Most candidates prepare for three to five weeks, assuming they already have basic PL/SQL knowledge. If you are new to PL/SQL, plan for six to eight weeks of study time combined with hands-on practice writing code against an Oracle Database 19c instance.
What will I face on exam day for 1Z0-149?
You will have 90 minutes to answer 65 multiple-choice and multiple-select questions in a proctored testing environment, either online or at a test centre. The questions test your ability to recognize correct PL/SQL syntax, understand program flow, and identify errors in code blocks.
Can I retake the 1Z0-149 exam if I don't pass on my first attempt?
Yes, you can retake the exam. Oracle allows retakes under standard testing policies, though you must wait a certain period between attempts and pay the exam fee for each sitting. Check with the exam provider for current retake policies.
How long does the Oracle PL/SQL Developer Certified Professional credential stay valid?
Oracle does not list an expiration date for this certification, which means it typically does not expire. However, you may choose to upgrade to newer database versions as Oracle releases them by passing updated exams.
What job role does the 1Z0-149 certification qualify me for?
This certification qualifies you for roles such as PL/SQL Developer, Database Developer, or Database Analyst. Employers use it to verify your ability to design, develop, and maintain PL/SQL applications on Oracle Database 19c systems.
How does 1Z0-149 relate to other Oracle Database certifications?
The 1Z0-149 is part of the Oracle PL/SQL Developer path. It follows foundation exams like 1Z0-071 (SQL) and can lead to advanced certifications. Many professionals complete it alongside the 1Z0-144 exam to demonstrate comprehensive PL/SQL expertise.
Can I use exam vouchers or bundles to take the 1Z0-149?
Oracle often offers exam vouchers and certification bundles that include multiple exams. Check the Oracle Store or authorized resellers for current bundled options that may include the 1Z0-149 at a reduced cost.
