Contents

List of Examples

Title and Copyright Information

Preface

• Documentation Topics
• Audience
• Documentation Accessibility
• Related Documentation
• Conventions

1 Overview of Development

• Overview of Developing Applications With Oracle Database XE
  • Oracle Database Express Edition
  • SQL
  • PL/SQL
  • Database Objects
  • Basic Application Development Concepts
  • Development Tools
    • Logging in to the Database Home Page
  • Sample HR Account
• Other Development Environments
  • Oracle Call Interface and Oracle C++ Call Interface
  • Open Database Connectivity
  • Oracle Provider for OLE DB
  • Oracle Data Provider for .NET
  • Oracle Database Extensions for .NET
  • Oracle Developer Tools for Visual Studio .NET
  • Oracle Application Express
  • Oracle Java Database Connectivity (JDBC)
  • PHP

2 Managing Database Objects

• Overview of Managing Objects
  • Database Objects for Your Application
  • Managing Database Objects With Object Browser
  • Viewing Data in Tables With Object Browser
  • Viewing Information With Object Reports
• Using Datatypes
  • Storing Character Data
    • What Are the Character Datatypes?
    • Choosing Between the Character Datatypes
  • Storing Numeric Data
    • What Are the Numeric Datatypes?
    • Using the NUMBER Datatype
    • Using Floating-Point Number Formats
  • Storing Date and Time Data
    • Using DATE and TIMESTAMP Datatypes
  • Storing Large Objects
• Managing Tables
  • Ensuring Data Integrity in Tables With Constraints
    • Column Default Value
    • NOT NULL Constraint
    • Check Constraint
    • Unique Constraint
    • Primary Key Constraint
    • Foreign Key Constraint
  • Creating a Table
  • Adding a Column To a Table
  • Modifying a Column In a Table
  • Dropping a Column From a Table
  • Adding a Check Constraint
  • Adding a Unique Constraint
  • Adding a Primary Key Constraint
  • Adding a Foreign Key Constraint
  • Viewing Existing Constraints
  • Disabling and Enabling a Constraint
  • Dropping a Constraint
  • Adding Data to a Table
  • Modifying Data in a Table
  • Removing a Row in a Table
  • Dropping a Table
• Managing Indexes
  • Index Types
  • Indexes for Use with Constraints
  • Guidelines for Creating Indexes
    • Index the Correct Tables and Columns
    • Limit the Number of Indexes for Each Table
    • Drop Indexes That Are No Longer Required
  • Creating an Index
  • Displaying an Index for a Table
  • Dropping an Index
• Managing Views
  • Creating a View
  • Displaying a View
  • Dropping a View
• Managing Sequences
  • Creating a Sequence
  • Displaying a Sequence
  • Dropping a Sequence
• Managing Synonyms
  • Creating a Synonym
  • Displaying a Synonym
  • Dropping a Synonym

3 Using SQL

• Overview of SQL
• Running SQL Statements
  • Running SQL Statements on the SQL Commands Page
  • Running SQL Statements in the Script Editor Page
• Retrieving Data With Queries
  • Displaying Data Using the SELECT Statement
  • Using a Column Alias to Change Headings When Selecting Data
  • Restricting Data Using the WHERE Clause
    • Using Character Literals in SQL Statements
  • Using Regular Expressions When Selecting Data
  • Sorting Data Using the ORDER BY Clause
  • Displaying Data From Multiple Tables
  • Using Bind Variables With the SQL Commands Page
• Using Pseudocolumns, Sequences, and SQL Functions
  • Using ROWNUM, SYSDATE, and USER Pseudocolumns With SQL
  • Using Arithmetic Operators
  • Using Numeric Functions
  • Using Character Functions
  • Using Date Functions
  • Using Conversion Functions
  • Using Aggregate Functions
  • Using NULL Value Functions
  • Using Conditional Functions
• Manipulating Data With SQL Statements
  • Adding Data With the INSERT Statement
  • Updating Data With the UPDATE Statement
  • Deleting Data With the DELETE Statement
• Using Transaction Control Statements
  • Committing Transaction Changes
  • Rolling Back a Transaction
• Using Data Definition Language Statements to Manage Database Objects
  • Creating a Table With SQL
  • Adding, Altering, and Dropping a Table Column With SQL
  • Creating and Altering a Constraint With SQL
  • Renaming a Table With SQL
  • Dropping a Table With SQL
  • Creating, Altering, and Dropping an Index With SQL
  • Creating and Dropping a View With SQL
  • Creating and Dropping a Sequence With SQL
  • Creating and Dropping a Synonym With SQL

4 Using PL/SQL

• Overview of PL/SQL
• Entering and Running PL/SQL Code
  • Running PL/SQL Code in the SQL Commands Page
• Using the Main Features of PL/SQL
  • Using the PL/SQL Block Structure
  • Inputting and Outputting Data with PL/SQL
  • Using Comments
  • Declaring Variables and Constants
  • Using Identifiers in PL/SQL
  • Assigning Values to a Variable With the Assignment Operator
  • Using Literals
  • Declaring Variables With the DEFAULT Keyword or NOT NULL Constraint
  • Assigning Values to a Variable With the PL/SQL SELECT INTO Statement
  • Using %TYPE and %ROWTYPE Attributes to Declare Identical Datatypes
    • Using the %TYPE Attribute to Declare Variables
    • Using the %ROWTYPE Attribute to Declare Variables
  • Using PL/SQL Control Structures
    • Conditional Control With IF-THEN
    • Conditional Control With the CASE Statement
    • Iterative Control With LOOPs
    • Sequential Control With GOTO
  • Using Local PL/SQL Procedures and Functions in PL/SQL Blocks
  • Using Cursors and Cursor Variables To Retrieve Data
    • Explicit Cursors
    • Cursor Variables (REF CURSORs)
    • Cursor Attributes
  • Working With PL/SQL Data Structures
    • Using Record Types
    • Using Collections
  • Using Bind Variables With PL/SQL
  • Using Dynamic SQL in PL/SQL
• Handling PL/SQL Errors
  • Summary of Predefined PL/SQL Exceptions
  • Using the Exception Handler
  • Declaring PL/SQL Exceptions
  • Scope Rules for PL/SQL Exceptions
  • Continuing After an Exception Is Raised

5 Using Procedures, Functions, and Packages

• Overview of Procedures, Functions, and Packages
  • Stored Procedures and Functions
  • Packages
• Managing Stored Procedures and Functions
  • Creating a Procedure or Function With the SQL Commands Page
  • Creating a Procedure or Function With the Object Browser Page
  • Viewing Procedures or Functions With the Object Browser Page
  • Creating Stored Procedures With SQL CREATE PROCEDURE
  • Creating a Stored Procedure That Uses Parameters
  • Creating a Stored Procedure With the AUTHID Clause
  • Creating Stored Functions With the SQL CREATE FUNCTION Statement
  • Calling Stored Procedures or Functions
  • Editing Procedures or Functions
  • Dropping a Procedure or Function
• Managing Packages
  • Writing Packages With PL/SQL Code
    • Guidelines for Writing Packages
  • Creating Packages in the SQL Commands Page
  • Creating Packages With the Object Browser Page
  • Viewing Packages With the Object Browser Page
  • Creating Packages With the SQL CREATE PACKAGE Statement
  • Editing Packages
  • Dropping Packages
  • Calling Procedures and Functions in Packages
  • Accessing Variables in Packages
  • Accessing Types in Packages
• Oracle Provided Packages
  • List of Oracle Database XE Packages
  • Overview of Some Useful Packages
    • DBMS_OUTPUT Package
    • DBMS_RANDOM Package
    • HTP Package
    • UTL_FILE Package

6 Using Triggers

• Overview of Triggers
  • Types of Triggers
  • Naming Triggers
  • When Is a Trigger Fired?
  • Controlling When a Trigger Is Fired
    • Firing Triggers With the BEORE and AFTER Options
    • Firing Triggers With the FOR EACH ROW Option
    • Firing Triggers Based on Conditions (WHEN Clause)
    • Firing Triggers With the INSTEAD OF Option
  • Accessing Column Values in Row Triggers
  • Detecting the DML Operation That Fired a Trigger
  • Enabled and Disabled Trigger Modes
  • Error Conditions and Exceptions in the Trigger Body
• Designing Triggers
  • Guidelines For Triggers
  • Restrictions For Creating Triggers
  • Privileges Needed to Work with Triggers
• Managing Triggers in the Database
  • Creating a Trigger With the SQL Commands Page
  • Creating a Trigger With the Object Browser Page
  • Viewing a Trigger With Object Browser
  • Creating a Trigger With the AFTER and FOR EACH ROW Option
  • Creating a Trigger With the BEFORE Option and WHEN Clause
  • Creating a Trigger With the INSTEAD OF Option
  • Creating a Trigger With an Exception Handler
  • Creating a Trigger That Fires Once For Each Update
  • Creating LOGON and LOGOFF Triggers
  • Modifying Triggers
  • Dropping Triggers
  • Disabling Triggers
  • Enabling Triggers
  • Compiling Triggers
    • Trigger Errors
    • Dependencies for Triggers
    • Recompiling Triggers

7 Working in a Global Environment

• Overview of Globalization Support
  • Globalization Support Features
  • Running the Examples
• Setting Up the Globalization Support Environment
  • Choosing a Locale with the NLS_LANG Environment Variable
  • Setting NLS Parameters
  • Language and Territory Parameters
    • NLS_LANGUAGE Parameter
    • NLS_TERRITORY Parameter
  • Date and Time Parameters
    • Date Formats
    • Time Formats
  • Calendar Definitions
    • Calendar Formats
    • NLS_CALENDAR Parameter
  • Numeric and List Parameters
    • Numeric Formats
    • NLS_NUMERIC_CHARACTERS Parameter
  • Monetary Parameters
    • Currency Formats
    • NLS_CURRENCY Parameter
    • NLS_ISO_CURRENCY Parameter
    • NLS_DUAL_CURRENCY Parameter
  • Linguistic Sorting and Searching
    • NLS_SORT Parameter
    • NLS_COMP Parameter
    • Case-Insensitive and Accent-Insensitive Searching
  • Length Semantics
    • NLS_LENGTH_SEMANTICS Parameter
• SQL and PL/SQL Programming with Unicode
  • Overview of Unicode
  • SQL NCHAR Datatypes
    • NCHAR Datatype
    • NVARCHAR2 Datatype
  • Unicode String Literals
  • NCHAR Literal Replacement
• Locale-Dependent SQL Functions with Optional NLS Parameters
  • Default Values for NLS Parameters in SQL Functions
  • Specifying NLS Parameters in SQL Functions
  • Unacceptable NLS Parameters in SQL Functions

A Using SQL Command Line

• Overview of SQL Command Line
• Using SQL Command Line
  • Starting and Exiting SQL Command Line
  • Displaying Help With SQL Command Line
  • Entering and Executing SQL Statements and Commands
  • SQL Command Line DESCRIBE Command
  • SQL Command Line SET Commands
  • Running Scripts From SQL Command Line
  • Spooling From SQL Command Line
  • Using Variables With SQL Command Line
    • Prompting for a Variable Value in a Query
    • Reusing a Variable Value in a Query
    • Defining a Variable Value for a Query

B Reserved Words

• SQL Reserved Words
• PL/SQL Reserved Words

C Using a PL/SQL Procedure With PHP

• PHP and Oracle Database XE
• Creating a PHP Program That Calls a PL/SQL Stored Procedure

D Using a PL/SQL Procedure With JDBC

• JDBC and Oracle Database XE
• Creating a Java Program That Calls a PL/SQL Procedure

Index