Differences

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

--- integration:rdbms:oracle:tables [2011/03/31 18:25]
doc
+++ integration:rdbms:oracle:tables [2016/06/28 22:38] (current)
@@ Line 11: / Line 11: @@
 [[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:storedproc|Stored Procedures]] |
 [[integration:rdbms:oracle:example|Example]]
@@ Line 19: / Line 20: @@
 ==== Tables ====
+=== The CREATE TABLE Statement ===
 The [[dev:sql:statements:create_table:home|CREATE TABLE]] statement is used to declare an Oracle table or view within an Omnidex Environment File.  The Omnidex Environment File will contain a declaration for each Oracle table or view to be accessed, and will correlate all of the schema information between Omnidex and Oracle, including object names and datatypes. This statement can either be issued directly, or it can be extracted from Oracle using the EXTRACT statement as discussed in the previous section.
-<code>
-create table          "STATES"
- physical             "MYUSER.STATES"
- (
-  "STATE"             CHARACTER(2),
-  "DESCRIPTION"       STRING(31),
-  "STATE_CODE"        CHARACTER(2),
-  "REGION"            CHARACTER(2),
-  "COUNTRY"           CHARACTER(2),
-  "TAX_RATE"          FLOAT,
-  constraint STATES_STATE_PK primary ("STATE"),
-  constraint STATES_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES"
- )
- in                   "simple.xml";
-</code>
-=== Omnidex's View of the Oracle Data Objects ===
-When Omnidex accesses a table, it only knows about the data objects that are declared in the Omnidex Environment File.  It does not have an independent understanding of the Oracle environment.  This allows administrators to shape the Omnidex Environment the way that they want.  Some of the opportunities that this provides are:
-  * Omnidex can have a controlled view of the Oracle database, limited to only the tables, columns and views that the application requires.
-  * Omnidex tables can point to Oracle views, allowing an easy approach to reshaping the application's view of the data.
-  * Omnidex columns can often be assigned datatypes that differ from the Oracle datatype.  Character-class datatypes can be interchanged, allowing applications to easily receive the datatype that works best for its needs.  Binary datatypes can be assigned to any of the integer or floating point datatypes; in fact, this is a necessity since Oracle's NUMBER datatype is only an internal datatype.
-  * Omnidex tables can include [[admin:features:expressioncols:home|Expression-based Columns]], which are columns derived from a SQL expression rather than a specific column in the underlying database.
 The following example compares the Oracle declarations with the Omnidex declarations.
@@ Line 58: / Line 36: @@
         REGION_CD               CHAR(2),
         COUNTRY_CD              CHAR(2),
-        TAX_RATE                NUMBER(16,6),
+        TAX_RATE                NUMBER(16,6));
-        constraint              STATES_STATE_PK
-                                primary key (STATE_CD),
-        constraint              STATES_COUNTRY_FK
-                                foreign key (COUNTRY)
-                                references COUNTRIES(COUNTRY);
 </code>
-In the Omnidex declaration, note that the PHYSICAL clause for table points to the Oracle syntax of //user.table//.  Also note that the columns use the PHYSICAL clause to point to the underlying Oracle column name, and use Omnidex datatypes rather than Oracle datatypes.
 **Omnidex CREATE TABLE Statement**
 <code>
 create table          "STATES"
- physical             "MYUSER.STATES"
+ physical             "SIMPLE.STATES"
  (
   "STATE"             CHARACTER(2)   physical "STATE_CD",
@@ Line 78: / Line 50: @@
   "REGION"            CHARACTER(2)   physical "REGION_CD",
   "COUNTRY"           CHARACTER(2)   physical "COUNTRY_CD",
-  "TAX_RATE"          FLOAT,
+  "TAX_RATE"          FLOAT
-  constraint STATES_STATE_PK primary ("STATE"),
-  constraint STATES_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES"
  )
  in                   "simple.xml";
 </code>
+=== Using the Table and Column PHYSICAL Clauses ===
+In the Omnidex declaration, note that the optional PHYSICAL clause for the table allows Omnidex to have a different table name than Oracle. This is most commonly used to allow a //user.table// designation for the Oracle table, but can also be used when administrators wish a different name in the Omnidex environment or need to shorten a table name to meet the 32-character limit in Omnidex.  If the PHYSICAL clause is not present, Omnidex assumes that the table names are the same between Omnidex and Oracle.
+Column declarations can similarly use a PHYSICAL clause to allow Omnidex to have a different column name than Oracle.
+=== Modifying Omnidex's View of the Oracle Data Objects ===
+When Omnidex accesses a table, it only knows about the data objects that are declared in the Omnidex Environment File.  It does not have an independent understanding of the Oracle environment.  This allows administrators to shape the Omnidex Environment the way they want.  Some of the opportunities this provides are:
+  * Omnidex can have a controlled view of the Oracle database, limited to only the tables that the application requires.  Similarly, administrators can choose to include only the columns for each table that the application requires.
+  * Omnidex tables can point to Oracle views, allowing an easy approach to reshaping the application's view of the data.
+  * Omnidex can use different names for tables and columns by using the PHYSICAL clause in the TABLE or COLUMN sections of the CREATE TABLE statement to map to the underlying Oracle names.
+  * Omnidex columns can often be assigned datatypes that differ from the Oracle datatype.  Character-class datatypes can be interchanged, allowing applications to easily receive the datatype that works best for its needs.  Binary datatypes can be assigned to any of the integer or floating point datatypes; in fact, this is a necessity since Oracle's NUMBER datatype is only an internal datatype.
+  * Omnidex tables can include [[admin:features:expressioncols:home|Expression-based Columns]], which are columns derived from a SQL expression rather than a specific column in the underlying database.
 === Referencing Oracle Views ===
@@ Line 89: / Line 75: @@
 Omnidex table declarations can reference Oracle Views.  Omnidex will retrieve from the Oracle view just as though it is a table.  Oracle Views can be used to provide different views of the data, appropriate for the application.
-When referencing an Oracle view, it is necessary to declare a UNIQUEKEY constraint.  The UNIQUEKEY constraint tells Omnidex how to uniquely identify a row.  When Omnidex retrieves individual rows, it will use this unique value; therefore, it is important to that access to this column (or columns) be properly indexed in the underlying table.
+When referencing an Oracle view, it is necessary to declare a UNIQUE constraint.  This requirement for the UNIQUE constraint is independent of any PRIMARY or FOREIGN constraints, discussed in the next page.  The UNIQUE constraint tells Omnidex how to uniquely identify a row.  When Omnidex retrieves individual rows, it will use this unique value; therefore, it is important that access to this column (or columns) be properly indexed in the underlying table.
 **Oracle CREATE VIEW Statement**
@@ Line 102: / Line 88: @@
    REGION_CD,
    COUNTRY_CD,
-   TAX_RATE,
+   TAX_RATE
-   constraint STATE_PK primary key (STATE_CD) rely disable novalidate
  ) as
    select C.DESCRIPTION,
@@ Line 113: / Line 98: @@
           S.COUNTRY_CD,
           S.TAX_RATE
-   from   MYUSER.STATES S, MYUSER.COUNTRIES C
+   from   SIMPLE.STATES S, SIMPLE.COUNTRIES C
    where  S.COUNTRY_CD = C.COUNTRY_CD;
 </code>
-The following Omnidex table declaration references the Oracle view.  Note the presence of the UNIQUE constraint.
 **Omnidex CREATE TABLE Statement**
 <code>
 create table          "STATES_VIEW"
- physical             "MYUSER.STATES_VIEW"
+ physical             "SIMPLE.STATES_VIEW"
  (
   "CTRY_DESCRIPTION"  STRING(47),
@@ Line 132: / Line 115: @@
   "COUNTRY"           CHARACTER(2)   physical "COUNTRY_CD",
   "TAX_RATE"          FLOAT,
-  constraint STATES_STATE_PK primary ("STATE"),
   constraint STATES_STATE_UK unique  ("STATE")
  )
@@ Line 138: / Line 120: @@
 </code>
-=== Mapping Oracle's Datatypes ===
-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.
 =====  =====
 **[[integration:rdbms:oracle:databases|Prev]]** |
-**[[integration:rdbms:oracle:queries|Next]]**
+**[[integration:rdbms:oracle:constraints|Next]]**
 ====== Additional Resources ======

Back to top

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

Sitemap Recent Changes