Skip Headers
Oracle® Database Express Edition 2 Day Developer Guide
10g Release 2 (10.2)

Part Number B25108-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

1 Overview of Development

This section provides an overview of developing applications with Oracle Database Express Edition (Oracle Database XE).

This section contains the following topics:

See Also:

Oracle Database Application Developer's Guide - Fundamentals for a complete overview of application development

Overview of Developing Applications With Oracle Database XE

This section introduces you to developing applications with Oracle Database Express Edition.

This section contains the following topics:

See Also:

Oracle Database Express Edition

Oracle Database Express Edition is a relational database that stores and retrieves collections of related information. A database, also called a database server, is the key to solving the problems of information management. In a relational database, collections of related information are organized into structures called tables. Each table contains rows (records) that are composed of columns (fields). The tables are stored in the database in structures called schemas, which are logical structures of data where database users store their tables.

The HR sample schema that is included with Oracle Database XE is an example of a schema with related tables. In the HR sample schema, there are tables to store information about employees and departments. The tables contain common columns that allow data from one table to be related to another. For each employee in the employees table, the department name in the departments table can be retrieved based on the department ID column that is in both tables. See "Sample HR Account".

See Also:


Structured Query Language (SQL) is a nonprocedural programming language that enables you to access a relational database. Using SQL statements, you can query tables to display data, create objects, modify objects, and perform administrative tasks. All you need to do is describe in SQL what you want done, and the SQL language compiler automatically generates a procedure to navigate the database and perform the desired task.

For information about using SQL, see Chapter 3, "Using SQL".

See Also:

Oracle Database SQL Reference for information about SQL


PL/SQL is an Oracle procedural extension to SQL, the standard database access language. It is an advanced programming language, which like SQL, has a built-in treatment of the relational database domain. Applications written in any of the Oracle programmatic interfaces can call stored procedures and functions written in PL/SQL.

In PL/SQL, you can manipulate data with SQL statements and control program flow with procedural constructs such as loops. You can also do the following:

  • Declare constants and variables

  • Define procedures and functions

  • Use collections and object types

  • Trap runtime errors

  • Create functions, packages, procedures, and triggers

For information about using PL/SQL, see Chapter 4, "Using PL/SQL".

See Also:

Database Objects

You need to create database objects before you start developing your application. These database objects are primarily used to store and organize the data that your application manipulates. These databases objects include tables, indexes, views, sequences and synonyms.

When creating some database objects, you need to specify a datatype for the data that is used by the object. When you create a table, you must specify a datatype for each of its columns. A datatype associates a fixed set of properties with the values that can be used in a column, or in an argument of a procedure or function. Each column value and constant in a SQL statement has a datatype, which is associated with a specific storage format, constraints, and a valid range of values. The most common datatypes are character, numeric, and date.

For information about managing database objects, see Chapter 2, "Managing Database Objects".

Basic Application Development Concepts

This section discusses the basic concepts in application development with Oracle Database Express Edition.

  • User interface

    The interface that your application displays to end users depends on the technology behind the application as well as the needs of the users themselves. The Oracle Database XE browser-based user interface is an example of an application interface. See "Development Tools".

  • Client/server model

    In a traditional client/server program, your application code runs on a different machine than where the database (server) is located. Database calls are transmitted from this client machine to a database, such as Oracle Database XE. Data is transmitted from the client to the server for insert and update operations and returned from the server to the client for query operations where the data is processed on the client machine.

  • Server-side coding

    You can develop application logic that resides entirely inside the database by using PL/SQL triggers that execute automatically when changes occur in the database or stored PL/SQL procedures or functions that are called explicitly. Off-loading the work from your application lets you reuse code that performs verification and cleanup and control database operations from a variety of clients.

Development Tools

There are various tools that you can use to develop with SQL and PL/SQL, and to manage database objects.

Oracle Database XE has a browser-based user interface for administering database objects, running SQL statements, PL/SQL code, building Web-based applications, and more. The starting point for this interface is the Database Home Page. See "Logging in to the Database Home Page".

You can also use SQL Command Line (SQL*Plus) to enter SQL statements and PL/SQL code. To use SQL Command Line, see Appendix A, "Using SQL Command Line".

Logging in to the Database Home Page

To log in to the Database Home Page:

  1. Access the Database Home Page.

    The page can be accessed from your graphical desktop or pointing your Web browser to a specific URL. See "Accessing the Database Home Page" in Oracle Database Express Edition 2 Day DBA.

    To view the database objects or run the examples discussed in this guide, log in to the Database Home Page as the user HR. In the Username field enter HR and in the Password field enter your password for the HR user account, then click the Login button. See "Sample HR Account".


    If the HR user account is locked, you need to log in as a user with administrator privileges and unlock the account. When unlocking the account, ensure the HR user has both CONNECT and RESOURCE roles enabled. See "Locking and Unlocking User Accounts" in Oracle Database Express Edition 2 Day DBA.
  2. On the Database Home Page, click the icon for the specific tool that you want to use. There are icons for Administration, Application Builder, Object Browser, SQL, and Utilities when you log in as the HR user.

    The Database Home Page includes links to the License Agreement, Getting Started, Learn More, Documentation, Forum Registration, Discussion Forum, and Product Page. The Usage Monitor on the page provides information about the storage and memory use, number of sessions and users, and log archiving status.

    Description of xe_home_page.gif follows
    Description of the illustration xe_home_page.gif

See Also:

Sample HR Account

Oracle Database XE provides the HR sample user account for use with the examples in this guide. This HR user account is also referred to as the HR schema. A schema is a logical container for the database objects that the user creates.

The HR sample account is set up to be a simple Human Resources division for tracking information about the employees and the facilities. In the HR schema, each employee has an identification number, e-mail address, job identification code, salary, and manager. Some employees earn commissions in addition to their salary. Each job has an identification code that associates it with a job title, a minimum salary, and a maximum salary for the job.

Each employee is assigned to a department, and each department is identified either by a unique department number or a short name. Each department is associated with one location, and each location has a full address that includes the street name, postal code, city, state or province, and the country code.

For information about viewing the database objects in the HR schema, including the structure of the HR tables, see "Managing Database Objects With Object Browser".

See Also:

Oracle Database Sample Schemas for information about the HR sample schema

Other Development Environments

This section lists other development languages that can be used with Oracle Database Express Edition. These environments are discussed in other guides.

This section contains the following topics:

Oracle Call Interface and Oracle C++ Call Interface

The Oracle Call Interface (OCI) and Oracle C++ Call Interface (OCCI) are native C and C++ APIs for accessing Oracle Database Express Edition from C and C++ applications.

See Also:

Open Database Connectivity

Open Database Connectivity (ODBC) is a database access API that enables you to connect to a database and then prepare and run SQL statements against Oracle Database XE. In conjunction with an ODBC driver, an application can access any data source including data stored in spreadsheets, such as an Excel spreadsheet.

An Oracle ODBC driver is provided for both Windows and Linux (32 bit). The Oracle ODBC Driver conforms to ODBC 3.51 specifications. It supports all core APIs and a set of Level 1 and Level 2 functions. For Windows, the Driver Manager component is supplied by Microsoft. For Unix platforms, the Oracle Database XE driver has been tested using the latest Driver Manager available from

See Also:

Oracle Provider for OLE DB

OLE DB is an open standard data access methodology that uses a set of Component Object Model (COM) interfaces to access and manipulate different types of data. These interfaces are available from various database providers.

Oracle Provider for OLE DB (OraOLEDB) is an OLE DB data provider that offers high performance and efficient access to Oracle data by OLE DB consumers.

With the advent of the .NET framework, support is provided for using the OLEDB.NET Data Provider with OraOLEDB. With the correct connection attribute setting, an OLEDB.NET Data Provider can utilize OraOLEDB to access Oracle Database.

See Also:

Oracle Provider for OLE DB Developer's Guide for information about developing applications to access Oracle Database XE using Oracle Provider for OLE DB

Oracle Data Provider for .NET

Oracle Data Provider for .NET (ODP.NET) provides data access for client applications from within Oracle Database XE. Oracle Data Provider for .NET is an implementation of a .NET data provider for Oracle Database, using and inheriting from classes and interfaces available in the Microsoft .NET Framework Class Library.

Following the .NET Framework, ODP.NET uses the ADO.NET model, which enables native providers to expose provider-specific features and datatypes. This is similar to Oracle Provider for OLE DB, where ActiveX Data Objects (ADO) provides an automation layer that exposes a programming model. ADO.NET provides a similar programming model, but without the automation layer for better performance. ODP.NET uses Oracle native APIs to access Oracle data and features from any .NET application.

See Also:

Oracle Database Express Edition 2 Day Plus .NET Developer Guide for information about application development with Oracle Database XE in Microsoft .NET

Oracle Database Extensions for .NET

Oracle Database Extensions for .NET provides the following:

  • A Common Language Runtime (CLR) host for Oracle Database XE

  • Data access through Oracle Data Provider for .NET classes

  • Oracle Deployment Wizard for Visual Studio .NET

Oracle Database XE hosts Microsoft Common Language Runtime (CLR) in an external process, outside of the Oracle database process, but on the same computer. The integration of Oracle Database XE with the Microsoft Common Language Runtime (CLR) enables applications to run .NET stored procedures or functions on Oracle Database XE, Microsoft Windows 2003, Windows 2000, and Windows XP.

Stored procedures and functions can be written using any .NET compliant language, such as C# and VB.NET. These .NET stored procedures can be used in the same manner as other PL/SQL or Java stored procedures, and can be called from PL/SQL packages, procedures, functions, and triggers.

.NET procedures or functions are built into a .NET assembly, typically using Microsoft Visual Studio .NET. Oracle Data Provider for .NET is used in .NET stored procedures and functions to access data. After building .NET procedures and functions into a .NET assembly, they can be deployed in Oracle Database XE, using the Oracle Deployment Wizard for .NET, a component of the Oracle Developer Tools for Visual Studio .NET.

See Also:

Oracle Database Express Edition 2 Day Plus .NET Developer Guide for information about application development with Oracle Database XE in Microsoft .NET

Oracle Developer Tools for Visual Studio .NET

Oracle Developer Tools is an add-on to Visual Studio .NET that provides graphical user interface (GUI) access to Oracle functionality.

Oracle Developer Tools include Oracle Explorer to browse your Oracle schema, designers and wizards to create and alter schema objects, and the ability to drag and drop schema objects onto your .NET form to automatically generate code. There is also a PL/SQL editor with integrated context-sensitive online Help. With Oracle Data Window, you can perform routine database tasks, such as inserting and updating Oracle data or testing stored procedures in the Visual Studio environment. For maximum flexibility, there is also a SQL Query Window for executing any SQL statement or SQL script.

See Also:

Oracle Database Express Edition 2 Day Plus .NET Developer Guide for information about application development with Oracle Database XE in Microsoft .NET

Oracle Application Express

Oracle Application Express is a Web-based application development and deployment tool integrated with Oracle Database Express Edition. Oracle Application Express enables users with only a Web browser and limited programming experience to quickly create secure and scalable Web applications that can be instantly deployed to tens, hundreds, or thousands of users. The Application Builder tool assembles an HTML interface (or application) on top of database objects such as tables and procedures. Each application is a collection of pages linked together using tabs, buttons, or hypertext links.

See Also:

Oracle Java Database Connectivity (JDBC)

Oracle Java Database Connectivity (JDBC) is an API that enables Java to send SQL statements to an object-relational database such as Oracle Database XE. For more information about the JDBC API see:

Oracle Database JDBC brings provides features, such as complete support for JDBC 3.0 standard, complete support for JDBC RowSet (JSR-114), Advanced Connection Caching (non-XA and XA connections), exposing SQL and PL/SQL data types to Java, and faster SQL data access. For information about the new JDBC features, see:

For more information about the Oracle JDBC Drivers, see:

For an example of the use of PL/SQL with JDBC, see Appendix D, "Using a PL/SQL Procedure With JDBC".

See Also:

Oracle Database Express Edition 2 Day Plus Java Developer Guide for information about using Java to access and modify data in Oracle Database XE


PHP is a recursive acronym for PHP Hypertext Preprocessor. It is a widely-used, open-source, interpretive, HTML-centric, server-side scripting language. PHP is especially suited for Web development and can be embedded into HTML pages. PHP is comparable to languages such as Java Server Pages (JSP) and Oracle PL/SQL Server Pages (PSP). Zend Core for Oracle, developed in partnership with Zend Technologies, enables application development using PHP with Oracle Database XE.

For an example of the use of PL/SQL with PHP, see Appendix C, "Using a PL/SQL Procedure With PHP".

See Also: