Oracle Database

Oracle Database Programming

Course Duration: 60 hours

An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management.

Introduction to Oracle Technology

  • Applying data modeling techniques
  • Oracle database tools: SQL Developer and SQL*Plus

Assessing the oracle database architecture

  • Defining memory structures, processes and SGA
  • Establishing a storage framework

Managing the Oracle Databases

  • Configuring Oracle Database 11g
  • Working with Oracle SQL Developer

Retrieving data efficiently with sQL developer

  • Selecting, restricting and ordering data
  • Avoiding pitfalls in null values
  • Exploiting built-in SQL functions

Applying Powerful SQL Techniques

  • Joins, outer joins and ANSI joins
  • Grouping data and applying aggregate functions
  • Combining result sets with set operators
  • Comparing simple and correlated subqueries

Modify Data with SQL statements

  • Inserting, updating, deleting and merging data
  • Controlling transactions with ROLLBACK and COMMIT

Implementing the Physical Design

  • Mapping logical model to physical design
  • Creating users and schemas

Construct and maintaing tables

  • Altering and dropping columns
  • Restoring data with Flashback and the recycle bin

Building views,Sequences and Synonyms

  • Filtering data with views
  • Generating unique IDs with sequences
  • Streamlining access to objects with synonyms

Enforcing Integrity

  • Implementing referential integrity with primary, unique and foreign keys
  • Managing transactions and data with deferred and enforced constraints

Securing The data

  • Authenticating users with password aging
  • Controlling access with system and object privileges
  • Simplifying privilege management with roles

Improving performance

  • Guidelines for creating indexes
  • Indexing the data for optimal access
  • Managing unique, nonunique and composite indexes

Writing basic programs

  • Controlling logic with IF and CASE statements
  • Performing iterations with WHILE and FOR LOOPs
  • Defining and managing PL/SQL records
  • Trapping errors with exception handlers

Processing Data with cursors

  • Declaring cursors to perform row-level operations
  • Passing parameters to cursors to increase flexibility
  • Simplifying cursors with FOR LOOPs
  • Improving performance with CURRENT OF or ROWID

Modularizing code

  • Constructing procedures and functions
  • Debugging programs with DBMS_OUTPUT