Creating a Lookup Table
The ability to use domain lists based on Oracle lookup tables, starts with the creation of a primary table:
CREATE TABLE STREET (ID NUMBER PRIMARY KEY, Name VARCHAR2(50), Street_Type VARCHAR2(20), Geometry MDSYS.SDO_GEOMETRY);
A lookup table is created that will be linked to the primary table. To link the Street_Type column of the Street table, use the following CREATE TABLE statement:
CREATE TABLE STREET_TYPE_LUT (Type VARCHAR2(20) NOT NULL, Description VARCHAR2(64), CONSTRAINT STREET_TYPE_LUT PRIMARY KEY(Type));
The table’s alpha column is linked with the Type column in the Street table. The lookup table needs to be populated with the appropriate values. For example using:
INSERT INTO STREET_TYPE_LUT ("TYPE", "DESCRIPTION") VALUES ('AVE', 'Avenue'); INSERT INTO STREET_TYPE_LUT ("TYPE", "DESCRIPTION") VALUES ('BLVD', 'Boulevard'); INSERT INTO STREET_TYPE_LUT ("TYPE", "DESCRIPTION") VALUES ('CT', 'Court'); INSERT INTO STREET_TYPE_LUT ("TYPE", "DESCRIPTION") VALUES ('DR', 'Drive'); INSERT INTO STREET_TYPE_LUT ("TYPE", "DESCRIPTION") VALUES ('PL', 'Place'); INSERT INTO STREET_TYPE_LUT ("TYPE", "DESCRIPTION") VALUES ('RD', 'Road'); INSERT INTO STREET_TYPE_LUT ("TYPE", "DESCRIPTION") VALUES ('ST', 'Street'); INSERT INTO STREET_TYPE_LUT ("TYPE", "DESCRIPTION") VALUES ('TERR', 'Terrace');