Oracle 9i PL/SQL Foundations
Course Description
This course will cover the Oracle 9i PL/SQL programming language. You will be able to control data sharing and locking, develop an understanding of multi-user and concurrent transactions and develop triggers, procedures, function and packages.
View other Oracle Courses available
View other Oracle Courses available
5 Days
Contact us for pricing
Prerequisites
Oracle 9i Foundation: SQL Basics & SQL *Plus or equivalent experience.Introduction to PL/SQL
History of PL/SQLFeatures and benefits of PL/SQL
Relationship of PL/SQL to SQL
PL/SQL development tools
Native Compilation
PL/SQL Basics
PL/SQL anonymous block structure, Lexical units, Variable declarations, types, and RecordsSQL *Plus development environment
Displaying messages with DBMS_OUTPUT
Object naming rules, and PL/SQL stype guide and coding conventions
Working with Database Data
SELECTing single rowsDeclaring variable datatypes dynamically
Modifying database data (DML)
Transaction control statements
Selecting Multiple Rows Using Cursors
Declaring explicit cursorsImplicit cursor attributes
Using teh cursor FOR LOOP
Exception Handling
Writing an exception handler sectionHandling predefined exceptions, Controlling exception processing - exception propagation
Using RAISE_APPLICATOIN_ERROR
Preventing unhandled exceptions
Exception propagations
Using PRAGMA EXCEPTION_INIT
Advanced Cursors
Cursor parametersTaking advantage of a weak cursor variable
OPEN FOR, FETCH and CLOSE
Using the FOR UPDATE clause
Using PL/SQL collections and nested collections
Introduction to Procedures and Functions
Creating stored PL/SQL objects, procedures, functionsCreating Packages
Creating package specifications and bodiesCreating DML Triggers
Triggering events, Trigger behaviorCorrelation identifiers, Multi-statement triggers
Trigger firing behavior, Enabling/Disabling triggers
Advanced Packages
Initialising variables, Module Overloading, Recursion, Purity levels, Using the "Persistent State" to advantage, and One Time Only ProceduresAdvanced Triggers
Triggerlimitations, Mutating and Constraining TablesUsing CALL, and client triggers
DDL Triggers
Using SERVERERROR event
Schema vs. Database triggers
Using alternative events and levels
INSTEAD OF triggers on views
PL/SQL Composite Datatypes and Collections
PL/SQL records, PL/SQL associative arrays, and arrays of recordsUsing PL/SQL record variables
PL/SQL collections
Bulk-Bind Data Loading Using PL/SQL
Defining bulk bindsError handling with bulk binds
Using Oracle Supplied Packages
DBMS_OUTPUT packageUTL_FILE package (file i/o)
DBMS_ALERT Package
DBMS_PIPE Package DBMS_JOB Package
DBMS_STATS Package
DBMS_UTILITY Package
UTL_SMTP Package
UTL_SMTP Package
DBMS_SQL Package
Writing Native Dynamic SQL
PL/SQL Wrapper
PL/SQL wrapper (source code encryption)Let`s wrap
Understanding Dependencies
Viewing dependenciesEffect of breaking dependency chain
Large Object Management in PL/SQL
Differences between LONG/LONG RAW and LOBsCreating and using BFILEs, and tables with LOBs
LOBs and PL/SQL
DBMS_LOB capabilities
Temporary LOBs
Objects
Basic Objects, Object InheritanceOracleOracle 9iOracle Database 9i