InformIT Home About InformIT Oracle Workforce Development Program Cisco Networking Academy Program Microsoft IT Training & Certification Other Intensive Training International IT Certifications
  10g DBA Track  
  10g iDS Track  
  10g Java Develop. Track  
  10g App. Server Track  
  9i DBA Track  
  9i iDS Track  
  9i J2EE Java Develop. Track  
  9i AS Web Adm. Track  
Oracle Data Warehouse






























































Oracle 9i : Program with PL/SQL

This class introduces participants to PL/SQL and helps them understand the benefits of this powerful programming language. In the class, participants learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications. Participants learn to create procedures, functions, packages, and database triggers. Participants use iSQL*Plus to develop these program units. Participants also learn to manage PL/SQL program units and database triggers, to manage dependencies, to manipulate large objects, and to use some of the Oracle-supplied package.

This course helps Participants prepare to successfully pass the Oracle certification exam #1z0-147 to become an Oracle PL/SQL Developer Certified Associate (OCA-PL/SQL Developer).


Course schedule

Duration: 30 hours
To view the schedule for this class, click here.

Who should attend

This course is valuable for programmers, application developers, database administrators, Web developers and others who are or will be, programming with PL/SQL.


•  Introduction to Oracle9i: SQL
. or equivalent knowledge.

Course Outline


  • Describing PL/SQL
  • Describing the Use of PL/SQL for the Developer and the Database Administrator
  • Explaining the Benefits of PL/SQL
  • PL/SQL program constructs
  • PL/SQL anonymous block structure
  • Subprogram block structure
  • Course Outline and overview
Declaring Variables
  • Recognizing the Basic PL/SQL Block and Its Sections
  • Describing the Significance of Variables in PL/SQL
  • Distinguishing Between PL/SQL and Non-PL/SQL Variables
  • Declaring Variables and Constants
  • Executing a PL/SQL Block
Writing Executable Statements
  • Recognizing the Significance of the Executable Section
  • Writing Statements Within the Executable Section
  • Describing the Rules of Nested Blocks
  • Executing and Testing a PL/SQL Block
  • Using Coding Conventions
Interacting with the Oracle Server
  • Writing a Successful SELECT Statement in PL/SQL
  • Declaring the Data type and Size of a PL/SQL Variable Dynamically
  • Writing Data Manipulation Language (DML) Statements in PL/SQL
  • Controlling Transactions in PL/SQL
  • Determining the Outcome of SQL DML Statements
Writing Control Structures
  • Identifying the Uses and Types of Control Structures
  • Constructing an IF Statement
  • Constructing and Identifying Different Loop Statements
  • Controlling Block Flow Using Nested Loops and Labels
  • Using Logic Tables
Working with Composite Data types
  • Creating User-Defined PL/SQL Records
  • Creating a PL/SQL Table
  • Creating a PL/SQL Table of Records
  • Differentiating Among Records, Tables, and Tables of Records
Writing Explicit Cursors
  • Using a PL/SQL Record Variable
  • Distinguishing Between the Implicit and Explicit Cursor
  • Writing a Cursor FOR Loop
Advanced Explicit Cursor Concepts
  • Writing a Cursor that Uses Parameters
  • Determining When a FOR UPDATE Clause in a Cursor Is Required
  • Using a PL/SQL Table Variable
  • Using a PL/SQL Table of Records
Handling Exceptions
  • Defining PL/SQL Exceptions
  • Recognizing Unhandled Exceptions
  • Listing and Using Different Types of PL/SQL Exception Handlers
  • Trapping Unanticipated Errors
  • Describing the Effect of Exception Propagation in Nested Blocks
  • Customizing PL/SQL Exception Messages
Creating Procedures
  • Describe the uses of procedures
  • Create procedures
  • Create procedures with arguments
  • Invoke a procedure
  • Remove a procedure
Creating Functions
  • Describe the uses of functions
  • Create a function
  • Invoke a function
  • Remove a function
  • Differentiate between a procedure and a function
Managing Subprograms
  • Describe system privilege requirements
  • Describe object privilege requirements
  • Query the relevant data dictionary views
  • Debug subprograms
Creating Packages
  • Describe packages and list their possible components
  • Create packages that include public and private subprograms, as well as global and local variables
  • Invoke objects in a package
  • Remove packages
More Package Concepts
  • Write packages that use the overloading feature of PL/SQL
  • Avoid errors with mutually referential subprograms
  • Initialize variables with a one-time-only procedure
  • Specify the purity level of packaged functions
  • Describe the persistent state of packaged variables, cursors, tables, and records
  • Query the relevant data dictionary views
Using Oracle-Supplied Packages
  • Overview of Oracle-supplied packages
  • View examples of some supplied packages
  • Write dynamic SQL
Creating Database Triggers
  • Describe different types of triggers
  • Describe database triggers and their use
  • Create database triggers
  • Describe database trigger firing rules
  • Drop database triggers
More Trigger Concepts
  • Create triggers that fire when certain database actions occur
  • List some of the limitations of database triggers
  • Determine when to use database triggers or Oracle Server features
  • Create triggers by using alternative events (notINSERT/UPDATE/DELETE)
  • Create triggers by using alternative levels (not STATEMENT/ROW)
  • Query the relevant data dictionary views
Managing Dependencies
  • Overview of object dependencies
  • Manage PL/SQL objects for recompilation
Manipulating Large Objects
  • Compare and contrast LONG/RAW/LONG RAW with large objects (LOBs)
  • Understand LOBs
  • Manage binary large file objects (BFILEs)
  • Use PL/SQL with an LOB
  • Create a table with LOB columns
  • Manipulate LOBs
  • Use DBMS_LOB Oracle-supplied packages
  • Create a temporary LOB





INFORMIT - Skill Development Center
Jl. Purnawarman 9c, Bandung 40117- Phone (022) 4224 168 / 4223 168
Fax. (022) 4231 168 - E-mail:

  Home | About Us | Oracle | Cisco | Microsoft | Other Training | Certification | Training Calendar  
© 2006, InformIT Ganesa Pratama. All Right Reserved