CORAL Component documentation: OracleAccess

Ioannis Papadopoulos

CERN

October 2005


1. Introduction

1.1. Purpose of the component

The OracleAccess package is a plugin library providing an implementation of the IRelationalDomain interface for accessing an Oracle database. The package is based on the Oracle Call Interface (OCI) API.

1.2. Known problem and restrictions

The package is for the moment tested against Oracle 9i and 10g database servers. Earlier server versions are not supported.

1.3. Repository of the component

:pserver:anonymoys@coral.cvs.cern.ch:/cvs/coral/coral/OracleAccess

2. OracleAccess Semantics

2.1. The logical data hiererchy

In order to connect to an Oracle database using CORAL one should specify a connection string which typically has a format such as

oracle://databaseServerName:port/schemaName/serviceName
where the port number is optional.

The schema name is in practice the user name of an oracle user. A user has access to the tables defined in the schema of another user for which all the relevant privileges have been granted.

The database server name refers either to the logical name which should appear as an entry in the $(TNS_ADMIN)/tnsnames.ora file, or an actual server hosting the database.

2.2. Controlling Oracle-specific parameters

2.2.1. Table spaces

New tables, indices and LOBs are created in the default table space of the user. This may not always be the optimum behaviour for a user who may wish that indices especially are created in different table spaces. There are three ways which allow the user specify the table space for new tables and indices:

  1. By setting the environment variables CORAL_ORA_TS_TAB, CORAL_ORA_TS_IND and CORAL_ORA_TS_LOB to the name of the table space for new tables, indices and LOBs respectively.

  2. By setting the properties TableSpaceForTables, TableSpaceForIndices and TableSpaceForLobs of the IRelationalDomain component "CORAL/RelationalPlugins/oracle" to the name of the table space for new tables, indices and LOBsrespectively.

  3. By overriding the default values (empty string) in the relevant methods of the CORAL API with the actual ones.

2.2.2. LOB handling

When reading back a LOB object from the database, this is done in chunks which have a default size of 1024 bytes (1 KB). This default value can changed by setting the property LobChunkSize or the environment variable CORAL_ORA_LOB_CHUNK_SIZE accordingly.

2.2.3. Enabling SQL TRACE

A user can trigger the generation of a trace file in the database server, whenever he/she authenticates, by setting the environment variable CORAL_ORA_SQL_TRACE_ON to any value.

In case the environment variable CORAL_ORA_CERN_TRACE_ON is set, it takes precedence over CORAL_ORA_SQL_TRACE_ON and executes the CERN_TRACE package in the server, which has to be already installed on the server by the system administrators. This also triggers the generation of the trace file, and whenever the user session is ended the trace file is sent by mail to the address stored in the environment variable CORAL_ORA_CERN_TRACE_ON.

3. Implementation specifics

The package is based on the the Oracle Call Interface (OCI) API. The choice was favoured with respect to OCCI, the C++ client library, in order to avoid potential problems which will arise with the non-availability of the OCCI library for a given C++ compiler, especially for the Linux platforms. The OCI API allows the implementation of the POOL Relational Abstraction Layer (RAL) interfaces in the most efficient way.

The shared library is linked against the Oracle instant client 10.2.0.1 libraries.

It is necessary that during runtime the relevant environment path variables are setup such the the Oracle client runtime libraries can be found.

4. Related components

  • RelationalAccess, is the package where the CORAL abstract interfaces are defined.

  • CoralCommon, is the package where some developer-level interfaces and some common implementation classes are defined.

5. OracleAccess Reference

5.1. Properties of the "CORAL/RelationalPlugins/oracle" component

  • TableSpaceForTables : defines the table space where tables are created. It takes its default value from the environment variable CORAL_ORA_TS_TAB. If not specified neither there, nor in the CORAL API, the user default is used.

  • TableSpaceForIndices : defines the table space where indices are created. It takes its default value from the environment variable CORAL_ORA_TS_IND. If not specified neither there, nor in the CORAL API, the user default is used.

  • TableSpaceForIndices : defines the table space where LOBs are created. It takes its default value from the environment variable CORAL_ORA_TS_LOB. If not specified neither there, nor in the CORAL API, the user default is used.

  • LobChunkSize : defines the size in bytes of the chunks when reading a LOB from the database. It takes its default value from the environment variable CORAL_ORA_LOB_CHUNK_SIZE. If not specified a default of 1024 bytes (1KB) is used.

5.2. Other environment variables

  • CORAL_ORA_SQL_TRACE_ON : if set to any value, it enables the generation of a trace file on the database server.

  • CORAL_ORA_CERN_TRACE_ON : if set, it enables the generation of a trace file on the database server, which is sent to the e-mail address defined in this variable.