This is an old revision of the document!
The 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.
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";
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:
The following example compares the Oracle declarations with the Omnidex declarations.
Oracle CREATE TABLE Statement
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), constraint STATES_STATE_PK primary key (STATE_CD), constraint STATES_COUNTRY_FK foreign key (COUNTRY) references COUNTRIES(COUNTRY);
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
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";
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.
Oracle CREATE VIEW Statement
create view STATES_VIEW ( CTRY_DESCRIPTION, CTRY_CAPITAL, STATE_CD, DESCRIPTION, STATE_NUM, REGION_CD, COUNTRY_CD, TAX_RATE, constraint STATE_PK primary key (STATE_CD) rely disable novalidate ) as select C.DESCRIPTION, C.CAPITAL, S.STATE_CD, S.DESCRIPTION, S.STATE_NUM, S.REGION_CD, S.COUNTRY_CD, S.TAX_RATE from MYUSER.STATES S, MYUSER.COUNTRIES C where S.COUNTRY_CD = C.COUNTRY_CD;
The following Omnidex table declaration references the Oracle view. Note the presence of the UNIQUE constraint.
Omnidex CREATE TABLE Statement
create table "STATES_VIEW" physical "MYUSER.STATES_VIEW" ( "CTRY_DESCRIPTION" STRING(47), "CTRY_CAPITAL" STRING(31), "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_STATE_UK unique ("STATE") ) in "simple.xml";
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.
The table declarations in the Omnidex Environment File can contain primary and foreign key constraints that establish parent-child relationships between the tables. Omnidex does not validate or enforce these constraints; they are for informational purposes only.
In most situations, the constraints declared in Omnidex should match those declared in the Oracle database; however, administrators may alter these if they wish. At times, a parent-child relationship may exist between tables in Oracle even though a constraint is not formally declared. These can be declared in Omnidex, allowing more advanced optimization of multi-table queries.
Omnidex is tolerant of violations of foreign key constraints, such as foreign keys for which there are no primary keys in the corresponding tables. Omnidex is not tolerant of violations of primary key constraints, as these can lead to incorrect results returned from queries.
See also: