The main use of the Oracle database system is to store and retrieve data for applications. The features of the Oracle database and related products described in this section are used to create applications. We''ve divided the discussion in this section into two categories: database programming and database extensibility options. Later in this chapter, we describe the Oracle Developer Suite, a set of optional tools used in Oracle Database Server and Oracle Application Server development.
All flavors of the Oracle database include different languages and interfaces that allow programmers to access and manipulate the data in the database. Database programming features usually interest two groups: developers building Oracle-based applications that will be sold commercially, and IT organizations within companies that custom-develop applications unique to their businesses. The following sections describe the languages and interfaces supported by Oracle.
The ANSI standard Structured Query Language (SQL) provides basic functions for data manipulation, transaction control, and record retrieval from the database. However, most end users interact with Oracle through applications that provide an interface that hides the underlying SQL and its complexity.
Oracle''s PL/SQL, a procedural language extension to SQL, is commonly used to implement program logic modules for applications. PL/SQL can be used to build stored procedures and triggers, looping controls, conditional statements, and error handling. You can compile and store PL/SQL procedures in the database. You can also execute PL/SQL blocks via SQL*Plus, an interactive tool provided with all versions of Oracle. Oracle Database 10g includes a more optimized version of the core PL/SQL engine, as Oracle9i allowed creation and storage of precompiled PL/SQL program units.
Oracle8i introduced the use of Java as a procedural language with a Java Virtual Machine (JVM) in the database (originally called JServer). JVM includes support for Java stored procedures, methods, triggers, Enterprise JavaBeans™ (EJBs), CORBA, and HTTP. The Accelerator is used for project generation, translation, and compilation, and can also be used to deploy/install shared libraries.
The inclusion of Java within the Oracle database allows Java developers to leverage their skills as Oracle application developers. Java applications can be deployed in the client, Application Server, or database, depending on what is most appropriate. We discuss Java development in Chapter 13 and Chapter 14.
Oracle data warehousing options for OLAP and data mining provide a Java API. These applications are typically custom built using Oracle''s JDeveloper.
Interest in the use of large objects (LOBs) continues to grow, particularly for storing nontraditional datatypes such as images. The Oracle database has been able to store large objects for some time. Oracle8 added the capability to store multiple LOB columns in each table. Oracle Database 10g essentially removes the space limitation on large objects.
Support of object structures has been included since Oracle8i to allow an object-oriented approach to programming. For example, programmers can create user-defined datatypes, complete with their own methods and attributes. Oracle''s object support includes a feature called Object Views through which object-oriented programs can make use of relational data already stored in the database. You can also store objects in the database as varying arrays (VARRAYs), nested tables, or index organized tables (IOTs). We discuss the object-oriented features of Oracle further in Chapter 13.
Programmers can interact with the Oracle database from C, C++, Java, COBOL, or FORTRAN applications by embedding SQL in those applications. Prior to compiling the applications using a platform''s native compilers, you must run the embedded SQL code through a precompiler. The precompiler replaces SQL statements with library calls the native compiler can accept. Oracle provides support for this capability through optional "programmer" precompilers for languages such as C and C++ (Pro*C) and COBOL (Pro*COBOL). More recently, Oracle added SQLJ, a precompiler for Java that replaces SQL statements embedded in Java with calls to a SQLJ runtime library, also written in Java.
All versions of Oracle include database drivers that allow applications to access Oracle via ODBC (the Open DataBase Connectivity standard) or JDBC (the Java DataBase Connectivity open standard). Also available are data providers for OLE DB and for .NET.
If you''re an experienced programmer seeking optimum performance, you may choose to define SQL statements within host-language character strings and then explicitly parse the statements, bind variables for them, and execute them using the Oracle Call Interface (OCI).
OCI is a much more detailed interface that requires more programmer time and effort to create and debug. Developing an application that uses OCI can be time-consuming, but the added functionality and incremental performance gains often make spending the extra time worthwhile.
Why Use OCI?Why would someone want to use OCI instead of the higher-level interfaces? In certain programming scenarios, OCI improves application performance or adds functionality. For instance, in high-availability implementations in which multiple systems share disks and implement Real Application Clusters/Oracle Parallel Server, you may want users to reattach to a second server transparently if the first fails. You can write programs that do this using OCI.
|
National Language Support (NLS) provides character sets and associated functionality, such as date and numeric formats, for a variety of languages. Oracle9i featured full Unicode 3.0 support. All data may be stored as Unicode, or select columns may be incrementally stored as Unicode. UTF-8 encoding and UTF-16 encoding provide support for more than 57 languages and 200 character sets. Oracle Database 10g adds support for Unicode 3.2. Extensive localization is provided (for example, for data formats) and customized localization can be added through the Oracle Locale Builder. Oracle Database 10g includes a Globalization Toolkit for creating applications that will be used in multiple languages.
All of these database programming features are included in both Oracle Standard Edition and Oracle Enterprise Edition.
The Internet and corporate intranets have created a growing demand for storage and manipulation of nontraditional datatypes within the database. There is a need for extensions to the standard functionality of a database for storing and manipulating image, audio, video, spatial, and time series information. These capabilities are enabled through extensions to standard SQL.
For more details regarding these features of Oracle, see Chapter 13.
Oracle Text includes what was previously referred to as the "ConText cartridge" with Ultrasearch capabilities. It can identify the gist of a document by searching for themes and key phrases in the document.
Oracle interMedia bundles additional image, audio, video, and locator functions and is included in the database license. Oracle interMedia offers the following capabilities:
The image portion of interMedia can store and retrieve images.
The audio and video portions of interMedia can store and retrieve audio and video clips, respectively.
The locator portion of interMedia can retrieve data that includes spatial coordinate information.
The Spatial option is available for Oracle Enterprise Edition. It can optimize the display and retrieval of data linked to coordinates and is used in the development of spatial information systems. Several vendors of Geographic Information Systems (GIS) products now bundle this option and leverage it as their search and retrieval engine.
Oracle added native XML datatype support to the Oracle9i database and XML and SQL interchangeability for searching. The structured XML object is held natively in object relational storage meeting the W3C DOM specification. The XPath syntax for searching in SQL is based on the SQLX group specifications.