Oracle 10g PL/SQL Programming Course – ORA509
Oracle 10g PL/SQL Programming Course – ORA509 Request a Class Date
PL/SQL is the Oracle embedded procedural database language, allowing developers and administrators to write scripts, stored procedures, functions, packages, and database triggers which run in the optimized environment of the Oracle Database server. The Oracle10g release has greatly enhanced the features and functionality of PL/SQL.
This advanced course helps PL/SQL programmers take advantage of language features, advanced techniques, and packages and facilities provided by Oracle to develop and tune efficient and effective PL/SQL subprograms. Students will learn the fundamentals of the PL/SQL programming language. Students will write stored procedures, functions, packages, and triggers, and implement complex business rules in Oracle.
PL/SQL is the Oracle embedded procedural database language, allowing developers and administrators to write scripts, stored procedures, functions, packages, and database triggers which run in the optimized environment of the Oracle Database server. The Oracle10g release has greatly enhanced the features and functionality of PL/SQL.
This advanced course helps PL/SQL programmers take advantage of language features, advanced techniques, and packages and facilities provided by Oracle to develop and tune efficient and effective PL/SQL subprograms. Students will learn the fundamentals of the PL/SQL programming language. Students will 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.
Course ID: ORA509
Duration: 5 days
Audience: Application developers and database administrators
Topics You Will Learn Taking Oracle 10g PL/SQL Programming:
- Beyond Declarative Integrity
- Triggers
- Types of Triggers
- Row-Level Triggers
- Cascading Triggers and Mutating Tables
- Generating an Error
- Triggers on Views
- Schema and System Triggers
- Maintaining Triggers
PL/SQL Variables and Datatypes
- Declaring Variables
- Datatypes
- Subtypes
- Character Data
- Dates and Timestamps
- Date Intervals
- Anchored Types
- Assignment and Conversions
- Selecting into a Variable
PL/SQL Syntax and Logic
- PL/SQL Blocks and Programs
- Conditional Statements – IF/THEN
- Conditional Statements – CASE
- Comments and Labels
- Loops
- WHILE and FOR Loops
Stored Procedures and Functions
- Stored Subprograms
- Procedures and Functions
- Creating a Stored Procedure
- Calling a Stored Procedure
- Passing Parameters and Default Arguments
- Parameter Modes
- Creating a Stored Function
- Calling a Stored Function
- Stored Functions and SQL
- Local Procedures and Functions
Exception Handling
- SQLCODE and SQLERRM
- Exception Handlers
- Nesting Blocks
- Scope and Name Resolution
- User-Defined Exceptions
- Compile-Time Warnings
Records and Collections
- Record Variables
- Using the %ROWTYPE Attribute
- VARRAY and Nested TABLE Collections
- Using Nested TABLEs
- Using VARRAYs
- Associative Array Collections
- Collection Methods
- Iterating Through Collections
- FORALL Statement
- BULK COLLECT Clause
Cursors
- Multi-Row Queries
- Declaring and Opening Cursors
- Fetching Rows
- Closing Cursors
- The Cursor FOR Loop
- FOR UPDATE Cursors
- Cursor Parameters
- The Implicit (SQL) Cursor
Using Packages
- Packages
- Oracle-Supplied Packages
- The DBMS_OUTPUT Package
- The DBMS_UTILITY Package
- The UTL_FILE Package
- The DBMS_METADATA Package
- XML Packages
- Networking Packages
- Other Supplied Packages
Creating Packages
- Structure of a Package
- The Package Interface and Implementation
- Package Variables
- Overloading Package Functions and Procedures
- Named Parameter Notation
- REF CURSOR Variables
Working with LOBs
- Large Object Types
- Oracle Directories
- LOB Locators
- Internal LOBs
- External LOBs
- Temporary LOBs
- The DBMS_LOB Package
Maintaining PL/SQL Code
- Privileges for Stored Programs
- PL/SQL Stored Program Compilation
- The PL/SQL Execution Environment
- Dependencies and Validation
- Maintaining Stored Programs
- The Server Process
- PL/SQL Execution
- The PL/SQL Compiler
- Compiler Optimization
- SQL – Parse
- SQL – Execute and Fetch
- Server Memory
- Latches
- Locks
Advanced Cursors
- Cursor Types
- Cursors and Storage
- Spanning Commits Across FETCHes
- Dynamic SQL in PL/SQL
- Bulk Operations
- Bulk Returns
- Limiting Results
- Cursor Parameters
- Cursor Variables
- Strong and Weak Cursors
- Using Cursor Variables
- Cursor Type Errors
- Cursor Subqueries
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 for DML and DDL
- Using DBMS_SQL for Queries
- Retrieving Meta Information with DBMS_SQL
Object-Oriented Oracle
- Introducing Object-Oriented Oracle
- Defining Object Types and Tables in SQL
- Querying and Modifying Object Data
- Object Method
- Inheritance
- Type Evolution
- Object Views
- Object Types in PL/SQL
- REF Pointers
- Object Functions and Operators
Tuning PL/SQL
- PL/SQL vs SQL
- PL/SQL Performance Tips
- Tuning Goals
- Monitoring Wait Events
- DBMS_PROFILER
- DBMS_TRAC
- Execution Plans
- Interpreting Explain Plan Results
- Execution Plan Details
- Trace Files
- TKPROF
- Using trcsess
- DBMS_APPLICATION_INFO
Debugging and Error Handling
- Exception Management
- Exception Propagation
- User-Defined Exceptions
- Exception Error Messages
- Stack Management
- Debugging with DBMS_OUTPUT
- Debugging with a Table
- Using UTL_FILE
- Using DBMS_DEBUG
- SQL Developer
- Avoiding Bugs
Advanced Programming Topics
- Autonomous Transactions
- Invokers Rights
- Fine-Grained Access Control with DBMS_RLS
- Creating Pipes with DBMS_PIPE
- Writing to and Reading from a Pipe
- Table Functions
- Pipelined Table Functions
- Enabling parallel execution
- DETERMINISTIC Functions
Interfacing with External Code
- External Programs and Procedures
- External Procedure Architecture
- Configure Oracle For External Procedures
- Creating a java Stored Procedure
- Security and External Programs
- The Job Scheduler
- Manage and Drop External Jobs
- Native Compilation of PL/SQL Code
- The Oracle Call Interface (OCI and OCCI)
- Pro*C and Pro*C++
- Using Pro*C and Pro*C++
- Perl DBI/DBD Architecture
- Perl and Stored Procedures
- ODBC
- Using ODBC
- JDBC
Working With XML
- Databases and XML
- Schema Validation
- Unstructured and Structured Storage
- The XMLType Datatype
- XPath Expressions
- Extracting XML Data
- Generating XML
- XMLQuery
- XMLType Views
- Oracle XML DB Repository
Appendix A – PL/SQL Versions, Datatypes, and Language Limits
Appendix B – Oracle10g Supplied Packages