Duration: 5 days
Description
This class focuses on key development skills necessary to work in Oracle10g environments. Topics will include the Oracle architecture, Oracle SQL programming and features, PL/SQL programming features, SQL tuning features and concepts as well as key Oracle tools used in development. This class will develop expertise and skills with SQL, PL/SQL and SQL tuning in Oracle10g as well as troubleshooting and diagnostics capability.
Topics
- Oracle overview of database, Apps Server and developer tools
- Relational and object-relational designs
- Review relational database design
- Data models
- Using SQL*Plus, iSQL*Plus
- SQL Developer
- SQL*Plus commands
- Changing your password
- Differences between SQL*Plus and iSQL*Plus
- SQL*Plus Worksheet
- SELECT statements
- Components of SQL statements
- Creating SQL scripts
- Filtering records
- Comparison conditions
- LIKE operator
- Working with NULL
- Logical conditions (AND, OR, NOT)
- Sorting data with ORDER BY
- Building SQL statements
- Techniques with SELECT statements
- Working with DUAL
- Using the data dictionary
- The GROUP BY clause
- Aggregate functions
- HAVING clause
- Different ways to group data
- What to watch out for with GROUP BY
- INSERT, UPDATE and DELETE statements
- Understanding read consistency
- Transactions
- The UNION, INTERSECT, and MINUS operators
- Techniques for SET operators
- SAMPLE clause, GROUPING function, CUBE and ROLLUP operators
- Introduction to Analytical functions
- The WITH clause
- Advanced subqueries
- Regular expressions
- Dealing with case sensitivity with SQL
- Techniques for writing joins
- Complex data retrieval using equi-joins, Cartesian-joins, self-joins, outer-joins
- Hierarchical and tree-structured queries
- Regular and correlated subqueries
- Single and multiple row subqueries
- Group functions in subqueries
- Guidelines for subqueries
- Oracle native and ANSI 99 SQL
- Character functions
- Date functions
- Numeric functions
- Conversion functions
- Miscellaneous functions
- SQL*Plus application and report writing using parameters, substitution variables, calculations, groups, breaks, summaries, headers and footers, Web reports
- Working with schema objects
- Sequences
- Synonyms
- Database links
- Creating, altering and deleting tables
- Renaming tables
- Managing tables
- Working with constraints
- Primary and foreign keys
- Unique keys
- NOT NULL constraints
- Check constraints
- DEFAULT values
- Creating and accessing external tables
- Different types of indexes
- PL/SQL in the Oracle10g environment
- Features of PL/SQL
- The Block Structure
- Language Rules
- Executing PL/SQL from SQL*Plus
- Variables and Constants
- Identifiers
- Customizing SQL*Plus for PL/SQL Scripts
- SELECT INTO Statement
- Using Bind Variables
- Interactive PL/SQL Programs
- Oracle Datatypes are supported in PL/SQL
- Numeric Datatypes
- Datatype Conversion
- Implicit and Explicit Conversion
- %TYPE and %ROWTYPE
- Subtypes
- Subtype Declarations
- Labels
- Variable Scope and Visibility Rules
- SQL and PL/SQL Reserved Words
- PL/SQL Reserved Words
- Direction of PL/SQL
- External Routines and Java Stored Procedures
- Control Flow
- Operators
- Built-In Functions
- Handling Exceptions
- Working with Exceptions
- SQLCODE and SQLERRM Functions
- Predefined PL/SQL Exceptions
- User-Defined Exceptions
- Working with Exceptions
- The Procedure Raise_Application_Error
- IF Statements
- Generic LOOP
- Working with NULL
- LOOP Labels
- FOR LOOP
- WHILE LOOP
- GOTO Statement
- Working with Cursors
- Working with Cursors
- Implicit Cursors and Attributes
- Explicit Cursors
- Explicit Cursor Attributes
- Using Subqueries with Cursors
- Using Cursor FOR LOOPs
- Using %ROWTYPE with Cursors
- Using Aliases and Subqueries
- Using Aliases and Subqueries (FOR LOOP)
- Using Parameters with Explicit Cursors
- Modifying Data using Explicit Cursors
- Managing Transactions in PL/SQL Programs
- Procedures and Functions
- Working with Subprograms
- Named Subprograms
- Software Evolution
- Stored Procedures
- Parameters
- NOCOPY Compiler Directive
- Parameter Default Values
- Positional and Named Notation
- Executing a Stored Procedure
- Compiling and Debugging Stored Procedures
- Compiling a Stored Procedure
- Stored Function Syntax
- Executing a Function from SQL*Plus
- The RETURN Statement
- Advantages of Packages
- Packages
- What can a Package Contain?
- Components of a Package
- Package Specification
- Package Body
- Defining Package Components
- Package Scope
- Public Scope
- Private Scope
- Accessing Package Components
- Privileges
- Managing Side Effects
- Defining Subprograms within another Subprogram
- Forward Declarations
- Overloading
- Restrictions on Overloading
- Restrictions on Packaged Variables
- Triggers
- Purpose of Triggers
- Trigger Guidelines
- Table Triggers
- Firing Order of Table Triggers
- Table Trigger Syntax
- ALTER and DROP Trigger Commands
- Boolean Functions
- Reference Variables for ROW Triggers
- ROW Trigger Examples
- Information about Triggers
- WHEN Clause
- Restrictions
- Database Triggers
- Event Functions
- Resource Manager Events
- Client Events
- Schema Triggers
- INSTEAD-OF Triggers
- object-relational types
- Autonomous Transactions
- Packages and Triggers
- SQL tuning fundamentals
- Understanding the Oracle10g tuning model
- Understanding execution plans
- Explain plan
- Oracle join-optimization
- Working with hints
Prerequisites
- A background in data processing, relational database concepts and programming is strongly recommended.
|