Oracle Training Overview
This class is the starting point for all developers entering the world of Oracle application development. You will learn the critical aspects of Oracle development including SQL, SQL*PLUS, stored procedures, and triggers. Hands-on workshops constitute approximately 50% of the class. Overviews of relational concepts and Oracle architecture are also provided.
Oracle Training Prerequisites
Programming experience in a procedural language such as C or COBOL. Relational database experience is helpful but not required. SQL experience is not required.
Oracle Training Course duration
5 Days
Oracle Training Objectives
After successfully completing this course, you will be able to:
- Query and manipulate an Oracle database using Structured Query Language
- Utilize SQL*Plus scripting and report generation features
- Navigate the Oracle data dictionary
- Utilize transaction control statements such as Commit, Rollback and Savepoint
- Create database objects such as tables, views, indexes, and sequences
- Code PL/SQL programs that include common programming constructs such as
data-typing, variable assignment, flow control, cursor handling, and error
handling
- Create server-side stored procedures, functions and triggers
- Publish Java programs in the database and call them from Oracle
- Create and maintain Oracle packages
- Describe the new features of Oracle that address the Internet and very large
databases
- Describe the fundamental concepts of the Relational Model of Data and how
Oracle adheres to the model
- Successfully master the material for exams 1 and 2 of the Oracle Certified
Application Developer Program.
Oracle Training Course outline
- What is Oracle?
- Relational data base management system
- Scalable
- Client/Server Architecture
- Oracle Architecture
- Advanced Features
- Oracle and the Internet
- Web site needs/Oracle solutions
- Preparing for Certification
- Getting Help
- What is SQL*Plus?
- Development environment
- Connect to SQL*Plus
- SQL*Plus commands
- SQL commands
- Editing in SQL*Plus
- SQL*Plus buffer editor
- Editing: A better way
- Running SQL*Plus scripts
- Relational Databases and SQL
- The relational model
- Data structure
- Data integrity
- Primary keys
- Handling NULL in the Database
- SQL Concepts
- Set operations
- Relational database design
- Entity Relationship model
- The many to many relationship
- Normalization
- Denormalization
- Schemas
- Tables and Indexes
- Creating tables
- Constraints
- Oracle objects
- Object naming rules
- Creating tables from other tables
- Altering table structure
- Dropping tables
- Indexes
- SQL*Plus Reporting
- Reporting concepts
- Creating SQL*Plus reports
- Executing SQL*Plus reports
- Spooling output
- Customizing your environment
- Headers and footers
- Column formatting
- Subtotals
- Other SQL*Plus report commands
- Data Access Basics
- Select query
- Select list
- Distinct rows
- Calculated columns
- Column aliases
- Concatenation
- Sorting rows
- Distributes database access
- Datatypes, Basic SQL Functions, and Pseudo Columns
- Datatypes
- Automatic data type conversion
- Datetime datatypes
- Dual: the Oracle work table
- Pseudo columns
- String manipulation functions
- String parsing
- Numeric functions
- Numeric conversion functions
- Null conversion
- Date arithmetic
- Date functions
- Date conversion functions
- Datetime conversion functions
- Date arithmetic
- Data Manipulation and Transaction Control
- Inserting one row
- Rounding
- Inserting multiple rows
- Multi-table Insert
- Updating rows
- Deleting rows
- Merging Tables
- Transaction concepts
- Commit and rollback
- Locking mechanisms
- Savepoints
- Autocommit
- Grouping Data
- Group functions
- GROUP BY Clause
- HAVING Clause
- CUBE and ROLLUP
- GROUPING function
- Views and Synonyms
- View concepts
- Creating, altering, and dropping views
- Updateable and read only views
- Private synonyms
- Public synonyms
- The Oracle Data Dictionary
- Advanced Data Access
- Advanced operators: IN, BETWEEN, LIKE, IS NULL
- Joins
- ANSI Joins
- Subqueries
- Subquery operators: in, exists
- Advanced SQL*Plus Scripting
- Scripts and parameters
- Passing parameters by position
- Passing parameters by name
- Substitution variables
- User interface
- Batch processing
- SQL*Plus as a code generator
- Other Database Objects
- Privileges and roles
- Sequences
- Object support
- Collections
- Temporary tables
- What is PL/SQL?
- PL/SQL concepts
- Server side execution
- Types of PL/SQL programs
- anonymous blocks
- triggers
- stored procedures
- stored functions
- Benefits
- Block structure
- Anonymous blocks
- Displaying output
- Calling PL/SQL programs
- Debugging and testing in SQL*Plus
- Procedure Builder and related Tools
- PL/SQL Programming Basics
- Delimiters
- Datatypes
- Variables and constants
- Naming variables
- Variable scope
- Calling SQL functions from PL/SQL
- Datatyping with %type
- PL/SQL Records and %rowtype
- Select into
- If-then-else
- Case
- Loops
- Cursors
- Cursor concepts
- Declaring a cursor
- Cursor attributes
- Fetch loop
- Parameters
- Cursor for loops
- Current row reference and data maintenance
- PL/SQL Stored Procedures
- Creating procedures
- Executing procedures
- Parameters
- Dropping and recreating procedures
- Reporting errors
- Source code
- Dependencies
- Privileges
- Synonyms
- PL/SQL Stored Functions
- Function concepts
- Creating functions
- Executing functions
- Introduction to Pipelined Functions
- Error Handling
- Error handling concepts
- The exception block
- Predefined exceptions
- User defined exceptions
- Passing exceptions to callers
- Packaged exceptions
- Application errors
- Error propagation
- Triggers
- Trigger concepts
- Creating a trigger
- Row versus statement triggers
- Before versus after triggers
- Getting before and after values
- Trigger attributes
- "Instead of" triggers
- Enabling and disabling triggers
- DDL Triggers
- System Event Triggers
- Autonomous Triggers
- Packages
- Package concepts
- Advantages
- Creating a package
- Global variables
- Oracle supplied packages
- Miscellaneous 9i Features
- Flashback Queries
- External Tables
- Java
- The Java language
- Oracle's Java support
- Java stored procedures
- Java installation
- Calling Java from Oracle
- Where do I go from here?
- Certification
- Sources and courses
Hardware/Software Requirements
- Hardware Requirements
- Minimal Processor: Pentium 166 or Pentium 200
- Recommended Processor: Pentium 233 or Pentium 266
- RAM: Minimum 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, Windows 2000
- Oracle9i 9.2 or above installed on each student machine
- A new install is strongly recommended.
- Microsoft Internet Explorer 5 or Netscape Navigator 4.5
- Adobe Acrobat Reader or Acrobat 4.0 or later (for the course presentation)
|
