Contents

List of Examples

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

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

• 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

3 Exploring Oracle Database with SQL Developer

• Tutorial: Viewing HR Schema Objects
• Tutorial: Viewing EMPLOYEES Table Properties and Data

4 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
• Specifying Conditions with Regular Expressions
• 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

5 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

6 Creating and Managing Schema Objects

• About Data Definition Language (DDL) Statements
• About Schema Object Names
• 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
  • Tutorial: Changing Views with the Edit View and Rename Tools
  • Dropping Views
• Creating and Managing Sequences
  • Tutorial: Creating a Sequence
  • Dropping Sequences
• Creating and Managing Synonyms
  • Creating Synonyms
  • Dropping Synonyms

7 Developing Stored Subprograms and Packages

• About Stored Subprograms
• About Packages
• About PL/SQL Identifiers
• About PL/SQL Data Types
• Creating and Managing Standalone Stored Subprograms
  • About Subprogram Structure
  • Tutorial: Creating a Standalone Stored Procedure
  • Tutorial: Creating a Standalone Stored Function
  • Changing Standalone Stored Subprograms
  • Tutorial: Testing a Standalone Stored Function
  • Dropping Standalone Stored 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 an Explicit Cursor to Retrieve Result Set Rows One at a Time
  • Tutorial: Using an Explicit 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 (Run-Time Errors)
  • About Exceptions and Exception Handlers
  • Handling Predefined Exceptions
  • Declaring and Handling User-Defined Exceptions

8 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
  • Creating Triggers that Log LOGON and LOGOFF Events
• Changing Triggers
• Disabling and Enabling Triggers
• About Trigger Compilation and Dependencies
• Dropping Triggers

9 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

10 Deploying an Oracle Database Application

• About Deployment Environments
• About Installation Script Files
  • About DDL Statements and Schema Object Dependencies
  • About INSERT Statements and Constraints
• Creating Installation Script Files
  • Creating an Installation Script File with SQL Developer
  • Editing Installation Script Files that Create Sequences
  • Editing Installation Script Files that Create Triggers
  • Tutorial: Creating an Installation Script File for the Sequence and Tables
  • Tutorial: Creating an Installation Script File for the Package
  • Tutorial: Creating an Installation Script File for the Synonym and View
  • Tutorial: Creating an Installation Script File for the Data
• Installing the Sample Application
• Checking the Validity of an Installation
• Archiving the Installation Script Files

Index