Oracle: Advanced Programming Techniques

2 days

Audience

Prerequisites

Systems analysts and programmers who are responsible for analyzing business areas, designing application systems, and manipulating data in the Oracle environment.

Experience in the fundamentals for Oracle and SQL.

Goals/Objectives

·     Describe the difference between an Instance and a Database.

·     Understand the Memory Structures on the server.

·     See the processes involved in running an Instance.

·     Learn which database files are used in Oracle.

·     See how Oracle provides a consistent view of the data in a multi-user environment.

·     Learn what a Schema is to you as the developer.

·     Demonstrate the use of the Oracle Data Dictionary.

·     Gain an understanding of how Oracle works.

·     Identify the software layers involved in Visual Basic to Oracle communication on the client and server.

·     Learn how to setup ODBC on the client machine.

·     Understand the different types of cursors and options available when accessing

·     Oracle from Visual Basic.

·     Understand database connections.

·     Demonstrate techniques for using an alternate editor for the SQL*Plus window.

·     Describe the use of the DUAL table to retrieve SYSDATE and sequences.

·     Learn how to use the DECODE function.

·     Demonstrate the use of concatenated columns for reports.

·     Demonstrate the use of DATE and TIME functions. Learn techniques for ensuring accurate date handling with a 4-digit year.

·     Learn how to create Outer Joins.

·      

·     Understand how to code SELECT statements for the best performance.

·     Define PL/SQL.

·     Know the advantages of Stored Procedures.

·     Understand how PL/SQL can be used when creating Stored Procedures.

·     Demonstrate the use of SQL*Plus to create, store, and test Stored Procedures.

·     Learn how to handle errors with PL/SQL.

·     Identify the PL/SQL data types.

·     Know how to declare variables and constants.

·     Understand how to code statements and assignments.

·     Use SELECT INTO, INSERT, UPDATE, and DELETE in simple Stored Procedures.

·     Demonstrate the use of flow control statements and SQL in PL/SQL.

·     Create Stored Procedures and Functions with PL/SQL.

·     Define the steps involved in creating a Package.

·     Create and execute a Package.

·     Define a PL/SQL cursor and the difference between an implied and explicit cursor.

·     Understand how to declare, open, fetch, and close a PL/SQL cursor.

·     Demonstrate the use of a cursor FOR loop.

·     Know when and how triggers are fired.

·     Describe the different types of triggers available and how to use them.

·     Demonstrate the creation and testing of triggers.

·     Discuss the differences between declarative referential integrity constraints and triggers.

Outline

·     Oracle Database Architecture

·     Connecting to Oracle from Visual Basic

·     SQL Review

·     Introduction to PL/SQL

·     Creating Subprograms with PL/SQL

·     Packages and Cursors

·     Triggers