This shows you the differences between two versions of the page.
|
integration:rdbms:oracle:tables [2011/03/31 17:29] doc |
integration:rdbms:oracle:tables [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | |||
| - | ====== Integration: Relational Databases ====== | ||
| - | |||
| - | ===== Oracle ===== | ||
| - | |||
| - | [[integration:rdbms:oracle:home|Overview]] | | ||
| - | [[integration:rdbms:oracle:environments|Environments]] | | ||
| - | [[integration:rdbms:oracle:databases|Databases]] | | ||
| - | **[[integration:rdbms:oracle:tables|Tables]]** | | ||
| - | [[integration:rdbms:oracle:queries|Queries]] | | ||
| - | [[integration:rdbms:oracle:updates|Updates]] | | ||
| - | [[integration:rdbms:oracle:storedproc|Stored Procedures]] | | ||
| - | [[integration:rdbms:oracle:example|Example]] | ||
| - | |||
| - | ---- | ||
| - | |||
| - | ==== Tables ==== | ||
| - | |||
| - | 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. | ||
| - | |||
| - | <code> | ||
| - | create table STATES | ||
| - | (STATE_CD CHAR(2), | ||
| - | DESCRIPTION VARCHAR2(31), | ||
| - | STATE_NUM CHAR(2), | ||
| - | REGION_CD CHAR(2), | ||
| - | COUNTRY_CD CHAR(2), | ||
| - | TAX_RATE NUMBER(16,6)); | ||
| - | |||
| - | alter table STATES | ||
| - | add constraint STATES_STATE_PK | ||
| - | primary key (STATE) | ||
| - | using index | ||
| - | pctfree 20 | ||
| - | tablespace SIMPLE | ||
| - | storage (initial 100k | ||
| - | next 100k | ||
| - | minextents 1 | ||
| - | maxextents unlimited | ||
| - | pctincrease 0); | ||
| - | |||
| - | alter table STATES | ||
| - | add 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. | ||
| - | |||
| - | <code> | ||
| - | create table "STATES" | ||
| - | physical "MYUSER.STATES" | ||
| - | ( | ||
| - | "STATE" CHARACTER(2) physical "STATE_CD", | ||
| - | "DESCRIPTION" STRING(31), | ||
| - | "STATE_CODE" CHARACTER(2) physical "STATE_NUM", | ||
| - | "REGION" CHARACTER(2) physical "REGION_CD", | ||
| - | "COUNTRY" CHARACTER(2) physical "COUNTRY_CD", | ||
| - | "TAX_RATE" FLOAT, | ||
| - | constraint STATES_STATE_PK primary ("STATE"), | ||
| - | constraint STATES_COUNTRY_FK foreign ("COUNTRY") references "COUNTRIES" | ||
| - | ) | ||
| - | in "simple.xml"; | ||
| - | </code> | ||
| - | |||
| - | |||
| - | |||
| - | ===== ===== | ||
| - | |||
| - | **[[integration:rdbms:oracle:databases|Prev]]** | | ||
| - | **[[integration:rdbms:oracle:queries|Next]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:integration:rdbms:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||