Oracle® Database Express Edition 2 Day Developer Guide 10g Release 2 (10.2) Part Number B25108-01 |
|
|
View PDF |
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 developmentThis section introduces you to developing applications with Oracle Database Express Edition.
This section contains the following topics:
See Also:
Oracle Database Express Edition Getting Started Guide for a short tutorial to get you quickly up and running using Oracle Database XE
Oracle Database Express Edition 2 Day DBA for information about getting started with Oracle Database XE
Oracle Database XE home page on Oracle Technology Network:
Oracle Database XE Documentation Library:
Oracle Database XE Discussion forum:
http://www.oracle.com/technology/products/database/xe/forum.html
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:
Oracle Database Sample Schemas for a description of the HR
sample schema
Oracle Database Concepts for an introduction to Oracle databases
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 SQLPL/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:
Oracle Database PL/SQL User's Guide and Reference for information about PL/SQL
Oracle Database PL/SQL Packages and Types Reference for information about PL/SQL packages that are supplied with Oracle Database XE
http://www.oracle.com/technology/tech/pl_sql/index.html
for additional PL/SQL information and code samples for on the Oracle Technology Network (OTN)
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".
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.
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".
To log in to the Database Home Page:
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".
Note:
If theHR
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.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.
See Also:
Oracle Database Express Edition Getting Started Guide for a short tutorial to get you quickly up and running using Oracle Database XE
Oracle Database Express Edition 2 Day DBA for information about getting started with Oracle Database Express Edition
Oracle Database Express Edition Application Express User's Guide for a description of the icons on Database Home Page.
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".
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:
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:
Oracle Call Interface Programmer's Guide for more information about OCI
Oracle C++ Call Interface Programmer's Guide for more information about OCCI
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 http://www.unixODBC.org
.
See Also:
Oracle Services for Microsoft Transaction Server Developer's Guide for information about how to use the Oracle ODBC driver on Windows
Oracle Database Administrator's Reference 10g Release 2 (10.2) for UNIX-Based Operating Systems for information about how to use the Oracle ODBC driver on Linux
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 DBOracle 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 .NETOracle 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 .NETOracle 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 .NETOracle 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 Database Express Edition 2 Day Plus Application Express Developer Guide for tutorials with step-by-step instructions that explain how to create a variety of application components and complete applications using Oracle Application Express
Oracle Database Express Edition Application Express User's Guide for information about building database-centric Web applications using the Oracle Application Express
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:
http://java.sun.com/products/jdbc
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:
http://www.oracle.com/technology/tech/java/sqlj_jdbc/index.html
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 XEPHP 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:
Oracle Database Express Edition 2 Day Plus PHP Developer Guide for information about application development using Zend Core for Oracle and Oracle Database XE
PHP Development Center at