Skip Headers
Oracle® Database Express Edition 2 Day Developer Guide
10
g
Release 2 (10.2)
Part Number B25108-01
Home
Book List
Index
Master Index
Contact Us
Next
View PDF
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