Differences

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

--- integration:rdbms:oracle:example [2011/03/15 19:53]
127.0.0.1 external edit
+++ integration:rdbms:oracle:example [2016/06/28 22:38] (current)
@@ Line 5: / Line 5: @@
 ====== Integration: Relational Databases ======
-===== Delimited Files =====
+===== Oracle =====
-[[integration:rdbms:delimited:home|Overview]] |
+[[integration:rdbms:oracle:home|Overview]] |
-[[integration:rdbms:delimited:environments|Environments]] |
+[[integration:rdbms:oracle:environments|Environments]] |
-[[integration:rdbms:delimited:options|Options]] |
+[[integration:rdbms:oracle:databases|Databases]] |
-[[integration:rdbms:delimited:datatypes|Datatypes]] |
+[[integration:rdbms:oracle:tables|Tables]] |
-[[integration:rdbms:delimited:ofx|Offset Indexes]] |
+[[integration:rdbms:oracle:constraints|Constraints]] |
-[[integration:rdbms:delimited:export|Exporting]] |
+[[integration:rdbms:oracle:datatypes|Datatypes]] |
-[[integration:rdbms:delimited:limitations|Limitations]] |
+[[integration:rdbms:oracle:queries|Queries]] |
-**[[integration:rdbms:delimited:example|Example]]**
+[[integration:rdbms:oracle:updates|Updates]] |
+**[[integration:rdbms:oracle:example|Example]]**
 ----
@@ Line 20: / Line 21: @@
 ==== Example ====
-The following example is a simple database using delimited files.  Each table uses a different combination of column and record delimiters.  The Omnidex Environment File is displayed below; however, the entire example, including sample data files, can be downloaded as a ZIP file at the following link:  **{{:integration:rdbms:delimited:delimited_example.zip|Download ZIP File}}**.
+The following example is a simple database using an Oracle database.  The Omnidex Environment File is displayed below; however, the entire example can be downloaded as a ZIP file at the following link:  **{{:integration:rdbms:oracle:oracle_example.zip|Download ZIP File}}**.  This ZIP file also includes files that can be used to create the Oracle database.
-The following statements will create and build an Omnidex Environment containing delimited files:
+The following statements will create and build an Omnidex Environment against an Oracle database:
 <code>
 > use sql/create_all.sql
@@ Line 31: / Line 32: @@
 Environment created in simple.xml
 -> ;
--> ;
+-> create database                "SIMPLE"
--> create database       "SIMPLE"
+>>  type                          oracle
->>   type                FILE
+>>  version                       "11"
->>   index_directory     "idx"
+>>  user                          "simple"
->>  in                   "simple.xml";
+>>  password                      "simple"
+>>  index_directory               "idx"
+>>  in                            "simple.xml";
 Database SIMPLE created in simple.xml
--> ;
 -> ;
 -> create table          "COUNTRIES"
->>  options              "DELMITED COLUMN=',' RECORD='\r\n' QUOTES"
+>>  physical             "SIMPLE.COUNTRIES"
->>  physical             "dat\countries.csv"
 >>  (
 >>   "COUNTRY"           CHARACTER(2)      omnidex,
@@ Line 50: / Line 51: @@
 >>   "CAPITAL_LAT"       FLOAT             omnidex      usage "LATITUDE",
 >>   "CAPITAL_LONG"      FLOAT             omnidex      usage "LONGITUDE",
->>   constraint COUNTRIES_COUNTRY_PK primary ("COUNTRY")
+>>   constraint COUNTRIES_COUNTRY_PK primary ("COUNTRY"),
+>>   native     COUNTRIES_COUNTRY_IDX ("COUNTRY")
 >>  )
 >>  in                   "simple.xml";
 Table COUNTRIES created in simple.xml
--> ;
 -> ;
 -> create table          "STATES"
->>  options              "DELMITED COLUMN='\t' RECORD='\r\n' QUOTES"
+>>  physical             "SIMPLE.STATES"
->>  physical             "dat\states.txt"
 >>  (
 >>   "STATE"             CHARACTER(2)      omnidex,
@@ Line 67: / Line 67: @@
 >>   "TAX_RATE"          FLOAT             omnidex,
 >>   constraint STATES_STATE_PK primary ("STATE"),
->>   constraint STATES_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES"
+>>   constraint STATES_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES",
+>>   native     STATES_STATE_IDX ("STATE"),
 >>  )
 >>  in                   "simple.xml";
 Table STATES created in simple.xml
--> ;
 -> ;
 -> create table          "GENDERS"
->>  options              "DELMITED COLUMN='|' RECORD='\r\n' QUOTES"
+>>  physical             "SIMPLE.GENDERS"
->>  physical             "dat\genders.txt"
 >>  (
 >>   "GENDER"            CHARACTER(1)      omnidex,
 >>   "DESCRIPTION"       STRING(31)        quicktext,
->>   constraint GENDERS_GENDER_PK primary ("GENDER")
+>>   constraint GENDERS_GENDER_PK primary ("GENDER"),
+>>   native     GENDERS_GENDER_IDX ("GENDER")
 >>  )
 >>  in                   "simple.xml";
 Table GENDERS created in simple.xml
--> ;
 -> ;
 -> create table          "HOUSEHOLDS"
->>  options              "DELIMITED COLUMN='##' RECORD='\n' QUOTES"
+>>  physical             "SIMPLE.HOUSEHOLDS"
->>  physical             "dat\households.txt"
 >>  (
 >>   "HOUSEHOLD"         CHARACTER(12)     omnidex,
@@ Line 97: / Line 95: @@
 >>   constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD"),
 >>   constraint HOUSEHOLDS_STATE_FK foreign ("STATE") references "STATES",
->>   constraint HOUSEHOLDS_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES"
+>>   constraint HOUSEHOLDS_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES",
+>>   native     HOUSEHOLDS_HOUSEHOLD_IDX ("HOUSEHOLD")
 >>  )
 >>  in                   "simple.xml";
 Table HOUSEHOLDS created in simple.xml
--> ;
 -> ;
 -> create table          "INDIVIDUALS"
->>  options              "DELIMITED COLUMN='%%' RECORD='~~' QUOTES"
+>>  physical             "SIMPLE.INDIVIDUALS"
->>  physical             "dat\individuals.txt"
 >>  (
 >>   "INDIVIDUAL"        CHARACTER(12)     omnidex,
@@ Line 112: / Line 108: @@
 >>   "NAME"              CHARACTER(50)     quicktext,
 >>   "GENDER"            CHARACTER(1)      omnidex bitmap,
->>   "BIRTHDATE"         ANSI DATE         omnidex,
+>>   "BIRTHDATE"         ORACLE DATETIME   omnidex,
 >>   "PHONE"             CHARACTER(14)     omnidex,
 >>   "EMAIL"             CHARACTER(60)     quicktext,
@@ Line 118: / Line 114: @@
 >>   constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS",
 >>   constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS",
+>>   native     INDIVIDUALS_INDIVIDUAL_IDX ("INDIVIDUAL")
 >>  )
 >>  in                   "simple.xml";
@@ Line 123: / Line 120: @@
 -> ;
 -> connect simple.xml
-Connected to D:\class\lab2a\simple.xml
+Connected to D:\class\lab2c\simple.xml
 ->
 -> ; Update the Omnidex indexes
 -> update indexes;
-Updated indexes for COUNTRIES (239 rows, 0.031 cpu, 0.390 elapsed)
+Updated indexes for COUNTRIES (239 rows, 0.016 cpu, 0.500 elapsed)
-Updated indexes for STATES (76 rows, 0.016 cpu, 0.031 elapsed)
+Updated indexes for STATES (76 rows, 0.016 cpu, 0.047 elapsed)
-Updated indexes for GENDERS (2 rows, 0.000 cpu, 0.031 elapsed)
+Updated indexes for GENDERS (2 rows, 0.016 cpu, 0.031 elapsed)
-Updated indexes for HOUSEHOLDS (1,909 rows, 0.016 cpu, 0.094 elapsed)
+Updated indexes for HOUSEHOLDS (1,909 rows, 0.031 cpu, 0.110 elapsed)
-Updated indexes for INDIVIDUALS (5,000 rows, 0.031 cpu, 0.390 elapsed)
+Updated indexes for INDIVIDUALS (5,000 rows, 0.031 cpu, 0.578 elapsed)
 Omnidex indexes updated for environment USER_ENVIRONMENT
 -> ;
 -> ; Update statistics to improve performance optimization;
 -> update statistics;
-Updated statistics for COUNTRIES (239 rows, 0.063 cpu, 0.063 elapsed)
+Updated statistics for COUNTRIES (239 rows, 0.156 cpu, 0.203 elapsed)
-Updated statistics for STATES (76 rows, 0.047 cpu, 0.047 elapsed)
+Updated statistics for STATES (76 rows, 0.016 cpu, 0.015 elapsed)
-Updated statistics for GENDERS (2 rows, 0.031 cpu, 0.031 elapsed)
+Updated statistics for GENDERS (2 rows, 0.016 cpu, 0.015 elapsed)
-Updated statistics for HOUSEHOLDS (1,909 rows, 0.094 cpu, 0.094 elapsed)
+Updated statistics for HOUSEHOLDS (1,909 rows, 0.047 cpu, 0.047 elapsed)
-Updated statistics for INDIVIDUALS (5,000 rows, 0.125 cpu, 0.125 elapsed)
+Updated statistics for INDIVIDUALS (5,000 rows, 0.078 cpu, 0.078 elapsed)
 Statistics updated for environment USER_ENVIRONMENT
 -> ;
 -> ; Update the text database to enable PowerSearch;
 -> update text;
-Updated text for COUNTRIES (0.016 cpu, 0.015 elapsed)
+Updated text for COUNTRIES (0.000 cpu, 0.015 elapsed)
 Updated text for STATES (0.000 cpu, 0.000 elapsed)
 Updated text for GENDERS (0.000 cpu, 0.000 elapsed)
-Updated text for HOUSEHOLDS (0.063 cpu, 0.062 elapsed)
+Updated text for HOUSEHOLDS (0.063 cpu, 0.078 elapsed)
-Updated text for INDIVIDUALS (0.063 cpu, 0.078 elapsed)
+Updated text for INDIVIDUALS (0.063 cpu, 0.062 elapsed)
 Text metadata updated for environment USER_ENVIRONMENT
 -> ;
 -> disconnect
 Disconnected
-</code>
-The ZIP file also contains the file sql/export.sql. This file gives examples of how to create delimited files from another Omnidex environment using EXPORT statements:
-<code>
-connect to    simple.xml
-export        (select        *
-                 from        COUNTRIES)
-  to          dat\countries.csv
-  with        TDF COLUMN=',' RECORD=\r\n QUOTES DELETE;
-export        (select        *
-                 from        STATES)
-  to          dat\states.txt
-  with        TDF COLUMN=\t RECORD=\r\n QUOTES DELETE;
-export        (select        *
-                 from        GENDERS)
-  to          dat\genders.txt
-  with        TDF COLUMN=| RECORD=\r\n QUOTES DELETE;
-export        (select        *
-                 from        HOUSEHOLDS)
-  to          dat\households.txt
-  with        TDF COLUMN=## RECORD=\n QUOTES DELETE;
-export        (select        *
-                 from        INDIVIDUALS)
-  to          dat\individuals.txt
-  with        TDF COLUMN=%% RECORD=~~ QUOTES DELETE;
-disconnect
 </code>
 =====  =====
-**[[integration:rdbms:fixed:limitations|Prev]]**
+**[[integration:rdbms:oracle:updates|Prev]]**
 ====== Additional Resources ======
@@ Line 200: / Line 164: @@
 {{page>:bottom_add&nofooter&noeditbtn}}

Back to top

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

Sitemap Recent Changes