Skip navigation

A question about type substitution came type substitution and since the explanation cannot fit into few rows I'll try to describe it shortly here.

Examples below are for illustrative purpose only so don't be fastidious on some details .

Some SQL99 terms:

“A structured type is a named, user-defined data type. A value of a structured type comprises a number of attribute values. Each attribute of a structured type has a data type, specified by an attribute type that is included in the descriptor of the structured type.”

“A structured type may be defined to be a subtype of another structured type, known as its direct supertype. A subtype inherits every attribute of its direct supertype, and may have additional attributes of its own. An expression of a subtype may appear anywhere that an expression of any of its supertypes is allowed (this concept is known as substitutability).”

“One or more base tables can be created based on a structured type. A base table based on a structured type ST can be a subtable of a base table based on a supertype of ST.”

 

You can look at type substitution (as it's in Data Modeler) as constraint, relationship or just a tool allowing you to define precisely what you want to get into target database. Basically it's related to possibility to get instance of subtypes at the place you expect instance of super-type.

Oracle Database and DB2/UDB LUW have a support for such object relational concepts. In terms of Data Modeler we are talking about tables that a based on structured types. Implementation in both databases is different - in Oracle it's a global flag for such table "allow substitution at all levels" or not, while in DB2/UDB you need to build the hierarchy of base tables.

Definition of type substitution at row level is straightforward and it follows SQL99 definition of supertable/subtable hierarchy. First it’s necessary tables to be defined as based on structured type ( tables City_Village and Village) :

before_type_subst.png

Using “New Type Substitution" button, a type substitution link can be created between tables that are based on direct supertype – subtype pair of given structured type. Tables included in supertable/subtable hierarchy show only attributes defined in the type they are based on (inherited attributes are not shown):

after_type_subst.png

With such definition we put a constraint that table City_Village cannot have instances of State_T and Country_T.

Here is the DDL for Oracle Database:

 

CREATE TABLE City_Village
    OF Geopol_Area_T
    SUBSTITUTABLE AT ALL LEVELS
    (
     ID  NOT NULL ,
     Name  NOT NULL
    )
    NOCOMPRESS
    NOCACHE
    NOPARALLEL
    NOROWDEPENDENCIES
    DISABLE ROW MOVEMENT
;
CREATE OR REPLACE TRIGGER OBJ_SUBS_TR_City_Village
BEFORE INSERT ON City_Village
FOR EACH ROW
BEGIN
-- DO NOT DELETE OR MODIFY THE FOLLOWING COMMENTS
-- <cwd_ts_tab table="City" type="City_T" parent_type="Geopol_Area_T" />
-- <cwd_ts_tab table="County" type="County_T" parent_type="Geopol_Area_T" />
-- <cwd_ts_tab table="Village" type="Village_T" parent_type="Geopol_Area_T" />
IF :new.object_value IS NOT OF ( City_T, County_T, Village_T ) THEN
  raise_application_error(-20222,' Only instances of City_T, County_T, Village_T are permitted');
END IF;
END;
/

-- Table City: is part of type substitution hierarchy
-- Table County: is part of type substitution hierarchy
-- Table Village: is part of type substitution hierarchy

And related DDL for DB2/UDB:

 

CREATE TABLE City_Village OF Geopol_Area_T
    (
    ID WITH OPTIONS  NOT NULL ,
    "Name" WITH OPTIONS  NOT NULL ,
    CONSTRAINT City_Village_PK PRIMARY KEY ( My_Oid ) ,
    REF IS My_Oid USER GENERATED
    )
;

ALTER TABLE City_Village ADD CONSTRAINT City_Village_PK PRIMARY KEY (My_Oid)
;

CREATE TABLE City OF City_T UNDER City_Village
    INHERIT SELECT PRIVILEGES
    (
    City_desc WITH OPTIONS  NOT NULL
    )
;

--  ERROR: Table County is based on structured type without attributes
--  ERROR: Table Village is based on structured type without attributes

Moving to area of views implementation in Oracle Database follows definition in SQL99 i.e. hierarchy of views can be built. Here is an example from OE sample schema:

OE_sample.png

And related DDL:

 

CREATE OR REPLACE NOFORCE VIEW OE.OC_CUSTOMERS
OF OE.CUSTOMER_TYP WITH OBJECT IDENTIFIER ( customer_id )   AS
SELECT c.customer_id, c.cust_first_name, c.cust_last_name, c.cust_address,
           c.phone_numbers,c.nls_language,c.nls_territory,c.credit_limit,
           c.cust_email,
           CAST(MULTISET(SELECT o.order_id, o.order_mode,
                               MAKE_REF(oc_customers,o.customer_id),
                               o.order_status,
                               o.order_total,o.sales_rep_id,
                               CAST(MULTISET(SELECT l.order_id,l.line_item_id,
                                                    l.unit_price,l.quantity,
                                             MAKE_REF(oc_product_information,
                                                      l.product_id)
                                             FROM order_items l
                                             WHERE o.order_id = l.order_id)
                                    AS order_item_list_typ)
                         FROM orders o
                         WHERE c.customer_id = o.customer_id)
                AS order_list_typ)
     FROM customers c
;

CREATE OR REPLACE NOFORCE VIEW OE.OC_CORPORATE_CUSTOMERS
OF OE.CORPORATE_CUSTOMER_TYP UNDER OE.OC_CUSTOMERS   AS
SELECT c.customer_id, c.cust_first_name, c.cust_last_name,
              c.cust_address, c.phone_numbers,c.nls_language,c.nls_territory,
              c.credit_limit, c.cust_email,
              CAST(MULTISET(SELECT o.order_id, o.order_mode,
                               MAKE_REF(oc_customers,o.customer_id),
                               o.order_status,
                               o.order_total,o.sales_rep_id,
                               CAST(MULTISET(SELECT l.order_id,l.line_item_id,
                                         l.unit_price,l.quantity,
                                         make_ref(oc_product_information,
                                                    l.product_id)
                                             FROM order_items l
                                             WHERE o.order_id = l.order_id)
                                    AS order_item_list_typ)
                            FROM orders o
                            WHERE c.customer_id = o.customer_id)
              AS order_list_typ), c.account_mgr_id
     FROM customers c
;

 

Type substitution also can be restricted at column level - for columns based on structured type.

 

Philip

As it's mentioned here "Check constraints" Do Not Appear to Import Properly. Is this a bug? DM transforms some column level check constraints into list of values during import from Oracle database.

DM 4.1 added transformation of specific table level check constraints to something else. Here is an example in SQL Developer 4.1:

Arc_constr_SQLDev_DDL.png

Some of you will recognize it as Arc constraint created in DM and generated as table level check constraint - well such constraint can be created manually or such DDL can be generated using Oracle Designer..

DM 4.1 recognizes such constructs (check constraint + related foreign key definitions) and transforms them into Arc constraints. In this way they appear on diagram and are not listed in table level check constraints. This transformation is applied during import from database or DDL script for all supported databases.

So you shouldn't be surprised if you see following picture in SQL Developer 4.1 "Model" tab:

Arc_constr_SQLDev_model_tab.png

Model tab shows current selected table in the browser (with red border) and tables referred by current table and table referring current table. From this point you can continue with analyses of dependencies using "Show parent and child tables" for each table on diagram and at the end can copy the objects to current relational model.

Those of you wanting to transform existing (in the model) table level check constraints to Arc constraints can run the following script:

model.createArcsFromTableCheckConstraints();

As always with scripts you should run it on saved status of your design and if you don't like the result can discard it.

 

Philip

Consultant, author, and Oracle ACE Director Kent Graziano takes it into overtime with his tip on color coding data model diagrams in Oracle SQL Developer Data Modeler.


Kent's tip is based on a post on his blog: Better Data Modeling: Color Code Your Data Model Diagrams using #SQLDevModeler.

 

Kent's blog also includes information about his books, training courses, and more.

               

Want more 2 Minute Tech Tips? Click here.

      

Have and idea for a 2 Minute Tech Tip? Contact The specified item was not found.

Data Modeler (DM) 4.0.3 (and all earlier versions) allows “Before Create”, “After Create”  and “End of Scripts” scripts to be defined at table level and they will be included in generated DDL if related option is set. DM 4.0.3 introduced support for some variables to be used thus allowing building of templates - {table}, {table abbr}, {pk name}, {view}, {schema}, {long name}. Still there is a drawback that those scripts need to be added to tables.

DM 4.1 approached the problem in different way - was added support  transformation scripts to be used at specific points of DDL generation for table – “Before Create”, “Instead of Create”, “After Create” and “End of Script”.

If “Instead of Create” script is defined it’ll suppress built-in DDL generation for table and its components so DDL generation for non supported databases can be implemented.

Sets of such quadruples of scripts can be defined and one of them could be set as active – if no one is active then there is no custom DDL generation.

User_Defined_DDL_Test.png

 

The dialog is activated from “Tools>Design rules and Transformations>Table DDL Transformations”. It allows writing, testing and debugging scripts. Table can be selected for “Test” and DDL preview window will pop-up and shows resulting DDL. There is no DDL preview in “Debug” mode.

Following variables are available to script during execution:

- model - relational model instance;

- pModel - physical model instance;

- table - the table in relational model;

- tableProxy - table definition in physical model;

- ddlStatementsList - should be used to return the list with DDL statements that are created by script – each DDL statement should be added as is shown below:

 

ddlStatementsList.add(new java.lang.String(ddl));

 

There is a new property on table defined “Use DDL Transformation Script” and if it’s set then table will be included in custom DDL generation. That property can be set in table properties dialog or in “DDL generation options”

Inclide_Table_DDL_Script.png

 

DM 4.1 is distributed with “After create” script that generates journal table and related supporting trigger providing 2 DDL statements for each processed table:

The script:

/*
variable ddlStatementsList should be used to return the list with DDL statements
that are created by script - as shown below:
ddlStatementsList.add(new java.lang.String(ddl));
other available variables:
- model - relational model instance
- pModel - physical model instance
- table - the table in relational model
- tableProxy - table definition in physical model
*/
var ddl;
var lname;
//journal table name suffix
jnTabSuf = "_JN";
// trigger name suffix
jnTrgSuf = "_JNtrg";
prompt = model.getAppView().getSettings().isIncludePromptInDDL();
useSchema = model.getAppView().getSettings().isIncludeSchemaInDDL();
if(model.getStorageDesign().isOpen()){
    if(useSchema){
         lname = tableProxy.getLongName();
    }else{
        lname = tableProxy.getName();
    }
}else{
    if(useSchema){
         lname = table.getLongName();
    }else{
        lname = table.getName();
    }
}
if(prompt){
    ddl= "PROMPT Creating Journal Table for '"+lname+"';\n";
}else{
    ddl = "";
}
ddl = ddl + "CREATE TABLE "+lname+jnTabSuf+"\n"+
" (JN_OPERATION CHAR(3) NOT NULL"+"\n"+
" ,JN_ORACLE_USER VARCHAR2(30) NOT NULL"+"\n"+
" ,JN_DATETIME DATE NOT NULL"+"\n"+
" ,JN_NOTES VARCHAR2(240)"+"\n"+
" ,JN_APPLN VARCHAR2(35)"+"\n"+
" ,JN_SESSION NUMBER(38)"+"\n";
cols = table.getElements();
for(var i=0;i<cols.length;i++){
    col = cols[i];
    ddl = ddl +" ,"+col.getName()+" "+col.getDatatypeString();
    if(col.isMandatory()){
        ddl = ddl + " NOT NULL\n";
    }else{
        ddl = ddl + "\n";
    }
}
ddl = ddl + " );";
ddlStatementsList.add(new java.lang.String(ddl));
if(prompt){
    ddl= "\nPROMPT Creating Journal Trigger for '"+lname+"';\n";
}else{
    ddl = "\n";
}
ddl = ddl +
"CREATE OR REPLACE TRIGGER "+lname+jnTrgSuf +"\n"+
"  AFTER \n" +
"  INSERT OR \n" +
"  UPDATE OR \n" +
"  DELETE ON emp2 for each row \n" +
" Declare \n" +
"  rec "+lname+"_JN"+"%ROWTYPE; \n" +
"  blank "+lname+"_JN"+"%ROWTYPE; \n" +
"  BEGIN \n" +
"    rec := blank; \n" +
"    IF INSERTING OR UPDATING THEN \n" ;
for(var i=0;i<cols.length;i++){
    col = cols[i];
    ddl = ddl + "      rec."+col.getName()+" := :NEW."+col.getName()+"; \n";
}
ddl = ddl +
"      rec.JN_DATETIME := SYSDATE; \n" +
"      rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER'); \n" +
"      rec.JN_APPLN := SYS_CONTEXT ('USERENV', 'MODULE'); \n" +
"      rec.JN_SESSION := SYS_CONTEXT ('USERENV', 'SESSIONID'); \n" +
"      IF INSERTING THEN \n" +
"        rec.JN_OPERATION := 'INS'; \n" +
"      ELSIF UPDATING THEN \n" +
"        rec.JN_OPERATION := 'UPD'; \n" +
"      END IF; \n" +
"    ELSIF DELETING THEN \n" ;
for(var i=0;i<cols.length;i++){
    col = cols[i];
    ddl = ddl + "      rec."+col.getName()+" := :OLD."+col.getName()+"; \n";
}
ddl = ddl +
"      rec.JN_DATETIME := SYSDATE; \n" +
"      rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER'); \n" +
"      rec.JN_APPLN := SYS_CONTEXT ('USERENV', 'MODULE'); \n" +
"      rec.JN_SESSION := SYS_CONTEXT ('USERENV', 'SESSIONID'); \n" +
"      rec.JN_OPERATION := 'DEL'; \n" +
"    END IF; \n" +
"    INSERT into "+lname+"_JN"+" VALUES rec; \n" +
"  END; \n" +
"  /"
ddlStatementsList.add(new java.lang.String(ddl));

 

The transformation script also shows the usage of some settings in preferences - "Include Prompt" and 'Include Schema"

And here is the script generated In "DDL Preview" for table COUNTRIES:

 

CREATE TABLE HR.COUNTRIES
    (
     COUNTRY_ID   CHAR (2 BYTE)
     CONSTRAINT COUNTRY_ID_NN NOT NULL ,
     COUNTRY_NAME VARCHAR2 (40 BYTE) ,
     REGION_ID    NUMBER ,
     CONSTRAINT COUNTRY_C_ID_PK PRIMARY KEY ( COUNTRY_ID )
    )
    ORGANIZATION INDEX
    PCTTHRESHOLD 50
;
/* ... other statements from generation ...*/
CREATE TABLE HR.COUNTRIES_JN
(JN_OPERATION CHAR(3) NOT NULL
,JN_ORACLE_USER VARCHAR2(30) NOT NULL
,JN_DATETIME DATE NOT NULL
,JN_NOTES VARCHAR2(240)
,JN_APPLN VARCHAR2(35)
,JN_SESSION NUMBER(38)
,COUNTRY_ID CHAR (2 BYTE) NOT NULL
,COUNTRY_NAME VARCHAR2 (40 BYTE)
,REGION_ID NUMBER
);

CREATE OR REPLACE TRIGGER HR.COUNTRIES_JNtrg
  AFTER
  INSERT OR
  UPDATE OR
  DELETE ON emp2 for each row
Declare
  rec HR.COUNTRIES_JN%ROWTYPE;
  blank HR.COUNTRIES_JN%ROWTYPE;
  BEGIN
    rec := blank;
    IF INSERTING OR UPDATING THEN
      rec.COUNTRY_ID := :NEW.COUNTRY_ID;
      rec.COUNTRY_NAME := :NEW.COUNTRY_NAME;
      rec.REGION_ID := :NEW.REGION_ID;
      rec.JN_DATETIME := SYSDATE;
      rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER');
      rec.JN_APPLN := SYS_CONTEXT ('USERENV', 'MODULE');
      rec.JN_SESSION := SYS_CONTEXT ('USERENV', 'SESSIONID');
      IF INSERTING THEN
        rec.JN_OPERATION := 'INS';
      ELSIF UPDATING THEN
        rec.JN_OPERATION := 'UPD';
      END IF;
    ELSIF DELETING THEN
      rec.COUNTRY_ID := :OLD.COUNTRY_ID;
      rec.COUNTRY_NAME := :OLD.COUNTRY_NAME;
      rec.REGION_ID := :OLD.REGION_ID;
      rec.JN_DATETIME := SYSDATE;
      rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER');
      rec.JN_APPLN := SYS_CONTEXT ('USERENV', 'MODULE');
      rec.JN_SESSION := SYS_CONTEXT ('USERENV', 'SESSIONID');
      rec.JN_OPERATION := 'DEL';
    END IF;
    INSERT into HR.COUNTRIES_JN VALUES rec;
  END;
  /

 

Philip Stoyanov

Import of tables and views from Hive was introduced in Oracle SQL Developer Data Modeler 4.0.2 and probably some of you have seen that description https://blogs.oracle.com/datawarehousing/entry/oracle_sql_developer_data_modeler

I'm going to describe some detail on the process

1. The goal - there is no dedicated physical model for Hive so the goal was to extract metadata about tables defined in Hive and prepare them for use with  Oracle Big Data SQL | Oracle Database | Oracle

 

2. Setting the connection

If you use SQL Developer 4.0.3 then steps described here https://blogs.oracle.com/datawarehousing/entry/oracle_sql_developer_data_modeler will work for you, setting the connection in standalone version of Data Modeler looks different:

Connection_to_Hive.png

 

If you use a JDBC driver from another vendor you need to check driver class used (could be org.apache.hive.jdbc.HiveDriver ).

 

3. Mapping of Hive primitive types

There is no physical model for Hive thus Hive native primitive types cannot fit into rules around logical types to native types mapping:

-    Import – direct name match between hive primitive type and existing Logical data type is required. New “String “ logical data types is added in order to cover “String” primitive Hive data type and it’s mapped for all supported databases to logical type Varchar with default size set to “max” – max means it’ll take max possible value for each database version supported

-    DDL generation and complex type presentation in compare/merge – the name of logical data type is taken if it matches Hive primitive type otherwise data type is transformed to Oracle 12 c data type and then transformed to Hive type:

•    CHAR, NCHAR – to CHAR

•    VARCHAR2, NVARCHAR2 – to Varchar

•    LONGRAW, BLOB, RAW – to Binary

•    FLOAT, BINARY_FLOAT – to Float

•    BINARY_DOUBLE – to Double

•    Date – to Date

•    Timestamp – to Timestamp

•    LONG, CLOB, NCLOB and everything else – to STRING

 

4. Mapping of Hive complex types

Structured types and collection types in Data Types model are used to represent Object relational definitions in Oracle database. The same constructs are used to represent Hive complex type:

-    ARRAY – mapped to collection type

-    STRUCT – mapped to Structured type

-    MAP and UNIONTYPE – mapped to Structured type – one more property is added to structured type – “Hive Type” with possible values (empty – Hive Struct, MAP – Hive MAP, UNIONTYPE – Hive Uniontype

Examples:

CREATE TABLE emp1 (

  name         STRING,

  salary       FLOAT,

  subordinates ARRAY<STRING>,

  deductions   MAP<STRING, FLOAT>,

  address      STRUCT<street:STRUCT<name:STRING, number:INT>, city:STRING, state:STRING, zip:INT>

)

 

CREATE TABLE emp_addresses (

  name         STRING,

  salary       FLOAT,

  subordinates ARRAY<STRING>,

  deductions   MAP<STRING, FLOAT>,

  addresses      ARRAY<STRUCT<street:STRUCT<name:Varchar(30), number:INT>, city:STRING, state:varchar(2), zip:INT>>

)

 

create table union_test(

foo UNION_TYPE< int , double , aray <string> , struct< a: int , b : string>>);

 

Following types will be created in data types model after importing of these definitions from Hive:

Data_Types_Model.png

 

So Hive complex type - STRUCT<street:STRUCT<name:Varchar(30), number:INT>, city:STRING, state:varchar(2), zip:INT>

Will be presented in Data Types model :

Struct_address2.png

And column with same complex type definition will be based on the same structured type – equivalent definitions are recognized and reused

 

5. Imported objects

5.1 Tables - columns with data types and defined comments for table and columns are imported; also details about some Hive specific constructs are imported and stored in dynamic properties for table and columns – partitioned columns, clustering and sorting columns, table type (external or managed – in Hive terms), location …; Those dynamic properties can be included into reports or visualized on relational diagram using settings for existing dynamic properties. In DM 4.1 they can be promoted to User defined Properties for better visualization (color) and manipulation (type, list of values,default value and group)

5.2 Views- definitions are imported as they are defined in Hive – no translation is applied

 

6.Hive tables as external tables in Oracle 12c physical model

New functionality for presenting Oracle external tables in relational model is used to represent Hive tables in relational model (with set classification type as “External”) and in Oracle physical model (with organization set to “External”).

Size for column with primitive types can be changed using already existing in DM functionality for setting size, precision and scale.

By default complex types represented by structured types and collection types are represented in generated DDL (for Oracle external tables) with size 4000 (columns subordinates, deductions and address in the example below):

CREATE TABLE emp1_addresses

    (

     name         VARCHAR2 (4000) ,

     salary       FLOAT ,

     subordinates VARCHAR2(4000) ,

     deductions   VARCHAR2(4000) ,

     address      VARCHAR2(4000)

    )

    ORGANIZATION EXTERNAL

    (

    TYPE ORACLE_HIVE

    DEFAULT DIRECTORY Dir1

    ACCESS PARAMETERS

        (

com.oracle.bigdata.tablename:emp1_addresses

        )

    )

;

 

That size for complex types can be changed at structured type/collection type or at column level in physical model for Oracle.

New property is introduced for structured and collection types – “Max Size as String”

Complex_type_size.png

Complex type can be used in definitions of several  tables and if the size is different then the size can be redefined at column level in physical model:

Size_in_physical_model.png

So we can tune generated DDL to:

CREATE TABLE emp1_addresses

    (

     name         VARCHAR2 (50) ,

     salary       FLOAT ,

     subordinates VARCHAR2(160) ,

     deductions   VARCHAR2(70) ,

     address      VARCHAR2(70)

    )

    ORGANIZATION EXTERNAL

    (

    TYPE ORACLE_HIVE

    DEFAULT DIRECTORY Dir1

    ACCESS PARAMETERS

        (

com.oracle.bigdata.tablename:emp1_addresses

        )

    )

;

 

7. Expanded visualization of complex type

New option is added to relational model diagrams that allow seeing details of structured types and collection of structured types:

expand_complex_types.png

After "resize to visible" details of complex types can be seen on diagram:

expanded_types.png

UnionType (Hive type) has a specific presentation – its meaning in Hive is that column can accommodate value of one of declared types. In used structured type those possible types are represented by attributes (>attr_1, >attr_2..) each one with related type – column foo in table default.union_test3.

Map (Hive type) is represented by structured type with 2 attributes “key” and “value” each one with type related to Hive definition – column deductions in table emp2_changed_name.

Structured type has one more property – “Hive Type” and it can be set to Map or UNIONTYPE

 

8. Visibility of existing dynamic properties

Visibility can be set for dynamic properties already created on tables and entities. New page is added for that in design level settings (settings in design properties dialog

Visibility_of_props.png

And option to show them in View>details menu on diagram ("Dynamic properties" need to be chcked - the same is used for User Defined Properties in DM 4.1):

View_details.png

9. Hive DDl preview

Limited Hive DDL can be generated If table has dynamic property hiveDDL with value true - then it’s possible to have DDL preview for Hive.

(Data Modeler 4.1 provides possibility for user defined DDL using scripting that can replace DDL generated by built-in DDL generator - so it's possible customers to generate complete Hive DDL based on Dynamic/user defined properties set for tables and columns)

Hive_DDL.png

That also works on tables not imported from Hive – as the example above from OE schema

Hive_DDL_Preview.png

DDL statement:

 

CREATE TABLE OE.CUSTOMERS

    (

        CUSTOMER_ID       DECIMAL(6) ,

        CUST_FIRST_NAME   VARCHAR(20) ,

        CUST_LAST_NAME    VARCHAR(20) ,

        CUST_ADDRESS      STRUCT<STREET_ADDRESS:VARCHAR(40),POSTAL_CODE:VARCHAR(10),CITY:VARCHAR(30),STATE_PROVINCE:VARCHAR(10),COUNTRY_ID:CHAR(2)> ,

        PHONE_NUMBERS     ARRAY<VARCHAR(25)> ,

        NLS_LANGUAGE      VARCHAR(3) ,

        NLS_TERRITORY     VARCHAR(30) ,

        CREDIT_LIMIT      DECIMAL(9,2) ,

        CUST_EMAIL        VARCHAR(30) ,

        ACCOUNT_MGR_ID    DECIMAL(6) ,

        CUST_GEO_LOCATION STRUCT<SDO_GTYPE:DECIMAL,SDO_SRID:DECIMAL,SDO_POINT:STRUCT<X:DECIMAL,Y:DECIMAL,Z:DECIMAL>,SDO_ELEM_INFO:ARRAY<DECIMAL>,SDO_ORDINATES:ARRAY<DECIMAL>> ,

        DATE_OF_BIRTH     Date ,

        MARITAL_STATUS    VARCHAR(20) ,

        GENDER            VARCHAR(1) ,

        INCOME_LEVEL      VARCHAR(20)

    )

;

 

Philip Stoyanov