Applications are often built on the foundation of the Oracle database system. 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 Internet Developer Suite, a set of optional tools used in Oracle9i Database Server and Oracle9i 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.
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, IIOP, and HTTP. The Accelerator is used for project generation, translation, and compilation. As of Oracle Version 8.1.7, it 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 applications developers. Java applications can be deployed in the client, Oracle9i Application Server, or database, depending on what is most appropriate. We discuss Java development in Chapter 13 and Chapter 14.
Interest in the use of large objects (LOBs) is growing, particularly for the storage of 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.
Support of object structures has been included since Oracle8i to provide support for 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).
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.
National Language Support (NLS) provides character sets and associated functionality, such as date and numeric formats, for a variety of languages. Oracle9i features 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. Extensive localization is provided (for example, for data formats) and customized localization can be added through the Oracle Locale Builder.
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. Oracle8 provides extensibility to the database through options sometimes referred to as cartridges. These options are simply extensions to standard SQL, usually built by Oracle or its partners through C, PL/SQL, or Java. You may find these options helpful if you’re working extensively with the type of data they’re designed to handle.
For more details regarding these features of Oracle, see Chapter 13.
Oracle interMedia bundles what was formerly referred to as the “Context cartridge” for text manipulation with additional image, audio, video, and locator functions and is included in the database license. interMedia offers the following major capabilities:
The text portion of interMedia (Oracle9i’s Oracle Text) can identify the gist of a document by searching for themes and key phrases within the document.
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.
Get Oracle Essentials: Oracle9i, Oracle8i and Oracle8, Second Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
