21 Concepts for Database Developers

Client/server programs are typically written using an Oracle precompiler, which is a programming tool that enables you to embed SQL statements in high-level programs. For example, the Oracle Pro*C/C++ precompiler enables you to embed SQL statements in a C or C++ source file. Oracle precompilers are also available for COBOL and FORTRAN.

A precompiler provides several benefits, including the following:

• Increases productivity because you typically write less code than equivalent OCI applications

• Enables you to create highly customized applications

• Allows close monitoring of resource use, SQL statement execution, and various run-time indicators

• Saves time because the precompiler, not you, translates each embedded SQL statement into calls to the Oracle Database run-time library

• Uses the Object Type Translator to map Oracle Database object types and collections into C data types to be used in the Pro*C/C++ application

• Provides compile time type checking of object types and collections and automatic type conversion from database types to C data types

The client application containing the SQL statements is the host program. This program is written in the host language. In the host program, you can mix complete SQL statements with complete C statements and use C variables or structures in SQL statements. When embedding SQL statements you must begin them with the keywords EXEC SQL and end them with a semicolon. Pro*C/C++ translates EXEC SQL statements into calls to the run-time library SQLLIB.

Many embedded SQL statements differ from their interactive counterparts only through the addition of a new clause or the use of program variables. The following example compares interactive and embedded ROLLBACK statements:

ROLLBACK;           -- interactive
EXEC SQL ROLLBACK;  -- embedded

The statements have the same effect, but you would use the first in an interactive SQL environment (such as SQL Developer), and the second in a Pro*C/C++ program.

A precompiler accepts the host program as input, translates the embedded SQL statements into standard database run-time library calls, and generates a source program that you can compile, link, and run in the usual way. Figure 21-2 illustrates typical steps of developing programs that use precompilers.

Figure 21-2 Program Development with Precompilers

Description of "Figure 21-2 Program Development with Precompilers"

SQLJ is an ANSI SQL-1999 standard for embedding SQL statements in Java source code. SQLJ provides a simpler alternative to the Java Database Connectivity (JDBC) API for client-side SQL data access from Java.

The SQLJ interface is the Java equivalent of the Pro* interfaces. You insert SQL statements in your Java source code. Afterward, you submit the Java source files to the SQLJ translator, which translates the embedded SQL to pure JDBC-based Java code.

As an alternative to precompilers, Oracle provides the OCI and OCCI APIs. OCI lets you manipulate data and schemas in a database using a host programming language such as C. OCCI is an object-oriented interface suitable for use with C++. Both APIs enable developers to use native subprogram invocations to access Oracle Database and control SQL execution.

In some cases, OCI provides better performance or more features than higher-level interfaces. OCI and OCCI provide many features, including the following:

• Support for all SQL DDL, DML, query, and transaction control facilities available through Oracle Database

• Instant client, a way to deploy applications when disk space is an issue

• Thread management, connection pooling, globalization functions, and direct path loading of data from a C application

OCI and OCCI provide a library of standard database access and retrieval functions in the form of a dynamic run-time library (OCILIB). This library can be linked in an application at run time. Thus, you can compile and link an OCI or OCCI program in the same way as a nondatabase application, avoiding a separate preprocessing or precompilation step. Figure 21-3 illustrates the development process.

Figure 21-3 Development Process Using OCI or OCCI

Description of "Figure 21-3 Development Process Using OCI or OCCI"

ODBC is a standard API that enables applications to connect to a database and then prepare and run SQL statements. ODBC is independent of programming language, database, and operating system. The goal of ODBC is to enable any application to access data contained in any database.

A database driver is software that sits between an application and the database. The driver translates the API calls made by the application into commands that the database can process. By using an ODBC driver, an application can access any data source, including data stored in spreadsheets. The ODBC driver performs all mappings between the ODBC standard and the database.

The Oracle ODBC driver provided by Oracle enables ODBC-compliant applications to access Oracle Database. For example, an application written in Visual Basic can use ODBC to query and update tables in an Oracle database.

JDBC is a low-level Java interface that enables Java applications to interact with Oracle database. Like ODBC, JDBC is a vendor-independent API. The JDBC standard is defined by Sun Microsystems and implemented through the java.sql interfaces.

The JDBC standard enables individual providers to implement and extend the standard with their own JDBC drivers. Oracle provides the following JDBC drivers for client-side programming:

• JDBC thin driver

  This pure Java driver resides on the client side without an Oracle client installation. It is platform-independent and usable with both applets and applications.

• JDBC OCI driver

  This driver resides on the client-side with an Oracle client installation. It is usable only with applications. The JDBC OCI driver, which is written in both C and Java, converts JDBC calls to OCI calls.

The following snippets are from a Java program that uses the JDBC OCI driver to create a Statement object and query the dual table:

// Create a statement
Statement stmt = conn.createStatement();
 
// Query dual table
ResultSet rset = stmt.executeQuery("SELECT 'Hello World' FROM DUAL");

A key component of globalization support is a character set, which is an encoding scheme used to display characters on a computer screen. The following distinction is important in application development:

• A database character set determines which languages can be represented in a database. The character set is specified at database creation.

  Note:

  After a database is created, changing its character set is usually very expensive in terms of time and resources. This operation may require converting all character data by exporting the whole database and importing it back.

• A client character set is the character set for data entered or displayed by a client application. The character set for the client and database can be different.

A group of characters (for example, alphabetic characters, ideographs, symbols, punctuation marks, and control characters) can be encoded as a character set. An encoded character set assigns a unique numeric code, called a code point or encoded value, to each character in the set. Code points are important in a global environment because of the potential need to convert data between different character sets.

The computer industry uses many encoded character sets. These sets differ in the number of characters available, the characters available for use, code points assigned to each character, and so on. Oracle Database supports most national, international, and vendor-specific encoded character set standards.

Oracle Database supports the following classes of encoded character sets:

• Single-Byte character sets

  Each character occupies one byte. An example of a 7-bit character set is US7ASCII. An example of an 8-bit character set is WE8DEC.

• Multibyte character sets

  Each character occupies multiple bytes. Multibyte sets are commonly used for Asian languages.

• Unicode

  The universal encoded character set enables you to store information in any language by using a single character set. Unicode provides a unique code value for every character, regardless of the platform, program, or language.

A locale is a linguistic and cultural environment in which a system or program is running. NLS parameters determine locale-specific behavior on both the client and database.

A database session uses NLS settings when executing statements on behalf of a client. For example, the database makes the correct territory usage of the thousands separator for a client. Typically, the NLS_LANG environment variable on the client host specifies the locale for both the server session and client application. The process is as follows:

1. When a client application starts, it initializes the client NLS environment from the environment settings.

   All NLS operations performed locally, such as displaying formatting in Oracle Developer applications, use these settings.

2. The client communicates the information defined by NLS_LANG to the database when it connects.

3. The database session initializes its NLS environment based on the settings communicated by the client.

   If the client did not specify settings, then the session uses the settings in the initialization parameter file. The database uses the initialization parameter settings only if the client did not specify any NLS settings. If the client specified some NLS settings, then the remaining NLS settings default.

Each session started on behalf of a client application may run in the same or a different locale as other sessions. For example, one session may use the German locale while another uses the French locale. Also, each session may have the same or different language requirements specified.

The following table shows two clients using different NLS_LANG settings. A user starts SQL*Plus on each host, logs on to the same database as hr, and runs the same query simultaneously. The result for each session differs because of the locale-specific NLS setting for floating-point numbers.

$ NLS_LANG=American_America.US7ASCII
$ export NLS_LANG 

$ NLS_LANG=German_Germany.US7ASCII
$ export NLS_LANG

$ sqlplus /nolog
SQL> CONNECT hr@proddb
Enter password: *******
SQL> SELECT 999/10 FROM DUAL;

999/10 
---------- 
99.9

$ sqlplus /nolog 
SQL> CONNECT hr@proddb 
Enter password: ******* 
SQL> SELECT 999/10 FROM DUAL;   

999/10 
---------- 
99,9

An internal LOB stores data in the database itself rather than in external files. Internal LOBS include the following:

• CLOB (character LOB), which stores large amounts of text, such as text or XML files, in the database character set

• NCLOB (national character set LOB), which stores Unicode data

• BLOB (binary LOB), which stores large amounts of binary information as a bit stream and is not subject to character set translation

The database stores LOBs differently from other data types. Creating a LOB column implicitly creates a LOB segment and a LOB index (see "User Segment Creation"). The tablespace containing the LOB segment and LOB index, which are always stored together, may be different from the tablespace containing the table.

The LOB segment stores data in pieces called chunks. A chunk is a logically contiguous set of data blocks and is the smallest unit of allocation for a LOB. A row in the table stores a pointer called a LOB locator, which points to the LOB index. When the table is queried, the database uses the LOB index to quickly locate the LOB chunks.

The database manages read consistency for LOB segments differently from other data (see "Read Consistency and Undo Segments"). Instead of using undo data to record changes, the database stores the before images in the segment itself. When a transaction changes a LOB, the database allocates a new chunk and leaves the old data in place. If the transaction rolls back, then the database rolls back the changes to the index, which points to the old chunk.

A BFILE (binary file LOB) is an external LOB because the database stores a pointer to a file in the operating system. The external data is read-only.

A BFILE uses a directory object to locate data. The amount of space consumed depends on the length of the directory object name and the length of the file name.

A BFILE does not use the same read consistency mechanism as internal LOBS because the binary file is external to the database. If the data in the file changes, then repeated reads from the same binary file may produce different results.

SecureFiles LOB storage is one of two storage types used with Oracle Database 12c; the other type is BasicFiles LOB storage. The SECUREFILE LOB parameter enables advanced features, including compression and deduplication (part of the Advanced Compression Option), and encryption (part of the Advanced Security Option). Starting with Oracle Database 12c, SecureFiles is the default storage mechanism for LOBs.