This shows you the differences between two versions of the page.
| Both sides previous revision Previous revision Next revision | Previous revision | ||
|
integration:rdbms:oracle:databases [2011/03/30 21:10] doc |
integration:rdbms:oracle:databases [2016/06/28 22:38] (current) |
||
|---|---|---|---|
| Line 11: | Line 11: | ||
| **[[integration:rdbms:oracle:databases|Databases]]** | | **[[integration:rdbms:oracle:databases|Databases]]** | | ||
| [[integration:rdbms:oracle:tables|Tables]] | | [[integration:rdbms:oracle:tables|Tables]] | | ||
| + | [[integration:rdbms:oracle:constraints|Constraints]] | | ||
| + | [[integration:rdbms:oracle:datatypes|Datatypes]] | | ||
| [[integration:rdbms:oracle:queries|Queries]] | | [[integration:rdbms:oracle:queries|Queries]] | | ||
| [[integration:rdbms:oracle:updates|Updates]] | | [[integration:rdbms:oracle:updates|Updates]] | | ||
| - | [[integration:rdbms:oracle:storedproc|Stored Procedures]] | | ||
| [[integration:rdbms:oracle:example|Example]] | [[integration:rdbms:oracle:example|Example]] | ||
| Line 20: | Line 21: | ||
| ==== Databases ==== | ==== Databases ==== | ||
| - | The [[dev:sql:statements:create_database|CREATE DATABASE]] statement is used to declare an Oracle database within an Omnidex Environment File. This statement can either be issued directly, or it can be extracted from Oracle using the EXTRACT statement as discussed | + | The [[dev:sql:statements:create_database:home|CREATE DATABASE]] statement is used to declare an Oracle database within an Omnidex Environment File. This statement can either be issued directly, or it can be extracted from Oracle using the EXTRACT statement as discussed in the previous section. |
| - | Databases in Omnidex are of a particular type, and the type , such as Oracle, SQL Server, MySQL, ODBC or File. A database that accessThe CREATE DATABASE Omnidex Environment File contains metadata that describes the Oracle database and the Omnidex configuration. This includes the following connection and schema information: | + | The CREATE DATABASE statement must include the database name, the Oracle version, and the Oracle user and password. |
| - | * Oracle major version number (eg. 10, 11, etc) | + | <code> |
| - | * SQL*Net service information | + | create database "SIMPLE" |
| - | * Oracle user and password | + | type oracle |
| - | * Oracle schema information tables, columns and datatypes | + | version "11" |
| - | * Oracle primary and foreign key constraints | + | user "simple" |
| - | * Oracle native indexes | + | password "simple" |
| + | index_directory "idx" | ||
| + | in "simple.xml"; | ||
| + | </code> | ||
| - | === Generating an Omnidex Environment File === | + | === Using SQL*Net === |
| - | 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|EXTRACT]] statement is used to connect to the Oracle database, query the underlying schema information, and generate the appropriate statements. | + | Omnidex is frequently installed on a separate server than the Oracle database. In fact, this is preferable since it allows both Omnidex and Oracle to operate at their best without competing for resources. This means that Omnidex often accesses the Oracle database through SQL*Net. When using SQL*Net, simply state the name of the SQL*Net service in the SERVICE clause. |
| - | + | ||
| - | The extracted CREATE statements represent the best translations that can be automatically generated from Oracle; however, these statements may require edits by the administrator. The documentation on [[integration:rdbms:oracle:databases|Databases]] and [[integration:rdbms:oracle:tables|Tables]] describe specific areas where edits may be required. | + | |
| <code> | <code> | ||
| - | extract DDL | + | create database "SIMPLE" |
| - | for ORACLE | + | type oracle |
| - | to "star.sql" | + | version "11" |
| - | with VERSION="11" | + | user "simple" |
| - | USERNAME="star" | + | password "simple" |
| - | PASSWORD="star" | + | service "simple" |
| - | INDEX_DIRECTORY="idx" | + | index_directory "idx" |
| + | in "simple.xml"; | ||
| </code> | </code> | ||
| - | The EXTRACT statement has several options that supply the connection information and determine the metadata to be extracted. For Oracle, the following options can be used: | + | === Oracle Security === |
| - | + | ||
| - | == VERSION == | + | |
| - | + | ||
| - | The major version of Oracle. Currently, Oracle versions "10" and "11" are supported. This option is required. | + | |
| - | + | ||
| - | == USERNAME == | + | |
| - | + | ||
| - | The username to be used when accessing Oracle. One username 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 username referenced in the USERNAME 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 == | + | Omnidex provides two methods of supporting Oracle security. |
| - | 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. | + | * Administrators can register a user and password in the Omnidex Environment File. This user and password will always be used to access the Oracle database, and no users and passwords will be needed from the application. |
| + | * The application can pass a user and password in the [[dev:connections:home|Omnidex Connection String]]. This user and password is then passed directly to Oracle to access the database. | ||
| + | One of these approaches must be used to allow connections to the Oracle database. Administrators should insure that the user and password provides access to all of the database objects referenced in the Omnidex Environment Files. | ||
| ===== ===== | ===== ===== | ||