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 and Rollback
- 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
- Nulls
- SQL
- 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
- Columns list
- Distinct rows
- Calculated columns
- Column aliases
- Concatenation
- Sorting rows
- Selecting rows
- Distributes database access
- Datatypes, Basic SQL Functions, and Pseudo Columns
- Datatypes
- Automatic data type conversion
- 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
- Date arithmetic
- Data Manipulation and Transaction Control
- Inserting one row
- Rounding
- Inserting multiple rows
- Updating rows
- Deleting rows
- Transaction concepts
- Commit and rollback
- Locking mechanisms
- Savepoints
- Autocommit
- Grouping Data
- Group functions
- GROUP BY Clause
- HAVING Clause
- 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
- Selecting nulls
- 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
- 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
- Delimiters
- Datatypes
- Variables and constants
- Naming variables
- Variable scope
- Calling SQL functions from PL/SQL
- %type and %rowtype
- Select into
- If-then-else
- 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
- 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
- Packages
- Package concepts
- Advantages
- Creating a package
- Global variables
- Oracle supplies packages
- Introduction to SQL Tuning
- SQL tuning basics
- The plan table
- Explain the plan
- SQL tuning
- Automating explains
- 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: 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 or Windows 2000
- Oracle8i 8.1.6 or above
- Java JDK 1.1.5 or above*
- Microsoft Internet Explorer 5 or Netscape Navigator 4.5
- Adobe Acrobat Reader or Acrobat 4.0 or later (for the course presentation)*
- Student Files
- Text Editor*
|
