This hands-on class is designed for PL/SQL developers writing or supporting complex enterprise systems and provides a critical continuation of learning for the PL/SQL developer. This course is the next step for persons who have knowledge of PL/SQL basics.
You will learn how to build systems that incorporate:
- New 9i features such as ANSI CASE statement, extracting DDL with DBMS_METADATA and using datetime data to support time zone data
- Object-Relational features such as object types, constructor methods, collections and more
- VARRAYs and Nested Tables to build Collections into Oracle tables
- Dynamic SQL within a PL/SQL program, letting the program build the SQL statement at run time
- Pipelined Table Functions to create virtual tables and perform efficient data transformations
- Packages to implement global (session) variables and overloaded procedures
- DBMS_LOB to process LOB (large object) datatypes, ideal for storing images, sound and large character strings in the database
- UTL_FILE to read and write to sequential files outside the database
- Job scheduling (one-time only or repetitive batch jobs scheduling with DBMS_JOB)
- Intersession communication with DBMS_PIPE or DBMS_ALERT
- Sophisticated transaction-based messaging with Advanced Queuing (DBMS_AQ)
You will also be introduced to PL/SQL program tracing and tuning with DBMS_TRACE and DBMS_PROFILER.
This course has been completely updated for Oracle9i. Working examples are included throughout the course book and students have access to electronic versions of all examples and lab solutions. Approximately 50% of class time is spent on hands-on exercises.
Audience: Application/software developers.
Objectives: After successfully completing this course, you wil be able to:
- Code the CASE statement
- Create DDL from existing database objects with DBMS_METADATA
- Incorporate datetime data into your database and applications
- Build autonomous transactions
- Incorporate object-relational features into your applications
- Code pipelined table functions in Oracle9i databases
- Package your PL/SQL objects
- Use DBMS_LOB to process LOB data
- Use UTL_FILE to read and write to sequential files
- Schedule batch jobs with DBMS_JOB
- Communicate across Oracle sessions
- Use database-centric transaction-based messaging Understand the process of tuning and tracing PL/SQL programs