1/17

Contents

List of Examples

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Changes in This Release for Oracle Database 2 Day Developer's Guide

• Changes in Oracle Database 11g Release 2 (11.2.0.4)

1 Introduction

• About Oracle Database Developers
• About This Document
• About Oracle Database
  • About Schema Objects
  • About Oracle Database Access
    • About SQL*Plus
    • About SQL Developer
    • About Structured Query Language (SQL)
    • About Procedural Language/SQL (PL/SQL)
    • About Other Client Programs, Languages, and Development Tools
• About Sample Schema HR

2 Connecting to Oracle Database and Exploring It

• Connecting to Oracle Database from SQL*Plus
• Connecting to Oracle Database from SQL Developer
• Connecting to Oracle Database as User HR
  • Unlocking the HR Account
  • Connecting to Oracle Database as User HR from SQL*Plus
  • Connecting to Oracle Database as User HR from SQL Developer
• Exploring Oracle Database with SQL*Plus
  • Viewing HR Schema Objects with SQL*Plus
  • Viewing EMPLOYEES Table Properties and Data with SQL*Plus
• Exploring Oracle Database with SQL Developer
  • Tutorial: Viewing HR Schema Objects with SQL Developer
  • Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer
• Selecting Table Data
  • About Queries
  • Running Queries in SQL Developer
  • Tutorial: Selecting All Columns of a Table
  • Tutorial: Selecting Specific Columns of a Table
  • Displaying Selected Columns Under New Headings
  • Selecting Data that Satisfies Specified Conditions
  • Sorting Selected Data
  • Selecting Data from Multiple Tables
  • Using Operators and Functions in Queries
    • Using Arithmetic Operators in Queries
    • Using Numeric Functions in Queries
    • Using the Concatenation Operator in Queries
    • Using Character Functions in Queries
    • Using Datetime Functions in Queries
    • Using Conversion Functions in Queries
    • Using Aggregate Functions in Queries
    • Using NULL-Related Functions in Queries
    • Using CASE Expressions in Queries
    • Using the DECODE Function in Queries

3 About DML Statements and Transactions

• About Data Manipulation Language (DML) Statements
  • About the INSERT Statement
  • About the UPDATE Statement
  • About the DELETE Statement
• About Transaction Control Statements
• Committing Transactions
• Rolling Back Transactions
• Setting Savepoints in Transactions

4 Creating and Managing Schema Objects

• About Data Definition Language (DDL) Statements
• Creating and Managing Tables
  • About SQL Data Types
  • Creating Tables
    • Tutorial: Creating a Table with the Create Table Tool
    • Creating Tables with the CREATE TABLE Statement
  • Ensuring Data Integrity in Tables
    • About Constraint Types
    • Tutorial: Adding Constraints to Existing Tables
  • Tutorial: Adding Rows to Tables with the Insert Row Tool
  • Tutorial: Changing Data in Tables in the Data Pane
  • Tutorial: Deleting Rows from Tables with the Delete Selected Row(s) Tool
  • Managing Indexes
    • Tutorial: Adding an Index with the Create Index Tool
    • Tutorial: Changing an Index with the Edit Index Tool
    • Tutorial: Dropping an Index
  • Dropping Tables
• Creating and Managing Views
  • Creating Views
    • Tutorial: Creating a View with the Create View Tool
    • Creating Views with the CREATE VIEW Statement
  • Changing Queries in Views
  • Tutorial: Changing View Names with the Rename Tool
  • Dropping Views
• Creating and Managing Sequences
  • Tutorial: Creating a Sequence
  • Dropping Sequences
• Creating and Managing Synonyms
  • Creating Synonyms
  • Dropping Synonyms

5 Developing Stored Subprograms and Packages

• About Stored Subprograms
• About Packages
• About PL/SQL Identifiers
• About PL/SQL Data Types
• Creating and Managing Standalone Subprograms
  • About Subprogram Structure
  • Tutorial: Creating a Standalone Procedure
  • Tutorial: Creating a Standalone Function
  • Changing Standalone Subprograms
  • Tutorial: Testing a Standalone Function
  • Dropping Standalone Subprograms
• Creating and Managing Packages
  • About Package Structure
  • Tutorial: Creating a Package Specification
  • Tutorial: Changing a Package Specification
  • Tutorial: Creating a Package Body
  • Dropping a Package
• Declaring and Assigning Values to Variables and Constants
  • Tutorial: Declaring Variables and Constants in a Subprogram
  • Ensuring that Variables, Constants, and Parameters Have Correct Data Types
  • Tutorial: Changing Declarations to Use the %TYPE Attribute
  • Assigning Values to Variables
    • Assigning Values to Variables with the Assignment Operator
    • Assigning Values to Variables with the SELECT INTO Statement
• Controlling Program Flow
  • About Control Statements
  • Using the IF Statement
  • Using the CASE Statement
  • Using the FOR LOOP Statement
  • Using the WHILE LOOP Statement
  • Using the Basic LOOP and EXIT WHEN Statements
• Using Records and Cursors
  • About Records
  • Tutorial: Declaring a RECORD Type
  • Tutorial: Creating and Invoking a Subprogram with a Record Parameter
  • About Cursors
  • Using a Declared Cursor to Retrieve Result Set Rows One at a Time
  • Tutorial: Using a Declared Cursor to Retrieve Result Set Rows One at a Time
  • About Cursor Variables
  • Using a Cursor Variable to Retrieve Result Set Rows One at a Time
  • Tutorial: Using a Cursor Variable to Retrieve Result Set Rows One at a Time
• Using Associative Arrays
  • About Collections
  • About Associative Arrays
  • Declaring Associative Arrays
  • Populating Associative Arrays
  • Traversing Dense Associative Arrays
  • Traversing Sparse Associative Arrays
• Handling Exceptions (Runtime Errors)
  • About Exceptions and Exception Handlers
  • When to Use Exception Handlers
  • Handling Predefined Exceptions
  • Declaring and Handling User-Defined Exceptions

6 Using Triggers

• About Triggers
• Creating Triggers
  • About OLD and NEW Pseudorecords
  • Tutorial: Creating a Trigger that Logs Table Changes
  • Tutorial: Creating a Trigger that Generates a Primary Key for a Row Before It Is Inserted
  • Creating an INSTEAD OF Trigger
  • Tutorial: Creating Triggers that Log LOGON and LOGOFF Events
• Changing Triggers
• Disabling and Enabling Triggers
  • Disabling or Enabling a Single Trigger
  • Disabling or Enabling All Triggers on a Single Table
• About Trigger Compilation and Dependencies
• Dropping Triggers

7 Working in a Global Environment

• About Globalization Support Features
  • About Language Support
  • About Territory Support
  • About Date and Time Formats
  • About Calendar Formats
  • About Numeric and Monetary Formats
  • About Linguistic Sorting and String Searching
  • About Length Semantics
  • About Unicode and SQL National Character Data Types
• About Initial NLS Parameter Values
• Viewing NLS Parameter Values
• Changing NLS Parameter Values
  • Changing NLS Parameter Values for All SQL Developer Connections
  • Changing NLS Parameter Values for the Current SQL Function Invocation
• About Individual NLS Parameters
  • About Locale and the NLS_LANG Parameter
  • About the NLS_LANGUAGE Parameter
  • About the NLS_TERRITORY Parameter
  • About the NLS_DATE_FORMAT Parameter
  • About the NLS_DATE_LANGUAGE Parameter
  • About NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT Parameters
  • About the NLS_CALENDAR Parameter
  • About the NLS_NUMERIC_CHARACTERS Parameter
  • About the NLS_CURRENCY Parameter
  • About the NLS_ISO_CURRENCY Parameter
  • About the NLS_DUAL_CURRENCY Parameter
  • About the NLS_SORT Parameter
  • About the NLS_COMP Parameter
  • About the NLS_LENGTH_SEMANTICS Parameter
• Using Unicode in Globalized Applications
  • Representing Unicode String Literals in SQL and PL/SQL
  • Avoiding Data Loss During Character-Set Conversion

8 Building Effective Applications

• Building Scalable Applications
  • About Scalable Applications
  • Using Bind Arguments to Improve Scalability
  • Using PL/SQL to Improve Scalability
    • How PL/SQL Minimizes Parsing
    • About the EXECUTE IMMEDIATE Statement
    • About OPEN FOR Statements
    • About the DBMS_SQL Package
    • About Bulk SQL
  • About Concurrency and Scalability
    • About Sequences and Concurrency
    • About Latches and Concurrency
    • About Nonblocking Reads and Writes and Concurrency
    • About Shared SQL and Concurrency
  • Limiting the Number of Concurrent Sessions
  • Comparing Programming Techniques with Runstats
    • About Runstats
    • Setting Up Runstats
    • Using Runstats
• Recommended Programming Practices
  • Use Instrumentation Packages
  • Statistics Gathering and Application Tracing
  • Use Existing Functionality
  • Cover Database Tables with Editioning Views
• Recommended Security Practices

9 Developing a Simple Oracle Database Application

• About the Application
  • Purpose of the Application
  • Structure of the Application
    • Schema Objects of the Application
    • Schemas for the Application
  • Naming Conventions in the Application
• Creating the Schemas for the Application
• Granting Privileges to the Schemas
  • Granting Privileges to the app_data Schema
  • Granting Privileges to the app_code Schema
  • Granting Privileges to the app_admin Schema
  • Granting Privileges to the app_user and app_admin_user Schemas
• Creating the Schema Objects and Loading the Data
  • Creating the Tables
  • Creating the Editioning Views
  • Creating the Triggers
    • Creating the Trigger to Enforce the First Business Rule
    • Creating the Trigger to Enforce the Second Business Rule
  • Creating the Sequences
  • Loading the Data
  • Adding the Foreign Key Constraint
  • Granting Privileges on the Schema Objects to Users
• Creating the employees_pkg Package
  • Creating the Package Specification for employees_pkg
  • Creating the Package Body for employees_pkg
  • Tutorial: Showing How the employees_pkg Subprograms Work
  • Granting the Execute Privilege to app_user and app_user_admin
  • Tutorial: Invoking get_job_history as app_user or app_admin_user
• Creating the admin_pkg Package
  • Creating the Package Specification for admin_pkg
  • Creating the Package Body for admin_pkg
  • Tutorial: Showing How the admin_pkg Subprograms Work
  • Granting the Execute Privilege to app_user_admin
  • Tutorial: Invoking add_department as app_admin_user

10 Deploying an Oracle Database Application

• About Development and Deployment Environments
• About Installation Scripts
  • About DDL Statements and Schema Object Dependencies
  • About INSERT Statements and Constraints
• Creating Installation Scripts
  • Creating Installation Scripts with the Cart
  • Creating an Installation Script with the Database Export Wizard
  • Editing Installation Scripts that Create Sequences
  • Editing Installation Scripts that Create Triggers
  • Creating Installation Scripts for the Sample Application
    • Creating Installation Script schemas.sql
    • Creating Installation Script objects.sql
    • Creating Installation Script employees.sql
    • Creating Installation Script admin.sql
    • Creating Master Installation Script create_app.sql
• Deploying the Sample Application
• Checking the Validity of an Installation
• Archiving the Installation Scripts

Index