Oracle8i Advanced PL/SQL Programming
Oracle Training Overview
This class will complete a developers training curriculum concerning Oracle PL/SQL. Advanced PL/SQL constructs such as records, tables, LOBs, dynamic PL/SQL and File input/output are discussed. The Oracle facilities to tune, trace, and debug your PL/SQL programs are provided. The concept of packages is further explored and explained with using suggested methods and techniques. Intersession communication and queuing of Oracle jobs is also explained.
Oracle Training Prerequisites
Programming experience in PL/SQL
Oracle Training Course duration
3 Days
Oracle Training Objectives
After successfully completing this course, you will be able to:
- Define PL/SQL records and tables
- Create PL/SQL programs to read and write operating system files
- Process Large Objects (LOBs)
- Code PL/SQL packages using advanced techniques
- Tune, trace and debug PL/SQL programs
- Create dynamic PL/SQL programs
- Schedule PL/SQL jobs
- Communicate between database sessions
- Provide transaction processing capabilities
Oracle Training Course outline
- PL/SQL Review
- PL/SQL Program Structure
- PL/SQL Program Types
- IF/THEN/ELSE and IF/THEN/ELSIF
- LOOPing
- SELECT INTO
- Cursors
- Exceptions
- Privileges
- PL/SQL Composite Datatypes
- PL/SQL Records and Tables
- Objects and Collections
- Nested Tables and VARRAYs
- CAST/THE/CURSOR Operators
- Advanced PL/SQL Package Practices
- Package Variable Scoping
- Overloading
- Oracle Supplied Packages Review
- Dynamic PL/SQL Coding Using NDS and DBMS_SQL
- NDS vs. DBMS_SQL
- Single and Multi-row NDS Queries
- Privileges
- Tune and Trace PL/SQL Programs
- Oracle8i New Features
- Invoker Rights
- Bulk Binds
- Autonomous Transactions
- Database Event Triggers
- LOB Handling Using DBMS_LOB
- LOB Types
- LOB vs. LONG
- Creating/Updating/Reading/Comparing LOBs
- Writing/Appending/Copying/Erasing LOBs
- Temporary LOBs
- Opening/Inserting into/Closing BFILEs
- File Input/Output Using UTL_FILE
- init.ora parameters
- Open/Close/Read/Write files
- Schedule Batch Jobs Using DBMS_JOB
- Submitting a job
- Running a job
- Removing a job
- Changing job parameters
- Cleaning up broken jobs
- Intersession Communication Using DBMS_PIPE and DBMS_ALERT
- Sending/Receiving Alerts using DBMS_ALERT
- Removing Alerts using DBMS_ALERT
- Packing and Sending Messages using DBMS_PIPE
- Creating and Removing Pipes using DBMS_PIPE
- Queuing using DBMS_AQ and DBMS_AQADM
- Creating a message object with DBMS_AQADM
- Creating a queue table with DBMS_AQADM
- Creating/Starting/Stopping/Dropping queues with DBMS_AQADM
- Enqueuing and Dequeuing Messages with DBMS_AQ
Hardware/Software Requirements
- Hardware Requirements
- Minimal Processor: Pentium 166 or Pentium 200
- Recommended Processor: Pentium 233 or Pentium 266
- RAM: 128 MB (256 MB recommended)
- Oracle Corporation also recommends increasing virtual memory to 200 MB. (Modify it in the Performance tab of System Properties in the Control Panel.)
- Available Disk Space (FAT or HTFS): 3 GB
- Video: 256 color
- A connection to the Internet is desirable (but not absolutely necessary) to show students various Oracle-related sites.
- Software Requirements
- Windows NT 4.0 or Windows 2000
- Oracle8i 8.1.6 or above
- Java JDK 1.1.5 or above*
- Microsoft Internet Explorer 5 or Netscape Navigator 4.5
- Adobe Acrobat Reader or Acrobat 4.0 or later (for the course presentation)*
- Student Files
- Text Editor*
