This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
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 ====== | ====== 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 ==== | ==== 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> | <code> | ||
> use sql/create_all.sql | > use sql/create_all.sql | ||
Line 31: | Line 32: | ||
Environment created in simple.xml | 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 | Database SIMPLE created in simple.xml | ||
- | -> ; | ||
-> ; | -> ; | ||
-> create table "COUNTRIES" | -> create table "COUNTRIES" | ||
- | >> options "DELMITED COLUMN=',' RECORD='\r\n' QUOTES" | + | >> physical "SIMPLE.COUNTRIES" |
- | >> physical "dat\countries.csv" | + | |
>> ( | >> ( | ||
>> "COUNTRY" CHARACTER(2) omnidex, | >> "COUNTRY" CHARACTER(2) omnidex, | ||
Line 50: | Line 51: | ||
>> "CAPITAL_LAT" FLOAT omnidex usage "LATITUDE", | >> "CAPITAL_LAT" FLOAT omnidex usage "LATITUDE", | ||
>> "CAPITAL_LONG" FLOAT omnidex usage "LONGITUDE", | >> "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"; | >> in "simple.xml"; | ||
Table COUNTRIES created in simple.xml | Table COUNTRIES created in simple.xml | ||
- | -> ; | ||
-> ; | -> ; | ||
-> create table "STATES" | -> create table "STATES" | ||
- | >> options "DELMITED COLUMN='\t' RECORD='\r\n' QUOTES" | + | >> physical "SIMPLE.STATES" |
- | >> physical "dat\states.txt" | + | |
>> ( | >> ( | ||
>> "STATE" CHARACTER(2) omnidex, | >> "STATE" CHARACTER(2) omnidex, | ||
Line 67: | Line 67: | ||
>> "TAX_RATE" FLOAT omnidex, | >> "TAX_RATE" FLOAT omnidex, | ||
>> constraint STATES_STATE_PK primary ("STATE"), | >> 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"; | >> in "simple.xml"; | ||
Table STATES created in simple.xml | Table STATES created in simple.xml | ||
- | -> ; | ||
-> ; | -> ; | ||
-> create table "GENDERS" | -> create table "GENDERS" | ||
- | >> options "DELMITED COLUMN='|' RECORD='\r\n' QUOTES" | + | >> physical "SIMPLE.GENDERS" |
- | >> physical "dat\genders.txt" | + | |
>> ( | >> ( | ||
>> "GENDER" CHARACTER(1) omnidex, | >> "GENDER" CHARACTER(1) omnidex, | ||
>> "DESCRIPTION" STRING(31) quicktext, | >> "DESCRIPTION" STRING(31) quicktext, | ||
- | >> constraint GENDERS_GENDER_PK primary ("GENDER") | + | >> constraint GENDERS_GENDER_PK primary ("GENDER"), |
+ | >> native GENDERS_GENDER_IDX ("GENDER") | ||
>> ) | >> ) | ||
>> in "simple.xml"; | >> in "simple.xml"; | ||
Table GENDERS created in simple.xml | Table GENDERS created in simple.xml | ||
- | -> ; | ||
-> ; | -> ; | ||
-> create table "HOUSEHOLDS" | -> create table "HOUSEHOLDS" | ||
- | >> options "DELIMITED COLUMN='##' RECORD='\n' QUOTES" | + | >> physical "SIMPLE.HOUSEHOLDS" |
- | >> physical "dat\households.txt" | + | |
>> ( | >> ( | ||
>> "HOUSEHOLD" CHARACTER(12) omnidex, | >> "HOUSEHOLD" CHARACTER(12) omnidex, | ||
Line 97: | Line 95: | ||
>> constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD"), | >> constraint HOUSEHOLDS_HOUSEHOLD_PK primary ("HOUSEHOLD"), | ||
>> constraint HOUSEHOLDS_STATE_FK foreign ("STATE") references "STATES", | >> 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"; | >> in "simple.xml"; | ||
Table HOUSEHOLDS created in simple.xml | Table HOUSEHOLDS created in simple.xml | ||
- | -> ; | ||
-> ; | -> ; | ||
-> create table "INDIVIDUALS" | -> create table "INDIVIDUALS" | ||
- | >> options "DELIMITED COLUMN='%%' RECORD='~~' QUOTES" | + | >> physical "SIMPLE.INDIVIDUALS" |
- | >> physical "dat\individuals.txt" | + | |
>> ( | >> ( | ||
>> "INDIVIDUAL" CHARACTER(12) omnidex, | >> "INDIVIDUAL" CHARACTER(12) omnidex, | ||
Line 112: | Line 108: | ||
>> "NAME" CHARACTER(50) quicktext, | >> "NAME" CHARACTER(50) quicktext, | ||
>> "GENDER" CHARACTER(1) omnidex bitmap, | >> "GENDER" CHARACTER(1) omnidex bitmap, | ||
- | >> "BIRTHDATE" ANSI DATE omnidex, | + | >> "BIRTHDATE" ORACLE DATETIME omnidex, |
>> "PHONE" CHARACTER(14) omnidex, | >> "PHONE" CHARACTER(14) omnidex, | ||
>> "EMAIL" CHARACTER(60) quicktext, | >> "EMAIL" CHARACTER(60) quicktext, | ||
Line 118: | Line 114: | ||
>> constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS", | >> constraint INDIVIDUALS_HOUSEHOLD_FK foreign ("HOUSEHOLD") references "HOUSEHOLDS", | ||
>> constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS", | >> constraint INDIVIDUALS_GENDER_FK foreign ("GENDER") references "GENDERS", | ||
+ | >> native INDIVIDUALS_INDIVIDUAL_IDX ("INDIVIDUAL") | ||
>> ) | >> ) | ||
>> in "simple.xml"; | >> in "simple.xml"; | ||
Line 123: | Line 120: | ||
-> ; | -> ; | ||
-> connect simple.xml | -> connect simple.xml | ||
- | Connected to D:\class\lab2a\simple.xml | + | Connected to D:\class\lab2c\simple.xml |
-> | -> | ||
-> ; Update the Omnidex indexes | -> ; Update the Omnidex indexes | ||
-> update 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 | Omnidex indexes updated for environment USER_ENVIRONMENT | ||
-> ; | -> ; | ||
-> ; Update statistics to improve performance optimization; | -> ; Update statistics to improve performance optimization; | ||
-> update statistics; | -> 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 | Statistics updated for environment USER_ENVIRONMENT | ||
-> ; | -> ; | ||
-> ; Update the text database to enable PowerSearch; | -> ; Update the text database to enable PowerSearch; | ||
-> update text; | -> 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 STATES (0.000 cpu, 0.000 elapsed) | ||
Updated text for GENDERS (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 | Text metadata updated for environment USER_ENVIRONMENT | ||
-> ; | -> ; | ||
-> disconnect | -> disconnect | ||
Disconnected | 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> | </code> | ||
===== ===== | ===== ===== | ||
- | **[[integration:rdbms:fixed:limitations|Prev]]** | + | **[[integration:rdbms:oracle:updates|Prev]]** |
====== Additional Resources ====== | ====== Additional Resources ====== | ||
Line 200: | Line 164: | ||
{{page>:bottom_add&nofooter&noeditbtn}} | {{page>:bottom_add&nofooter&noeditbtn}} | ||
+ |