Intro to Oracle 11g PL/SQL Programming – ORA503
This Oracle PL/SQL training class is designed to give students a hands-on Oracle PL/SQL learning experience. This course is designed with 40% lecture and demos and 55% hand on labs and Q&A. Dozens on labs and examples are delivered and student for from easy SQL to more complex PL/SQL coding.
Oracle 11g PL/SQL Training – ORA503 – 3 Days Request a Class Date
In this three day live instructor-led Oracle PL/SQL training course students learn the fundamentals of PL/SQL which is Oracle’s Procedural Language for SQL. PL/SQL is Oracle’s database programming language for creating stored procedures, functions, database triggers, and object methods.
Multi-Enrollment Discounts Available at Check-Out
PO BOX 334
Golden CO 80402
303-416-3056
Course ID: ORA503 Duration: 3 days
Audience: Application developers and database administrators
Prerequisites:A good working knowledge of Oracle 11g SQL features is required. Programming experience in a high-level language, such as COBOL™, Java™, or Perl is also required. You will want to have mastery of the topics covered in the Introduction to Oracle 11g SQL Programming training course.
In this live, instructor-led Oracle PL/SQL training course students learn the fundamentals of PL/SQL which is Oracle’s Procedural Language for SQL. PL/SQL is Oracle’s database programming language for creating stored procedures, functions, database triggers, and object methods. PL/SQL can be used for implementing business rules, computing algorithms, manipulating data, and for stand-alone programs. In the Oracle 11g version release has greatly enhanced the features and functionality of PL/SQL.
Students learn how to write stored procedures, functions, packages, and triggers, and implement complex business rules in Oracle. Students will learn programming, management, and security issues of working with PL/SQL program units. Programming topics will include the built-in packages that come with Oracle, the creation of triggers, and stored procedure features.
Introduction to Oracle 11g PL/SQL Programming Training Course Topics:
- Beyond Declarative Integrity
- Triggers
- Types of Triggers
- Trigger Sequencing
- Row-Level Triggers
- Trigger Predicates
- Trigger Conditions
- Using Sequences
- Cascading Triggers and Mutating Tables
- Generating an Error
- Maintaining Triggers
PL/SQL Variables and Datatypes
- Anonymous Blocks
- Declaring Variables
- Datatypes
- Subtypes
- Character Data
- Dates and Timestamps
- Date Intervals
- Anchored Types
- Assignment and Conversions
- Selecting into a Variable
- Returning into a Variable
PL/SQL Syntax and Logic
- Conditional Statements – IF/THEN
- Conditional Statements – CASE
- Comments and Labels
- Loops
- WHILE and FOR Loops
- SQL in PL/SQL
- Local Procedures and Functions
Stored Procedures and Functions
- Stored Subprograms
- Creating a Stored Procedure
- Procedure Calls and Parameters
- Parameter Modes
- Named Parameter Notation
- Default Arguments
- Creating a Stored Function
- Stored Functions and SQL
- Invoker’s Rights
Exception Handling
- SQLCODE and SQLERRM
- Exception Handlers
- Nesting Blocks
- Scope and Name Resolution
- Declaring and Raising Named Exceptions
- User-Defined Exceptions
Records, Collections, and User-Defined Types
- Record Variables
- Using the %ROWTYPE Attribute
- User-Defined Object Types
- VARRAY and Nested TABLE Collections
- Using Nested TABLEs
- Using VARRAYs
- Collections in Database Tables
- Associative Array Collections
- Collection Methods
- Iterating Through Collections
- Multi-Row Queries
- Declaring and Opening Cursors
- Fetching Rows
- Closing Cursors
- The Cursor FOR Loop
- FOR UPDATE Cursors
- Cursor Parameters
- The Implicit (SQL) Cursor
- Bulk Operations
- Bulk Binding
- BULK COLLECT Clause
- FORALL Statement
- FORALL Variations
- Bulk Returns
- Bulk Fetching with Cursors
Using Packages
- Packages
- Oracle-Supplied Packages
- The DBMS_OUTPUT Package
- The DBMS_UTILITY Package
- The UTL_FILE Package
- Creating Pipes with DBMS_PIPE
- Writing to and Reading from a Pipe
- The DBMS_METADATA Package
- XML Packages
- Networking Packages
- Other Supplied Packages
Creating Packages
- Structure of a Package
- The Package Interface and Implementation
- Package Variables and Package State
- Overloading Package Functions and Procedures
- Forward Declarations
- Strong REF CURSOR Variables
- Weak REF CURSOR Variables
Working with LOBs
- Large Object Types
- Oracle Directories
- LOB Locators
- Internal LOBs
- LOB Storage and SECUREFILEs
- External LOBs
- Temporary LOBs
- The DBMS_LOB Package
Maintaining PL/SQL Code
- Privileges for Stored Programs
- Data Dictionary
- PL/SQL Stored Program Compilation
- Conditional Compilation
- Compile-Time Warnings
- The PL/SQL Execution Environment
- Dependencies and Validation
- Maintaining Stored Programs
Appendix A: Dynamic SQL
- Generating SQL at Runtime
- Native Dynamic SQL vs. DBMS_SQL Package
- The EXECUTE IMMEDIATE Statement
- Using Bind Variables
- Multi-row Dynamic Queries
- Bulk Operations with Dynamic SQL
- Using DBMS_SQL
- DBMS_SQL Subprograms
Appendix B: PL/SQL Versions, Datatypes, and Language Limits
Appendix C: Oracle 11g Supplied Packages