Differences

This shows you the differences between two versions of the page.

--- integration:rdbms:oracle:environments [2011/03/14 14:29]
127.0.0.1 external edit
+++ integration:rdbms:oracle:environments [2016/06/28 22:38] (current)
@@ Line 5: / Line 5: @@
 ====== Integration: Relational Databases ======
-===== Delimited Files =====
+===== Oracle =====
-[[integration:rdbms:delimited:home|Overview]] |
-**[[integration:rdbms:delimited:environments|Environments]]** |
-[[integration:rdbms:delimited:options|Options]] |
-[[integration:rdbms:delimited:datatypes|Datatypes]] |
-[[integration:rdbms:delimited:ofx|Offset Indexes]] |
-[[integration:rdbms:delimited:export|Exporting]] |
-[[integration:rdbms:delimited:limitations|Limitations]] |
-[[integration:rdbms:delimited:example|Example]]
+[[integration:rdbms:oracle:home|Overview]] |
+**[[integration:rdbms:oracle:environments|Environments]]** |
+[[integration:rdbms:oracle:databases|Databases]] |
+[[integration:rdbms:oracle:tables|Tables]] |
+[[integration:rdbms:oracle:constraints|Constraints]] |
+[[integration:rdbms:oracle:datatypes|Datatypes]] |
+[[integration:rdbms:oracle:queries|Queries]] |
+[[integration:rdbms:oracle:updates|Updates]] |
+[[integration:rdbms:oracle:example|Example]]
 ----
@@ Line 21: / Line 21: @@
 ==== Environments ====
-The Omnidex Environment File is the key to setting up Omnidex on raw data files.  Delimited files do not contain metadata about columns and datatypes.  The Omnidex Environment File fulfills this role.
+The Omnidex Environment File contains metadata that describes the Oracle database and the Omnidex configuration.  This includes the following connection and schema information:
-=== Declaring Databases for Raw Data Files ===
+  * Oracle major version number (eg. 10, 11, etc)
+  * SQL*Net service information
+  * Oracle user and password
+  * Oracle schema information tables, columns and datatypes
+  * Oracle primary and foreign key constraints
+  * Oracle native indexes
-Databases in Omnidex are of a particular type, such as Oracle, SQL Server, MySQL or ODBC.  A database that accesses raw data file has its own type: FILE.  All tables within this database must be raw data files, though they can be a mixture of the types of raw data files supported by Omnidex.
+=== 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 [[dev:sql:statements:extract:home|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.
 <code>
-create database       "SIMPLE"
+extract       DDL
- type                 FILE
+  for         ORACLE
- index_directory      "idx"
+  to          "simple.sql"
- in                   "simple.xml";
+  with        VERSION="11"
+              USER="simple"
+              PASSWORD="simple"
+              DATABASE="simple"
+              INDEX_DIRECTORY="idx"
 </code>
+\\
+The following options from the [[dev:sql:statements:extract:home|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 [[dev:sql:statements:create_database:home|CREATE DATABASE]] statement.
+== VERSION ==
-=== Declaring Tables for Delimited Files ===
+The major version of Oracle.  Currently, Oracle versions "10" and "11" are supported.  This option is required.
-Tables will point to one or more delimited files, and the files must match the record layout specified in the [[dev:sql:statements:create_table:home|CREATE TABLE]] statement.  In the [[dev:sql:statements:create_table:home|CREATE TABLE]] statement, the [[integration:rdbms:delimited:options|OPTIONS clause]] is used to identify this as a delimited file and indicate which delimiters to use.  The DELIMITED option indicates that this is a delimited file.  The remaining options govern the specific delimiters and behavior to use when parsing the file.
+== USER ==
-In the CREATE TABLE statement, the PHYSICAL clause is used to point to the physical location of the raw data file.  This PHYSICAL clause can contain a relative path name (relative to the location of the Omnidex Environment File) such as "dat/states.csv", or an absolute path name such as "d:/class/lab1/dat/states.csv".  This PHYSICAL clause can also contain wildcards as described below, and can also reference [[appendix:reference:envvar|environment variables]] using a special syntax.
+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.
-<code sql>
+== PASSWORD ==
-create table          "STATES"
- options              "DELIMITED COLUMN=',' RECORD='\r\n' QUOTES"
- physical             "dat/states.csv"
- ...
-</code>
-In the example above, a single file was used as the source of data for a table.  It is also possible for multiple files to be used as the source of data for a table.  As long as the files all share the same record layout, they will all be processed when reading the table. Filesets can be declared as a comma-separated list of files, or they can be declared using wildcards in the filename.  In this example, three files are declared individually in the PHYSICAL clause:
+The password to be used when accessing Oracle.  This password must correspond to the user referenced in the USER option.  This option is required.
-<code sql>
+== SERVICE ==
-create table          "STATES"
- options              "DELIMITED COLUMN=',' RECORD='\r\n' QUOTES"
- physical             "c:/class/lab1/dat/states1.csv,
-                       c:/class/lab1/dat/states2.csv,
-                       d:/class/lab1/dat/states3.csv"
- ...
-</code>
-In this second example, wildcards are used to declare these files:
+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.
-<code sql>
+== DATABASE ==
-create table          "STATES"
- options              "DELIMITED COLUMN=',' RECORD='\r\n' QUOTES"
+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.
- physical             "c:/class/lab1/dat/states*.csv,
-                       d:/class/lab1/dat/states*.csv"
+== TABLES ==
- ...
-</code>
+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:
+  * Oracle object names, such as tables, columns, constraints or indexes, are reduced to 32 bytes in length for use in Omnidex.
+  * Most Oracle binary data is stored in NUMBER datatypes. If the NUMBER datatype is accompanied by scale and precision information, Omnidex can guess at an appropriate matching binary datatype, such as BIGINT, INTEGER, SMALLINT, TINYINT, FLOAT or DOUBLE. If scale and    precision are not specified, Omnidex will necessarily use a DOUBLE datatype. For the best performance and ease of development, these datatypes should be changed to the smallest binary datatype that will accommodate the values in the column.
+  * Oracle National Character datatypes, which are commonly used to store Unicode data, are shown as CHARACTER or STRING datatype, as these are the most universal datatypes in an Omnidex application. If necessary, these can be changed to Omnidex NCHARACTER or NSTRING datatypes.
+  * Depending on the configuration of the Oracle database, some constraint information may not be available and must be added manually.
+=== Manually Creating an Omnidex Environment File ===
-Files can reside in multiple directories and in multiple drives or volumes to allow a table to span multiple storage devices.  Files may also reside on a Storage Area Network (SAN); in fact, this is one of the most common implementations of Omnidex.  Files may also reside on Microsoft Windows mapped drives; however, users should expect performance degradation to occur in this scenario.
+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.
-Wildcards provide a great flexibility since they allow more data to be added to a table by simply moving another file into place.  Care should be taken with this approach, though, since the table must be reindexed as soon as new data is in place.
 =====  =====
-**[[integration:rdbms:delimited:home|Prev]]** |
+**[[integration:rdbms:oracle:home|Prev]]** |
-**[[integration:rdbms:delimited:options|Next]]**
+**[[integration:rdbms:oracle:databases|Next]]**
 ====== Additional Resources ======

Back to top

integration/rdbms/oracle/environments.1300112997.txt.gz · Last modified: 2016/06/28 22:38 (external edit)

Sitemap Recent Changes