Integration: Relational Databases

Oracle

Overview | Environments | Databases | Tables | Constraints | Datatypes | Queries | Updates | Example


Environments

The Omnidex Environment File contains metadata that describes the Oracle database and the Omnidex configuration. This includes the following connection and schema information:

Generating an Omnidex Environment File

Omnidex can automatically generate the CREATE ENVIRONMENT, CREATE DATABASE, CREATE TABLE and CREATE INDEX statements needed for an Omnidex Environment File. The EXTRACT statement is used to connect to the Oracle database, query the underlying schema information, and generate the appropriate statements. This statement can be issued through OdxSQL, or through any ODBC or JDBC application.

extract       DDL
  for         ORACLE
  to          "simple.sql"
  with        VERSION="11"
              USER="simple"
              PASSWORD="simple"
              DATABASE="simple"
              INDEX_DIRECTORY="idx"


The following options from the EXTRACT SQL statement are used to provide Oracle connection information and control the output of the statement. These options also correlate with the options of the CREATE DATABASE statement.

VERSION

The major version of Oracle. Currently, Oracle versions “10” and “11” are supported. This option is required.

USER

The user to be used when accessing Oracle. One user and password is used when connecting to the Oracle database. That login determines that security access to the database and the visibility of the underlying database objects. This option is required.

PASSWORD

The password to be used when accessing Oracle. This password must correspond to the user referenced in the USER option. This option is required.

SERVICE

If SQL*Net is to be used to access the Oracle database, then the SERVICE option contains the SQL*Net service name. If this option is omitted, then SQL*Net is not used.

DATABASE

The DATABASE option controls the logical database name to be recorded in the Omnidex Environment File for this database. This is not an Oracle setting, but rather a logical name given to the database for use in Omnidex statements. This option is required.

TABLES

The TABLES option controls the table schema information to be extracted from Oracle. The TABLES option can include a comma-separated list of tables, “USER.*” to extract all tables for a named user, or “*” to extract all tables. If this option is omitted, then all available tables are extracted.

INDEX_DIRECTORY

The INDEX_DIRECTORY option declares the directory to contain the Omnidex indexes. This is not an Oracle setting, but rather path designation where indexes should be built. This directory can include either absolute or relative path designations. If this option is omitted, then the indexes will be built in the same directory as the Omnidex Environment File.

Limitations of the Omnidex EXTRACT statement

The extracted CREATE statements represent the best translations that can be automatically generated from Oracle; however, these statements may require edits by the administrator. Edits that may be required include:

Manually Creating an Omnidex Environment File

An Omnidex Environment File can also be created by manually issuing CREATE ENVIRONMENT, CREATE DATABASE, CREATE TABLE and CREATE INDEX statements in OdxSQL or using ODBC or JDBC. If these statements are written to a text batch file, then that file can be submitted to OdxSQL using the USE command.

Prev | Next

Additional Resources

See also: