Oracle Fundamentals 5-day Workshop

Course Description

Give your user community that competitive edge with this local Oracle Workshop. The course starts out with a complete overview of the Oracle architecture so students can get an understanding of how Oracle works. Attendees will learn how to access data from Oracle using the SQL and PL/SQL languages.
The focus of this course is to give a working knowledge of SQL and PL/SQL to non-IT people, or to people who are new to the relational database world or new to the Oracle RDBMS. This is an excellent course for those people that need a better knowledge of the PL/SQL language. This course covers all the latest features of SQL and PL/SQL using databases to Oracle 21c.
The attendees have the opportunity to construct various SQL statements to solve simple and complex business questions. Dan will cover how to update data, delete data, create tables, data relationships, and will touch briefly on using indexes to aid in the performance of the SQL. This course also covers creating various types of reports and web reports using the SQL*Plus interface.
The attendees have the opportunity to learn how to find the poorly-performing components of PL/SQL routines, use Oracle’s newer DEBUG routines to find problems in PL/SQL logic and how to use the latest features of the PL/SQL language such as the new optimizing compiler and collections. The course contains additional white papers on Java and PL/SQL and executing external procedures.
The course utilizes a variety of current tools. Students will have the opportunity to learn more about SQL Developer, TOAD, as well as the Oracle tools like SQL*Plus.
5 days
Contact us for pricing
 

Prerequisites

A working knowledge of a PC is helpful but not required.

Topics covered

• Oracle Architecture
• Introduction to SQL topics:
o Select, Insert, Update, Delete
• SQL Scripting Topics
o Formatting SQL
• Creating Tables/Indexes/Views
• Privileges
• Advanced Topics
o Joins, Functions, Group Functions, cross-reference output
o ANSI SQL Syntax
o Referential Integrity (Data Relationships)
o ODBC Drivers and MS Products
o SQL*Plus scripting topics
• Introduction to PL/SQL
o Syntax and language structure review
• PL/SQL Topics
o Package Usage (both User Defined and Oracle Defined Packages)
o Compiler Options, Code Encryption, Conditional Compilation
o Cursor Sharing/Cursor Variables
o Collections
o Triggers
o Dynamic SQL
o Autonomous Transactions
o PL/SQL Coding Tips
o Debugging PL/SQL
o PL/SQL Profiling

Attendees Receive

• Study guide with presentations and relevant white papers
• PL/SQL Reference Pocket Guide, SQL book
• Diskette full of example SQL and SQL*Plus scripts
• Opportunity to ask the tough Oracle questions
• A hands-on opportunity to learn more about Oracle

Course Outline

Day 1: Introduction to Oracle SQL

- Oracle RDBMS Architecture overview
- Introduction to TOAD/SQL*Plus
- Introduction to SQL
Database queries
Data Manipulation
• Insert, Update, Delete
Creating Tables
- Introduction to SQL Scripts

Day 2: Advanced SQL Topics

- Functions
- Advanced SQL
 Joins
 ANSI SQLSyntax
 Sub queries
- Group Functions
- Intro to SQL Tuning
- Referential Integrity
- Using ODBC Drivers and MS Products
- Building HTML Reports/SQL*Plus scripting topics

Day 3: Oracle PL/SQL

o Introduction to PL/SQL
Syntax and language structure
o Basic Syntax
o IF-Then-Else/Case/Looping
o Cursor management
o Exception handling

Day 4: Oracle PL/SQL

o Procedures/Functions
- Packages
- PL/SQL Compiler Options
- Definer/Invoker Rights
- Executing and Exception handling
- Autonomous Transactions
- Database Triggers
 DML, Instead of, ServerError, Startup, Shutdown, Logon

Day 5: Oracle PL/SQL

- Working with Cursors
User Defined Datatypes
Cursor Sharing
Cursor Variables
Strong vs Weak Cursors
- Working with Collections
Associate Arrays
Nested Tables
Varrays
Using Collections for Reference Table
- Oracle Provided Packages
DBMS_Alert
DBMS_Job
DBMS_Output
DBMS_Scheduler
UTL_File
Other Provided Packages
- Debugging PL/SQL Routines
- PL/SQL Profile
- PL/SQL Coding Tips and Techniques

About Dan Hotka

Dan Hotka is a Training Specialist and an Oracle ACE Director who has over 40 years in the computer industry, over 34 years of experience with Oracle products. His experience with the Oracle RDBMS dates back to the Oracle V4.0 days. Dan enjoys sharing his knowledge of the Oracle RDBMS. Dan is well-published with 12 Oracle books and well over 200 published articles. He is frequently published in Oracle trade journals, regularly blogs, and speaks at Oracle conferences and user groups around the world.
OracleDan HotkaOracle 12cOracle Database 12cOracle PL-SQLOracle PL-SQL TuningOracle PL/SQLOracle PL/SQL TuningOracle SQLOracle SQL Performance TuningOracle SQL TuningOracle SQL Tuning10gOracle SQL Tuning11gOracle SQL Tuning12cOracle TuningOracle 21cOracle 21c TuningOracle Expert