OCA
- Introduction to PL/SQL
- PL/SQL Overview
- Benefits of PL/SQL Subprograms
- Overview of the Types of PL/SQL blocks
- Creating and Executing a Simple Anonymous Block Classes
- Generating Output from a PL/SQL Block
Declaring PL/SQL Identifiers
- Different Types of Identifiers in a PL/SQL subprogram
- Using the Declarative Section to Define Identifiers
- Storing Data in Variables
- Scalar Data Types
- %TYPE Attribute
- Bind Variables
- Using Sequences in PL/SQL Expressions
Writing Executable Statements
- Describing Basic PL/SQL Block Syntax Guidelines
- Commenting Code
- SQL Functions in PL/SQL
- Object Serialization
- Data Type Conversion
- Nested Blocks
- Operators in PL/SQL
Interacting with the Oracle Server
- Including SELECT Statements in PL/SQL to Retrieve data
- Manipulating Data in the Server Using PL/SQL
- The SQL Cursor concept
- Using SQL Cursor Attributes to Obtain Feedback on DML
- Saving and Discarding Transactions
Writing Control Structures
- Conditional processing Using IF Statements
- Conditional processing Using CASE Statements
- Simple Loop Statement
- While Loop Statement
- For Loop Statement
- The Continue Statement
Working with Composite Data Types
- Using PL/SQL Records
- Using the %ROWTYPE Attribute
- Inserting and Updating with PL/SQL Records
- INDEX BY Tables
- INDEX BY Table Methods
- INDEX BY Table of Records
Using Explicit Cursors
- Understanding Explicit Cursors
- Declaring the Cursor
- Opening the Cursor
- Fetching data from the Cursor
- Closing the Cursor
- Cursor FOR loop
- Explicit Cursor Attributes
- FOR UPDATE Clause and WHERE CURRENT Clause
Handling Exceptions
- Understanding Exceptions
- Handling Exceptions with PL/SQL
- Trapping Predefined Oracle Server Errors
- Trapping Non-Predefined Oracle Server Errors
- Trapping User-Defined Exceptions
- Propagate Exceptions
- RAISE_APPLICATION_ERROR Procedure
Creating Stored Procedures
- Creating a Modularize and Layered Subprogram Design
- Modularizing Development With PL/SQL Blocks
- Understanding the PL/SQL Execution Environment
- The Benefits of Using PL/SQL Subprograms
- The Differences Between Anonymous Blocks and Subprograms
- Creating,Calling,and Removing Stored Procedures
- Using Procedures Parameters and Parameters Modes
Creating Stored Functions
- Creating, Calling, and Removing a Stored Function
- Identifying the Advantages of Using Stored Functions
- Using User-Defined Functions in SQL Statements
- Restrictions When Calling Functions from SQL statements
- Viewing Functions Information
Creating Packages
- Describing Packages
- Developing a Package
- The Visibility of a Package’s Components
- Invoking the Package Constructs
- Viewing the PL/SQL Source Code Using the Data Dictionary
Working With Packages
- Overloading Subprograms in PL/SQL
- Using the STANDARD Package
- Using Package Functions in SQL and Restrictions
- Persistent State of Packages
- Controlling Side Effects of PL/SQL Subprograms
- Persistent State of a Package Cursor
Using Oracle-Supplied Packages in Application Development
- Using Oracle-Supplied Packages
- Examples of Some of the Oracle-Supplied Packages
- How Does the DBMS_OUTPUT Package Work?
- Using the UTL_MAIL Package
- Using the UTL_MAIL Subprograms
- Persistent State of a Package Cursor
Using Dynamic SQL
- The Execution Flow of SQL
- What is Dynamic SQL
- Declaring Cursor Variables
- Dynamically Executing a PL/SQL Block
- Using DBMS_SQL Package
- Dynamic SQL Functional Completeness
Design Considerations for PL/SQL Code
- Standardizing Constants and Exceptions
- Using Local Subprograms
- Using the NOCOPY Compiler
- Using the PARALLEL_ENABLE
- Using the DETERMINISTIC Clause with Functions
- Using Bulk Binding to Improve Performance
Creating Triggers
- Working With Triggers
- Identifying the Trigger Event Types and Body
- Business Application Scenarios for Implementing Triggers
- Creating Instead of and Disabled Triggers
- Managing, Testing, and Removing Triggers
Creating Compound, DDL, and Event Database Triggers
- Working With Triggers
- Identifying Timing-Point Sections of a Table Trigger
- Creating Triggers on DDL Statements
- Creating Database-Event and System-Events Triggers
- System Privileges Required to Manage Triggers
Using the PL/SQL Compiler
- Using the Initialization Parameters for PL/SQL Compilation
- Using the New PL/SQL Compile Time Warnings
- The Benefits of Compiler Warnings
- Setting the Warning Messages Levels
- Viewing the Compiler Warnings
Managing PL/SQL Code
- Using Selection Directives
- Using Predefined and User-Defined Inquiry Directives
- Using the DBMS_DB_VERSION Package
- Obfuscating and Wrapping PL/SQL Code
Managing Dependencies
- Overview of Schema Object Dependencies
- Querying an Object’s Status
- Invalidation of Dependent Objects
- Displaying Direct and Indirect Dependencies
- Understanding Remote Dependencies
- Recompiling a PL/SQL Program Unit