You can use this Oracle 1Z0-149 exam study guide to collect all the information about the Oracle Database Program with PL/SQL exam. The Oracle 1Z0-149 certification exam is mainly targeted to those candidates who have some experience or exposure to Oracle Database technology and want to flourish their career with Oracle Database PL/SQL Developer Certified Professional (OCP) credential.
The Oracle 1Z0-149 certification exam validates your understanding of the Oracle Database technology and sets the stage for your future progression. Your preparation plan for Oracle Database Program with PL/SQL Certification exam should include hands-on practice or on-the-job experience performing the tasks described in following certification exam syllabus topics.
Oracle 1Z0-149 Exam Details:
| Exam Name | Oracle Database Program with PL/SQL |
| Exam Code | 1Z0-149 |
| Exam Price | USD $245 (Pricing may vary by country or by localized currency) |
| Duration | 90 minutes |
| Number of Questions | 65 |
| Passing Score | 66% |
| Format | Multiple Choice Questions (MCQ) |
| Recommended Training | Earn the Oracle Database PL/SQL Developer Certified Professional Credential |
| Schedule Exam | Buy Oracle Training and Certification |
| Sample Questions | Oracle Database PL/SQL Developer Certified Professional (OCP) |
| Recommended Practice | 1Z0-149 Online Practice Exam |
Oracle 1Z0-149 Syllabus Topics:
| 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 parameterrs - 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 |
The Oracle Database Program with PL/SQL Certification Program certifies candidates on skills and knowledge related to Oracle Database products and technologies. The Oracle 1Z0-149 is granted based on a combination of passing exams, training, and performance-based assignments, depending on the level of certification. Oracle Database Program with PL/SQL certification is a real benchmark of experience and expertise that helps you stand out in a crowd among employers. To ensure success, Oracle recommends combining education courses, practice exams, and hands-on experience to prepare for your Oracle Database PL/SQL Developer Certified Professional (OCP) certification exam as questions will test your ability to apply the knowledge you have gained in hands-on practice or professional experience.