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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 
 
Oracle10g: 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 PL/SQL Developer, Database Designers, Forms Developer, or Technical Consultant

Prerequisites

•  Oracle 10g: Introduction to SQL
. or equivalent knowledge.

Course Outline

INTRODUCTION TO PL/SQL
•  Benefits of PL/SQL
•  Overview of the Types of PL/SQL blocks

DECLARING PL/SQL IDENTIFIERS
•  Identify the Different Types of Identifiers
•  List the Uses for Variables
•  Declare PL/SQL Variables

WRITING EXECUTABLE STATEMENTS
•  Basic Block Syntax Guidelines
•  Use Operators in PL/SQL

INTERACTING WITH THE ORACLE SERVER
•  Include SELECT Statements in PL/SQL
•  Manipulate Data in the Server Using PL/SQL
•  The SQL Cursor concept

WRITING CONTROL STRUCTURES
•  Conditional processing Using IF & CASE Statements
•  Use Iterative Control with Looping Statements

WORKING WITH COMPOSITE DATA TYPES
•  Learn the Composite Data Types of PL/SQL Records and Tables
•  Inserting and Updating with PL/SQL Records
•  Use INDEX BY Tables

USING EXPLICIT CURSORS
•  Cursor FOR Loops
•  Use the FOR UPDATE Clause to Lock Rows
•  Use the WHERE CURRENT Clause to Reference the Current Row

HANDLING EXCEPTIONS
•  Predefined Exceptions
•  Propagate Exceptions
•  Use The RAISE_APPLICATION_ERROR Procedure

CREATING STORED PROCEDURES
•  Differentiate between formal and actual parameters
•  Parameter modes

CREATING STORED FUNCTIONS
•  List the CREATE OR REPLACE FUNCTION syntax
•  Using stored functions in SQL statements

CREATING PACKAGES
•  List the benefits or using PL/SQL packages
•  Create & Remove packages

USING MORE PACKAGE CONCEPTS
•  Overload definitions
•  Use forward declarations
•  Create a one-time package initialization block
•  Wrap code to hide the source

UTILIZING ORACLE SUPPLIED PACKAGES
•  Describe & use how DBMS_OUTPUT, UTL_FILE, HTP, UTL_MAIL, DBMS_SCHEDULER package to schedule PL/SQL code to run

DYNAMIC SQL AND METADATA
•  Write dynamic SQL using the EXECUTE IMMEDIATE syntax & with the DBMS_SQL package
•  Generate DDL from metadata using the DBMS_METADATA package

DESIGN CONSIDERATIONS FOR PL/SQL CODE
•  Use the NOCOPY, PARALLEL ENABLE hint
•  Use the AUTONOMOUS TRANSACTION pragma
•  Set the AUTHID directive

MANAGING DEPENDENCIES
•  Describe dependent and referenced objects
•  Track procedural dependencies with dictionary views
•  Manage local and remote procedural dependencies

MANIPULATING LARGE OBJECTS
•  Create and maintain LOB data types
•  Use the DBMS_LOB PL/SQL package to control LOBs

CREATING TRIGGERS
•  Describe different types of triggers
•  Create database triggers
•  Describe database trigger firing rules
•  Remove database triggers

APPLICATIONS FOR TRIGGERS
•  Create database and system event triggers
•  Create triggers on DDL statements
•  Explain the rules for reading and writing to tables with triggers
•  Manage trigger code

UNDERSTANDING AND INFLUENCING THE PL/SQL COMPILER
•  Describe native compilation and interpreted compilation
•  Explain the compiler warning mechanism
•  Use DBMS_WARNING to implement compiler warnings

 

 

 
 


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

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