In this section you could find information about TABLES, their syntax and current convertions.
1. Description
In Oracle, the CREATE TABLE statement is used to create one of the following types of tables: a relational table which is the basic structure to hold user data, or an object table which is a table that uses an object type for a column definition. (Oracle documentation)
CREATE OR REPLACETABLE "MySchema"."BaseTable" ( BaseId NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/DEFAULT10NOT NULL ) COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}' ;
Table properties are removed because they are not required after the migration in Snowflake.
2.2. Constraints and Constraint States
The following constraints will be commented out:
CHECK Constraint
The USING INDEX constraint will be entirely removed from the output code during the conversion.
CREATE OR REPLACETABLE "MySchema"."BaseTable" ( BaseId NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/DEFAULT10NOT NULL,"COL1"NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/NOT NULL !!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!!CHECK( "COL1"IS NOT NULL ), !!!RESOLVE EWI!!! /*** SSC-EWI-0035 - CHECK STATEMENT NOT SUPPORTED ***/!!!CHECK( "COL1"IS NOT NULL ),CONSTRAINT"Constraint1BaseTable"PRIMARY KEY (BaseId) ) COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}' ;
On the other hand, but in the same way, in case you have any constraint state after a NOT NULL constraint as follows:
RELY
NO RELY
RELY ENABLE
RELY DISABLE
VALIDATE
NOVALIDATE
These will also be commented out.
The ENABLE constraint state will be completely removed from the output code during the conversion process. In the case of the DISABLE state, it will also be removed concurrently with the NOT NULL constraint.
CREATE OR REPLACETABLETable1 ( col1 INTNOT NULL, col2 INT , col3 INTNOT NULL/*** SSC-FDM-OR0006 - CONSTRAINT STATE RELY REMOVED FROM NOT NULL INLINE CONSTRAINT ***/ ) COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}' ;
2.3. Foreign Key
If there is a table with a NUMBER column with no precision nor scale, and another table with a NUMBER(*,0) column that references to the previously mentioned NUMBER column, we will comment out this foreign key.
CREATE OR REPLACETABLE "MySchema"."MyTable" ("COL1"NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,CONSTRAINT"PK"PRIMARY KEY ("COL1") ) COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}' ;
CREATE OR REPLACETABLE "MySchema"."MyTable" ("COL1"NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/AS (COL1 * COL2) ) COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}' ;
2.5. Identity Column
For identity columns, a sequence is created and assigned to the column.
CREATE OR REPLACESEQUENCEMySchema.BaseTable_COL0 INCREMENT BY1STARTWITH621COMMENT ='FOR TABLE-COLUMN "MySchema.BaseTable".COL0';CREATE OR REPLACETABLE "MySchema"."BaseTable" ("COL0"NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/DEFAULT MySchema.BaseTable_COL0.NEXTVAL NOT NULL)COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}';
2.6. CLOB and BLOB column declaration
Columns declared as CLOB or BLOB will be changed to VARCHAR.
CREATE OR REPLACETABLE "CustomSchema"."BaseTable" ("PROPERTY"VARCHAR(64) NOT NULL ) COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}' ;
2.8. Default columns with times
The columns declared as Date types will be cast to match with the specific date type.
Oracle
IN -> Oracle_09.sql
CREATETABLETABLE1("COL1"VARCHAR(50) DEFAULT CURRENT_TIMESTAMP);CREATETABLETABLE1( COL0 TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP, COL1 TIMESTAMP(6) DEFAULT CURRENT_TIME, COL2 TIMESTAMP(6) WITHLOCALTIMEZONEDEFAULT'1900-01-01 12:00:00', COL3 TIMESTAMP(6) WITH TIME ZONEDEFAULT'1900-01-01 12:00:00', COL4 TIMESTAMP(6) WITHOUT TIME ZONEDEFAULT'1900-01-01 12:00:00', COL5 TIMESTAMP(6) DEFAULT TO_TIMESTAMP('01/01/1900 12:00:00.000000 AM', 'MM/DD/YYYY HH:MI:SS.FF6 AM') );
Snowflake
OUT -> Oracle_09.sql
CREATE OR REPLACETABLETABLE1 ("COL1"VARCHAR(50) DEFAULT TO_VARCHAR(CURRENT_TIMESTAMP(), 'YYYY-MM-DD HH:MI:SS') ) COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}' ;--** SSC-FDM-0019 - SEMANTIC INFORMATION COULD NOT BE LOADED FOR TABLE1. CHECK IF THE NAME IS INVALID OR DUPLICATED. **CREATE OR REPLACETABLETABLE1 ( COL0 TIMESTAMP(6) DEFAULTCURRENT_TIMESTAMP() :: TIMESTAMP(6), COL1 TIMESTAMP(6) DEFAULTCURRENT_TIME() :: TIMESTAMP(6), COL2 TIMESTAMP_LTZ(6) DEFAULT'1900-01-01 12:00:00' :: TIMESTAMP_LTZ(6), COL3 TIMESTAMP_TZ(6) DEFAULT'1900-01-01 12:00:00' :: TIMESTAMP_TZ(6), COL4 TIMESTAMP(6) WITHOUT TIME ZONEDEFAULT'1900-01-01 12:00:00' :: TIMESTAMP(6) WITHOUT TIME ZONE, COL5 TIMESTAMP(6) DEFAULT TO_TIMESTAMP('01/01/1900 12:00:00.000000 AM', 'MM/DD/YYYY HH:MI:SS.FF6 AM') :: TIMESTAMP(6) ) COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"oracle"}}' ;
2.9 Sharing and Memoptimize options
Some options in Oracle are not required in Snowflake. That is the case for the sharing and memoptimize options, they will be removed in the output code.
CREATE OR REPLACETABLEtable1 ( id NUMBER(38, 18) /*** SSC-FDM-0006 - NUMBER TYPE COLUMN MAY NOT BEHAVE SIMILARLY IN SNOWFLAKE. ***/,nameVARCHAR(50),dateTIMESTAMP/*** SSC-FDM-OR0042 - DATE TYPE COLUMN HAS A DIFFERENT BEHAVIOR IN SNOWFLAKE. ***/,CONSTRAINT pk_table PRIMARY KEY (id) ) COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}' ;
2.10 AS SubQuery
The following properties and clauses are unsupported when creating a table through AS SubQuery in Snowflake.
createtabletable1-- NO DROP NO DELETE HASHING USING sha2_512 VERSION v1 -- blockchain_clause not yet supportedDEFAULT COLLATION somenameONCOMMITDROPDEFINITIONONCOMMITDELETEROWSCOMPRESSNOLOGGINGASselect*from table1;
Snowflake
OUT -> Oracle_11.sql
CREATE OR REPLACETABLEtable1COMMENT ='{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},"attributes":{"component":"oracle"}}'-- NO DROP NO DELETE HASHING USING sha2_512 VERSION v1 -- blockchain_clause not yet supportedASselect*from table1;
Known Issues
Some properties on the tables may be adapted to or commented on because the behavior in Snowflake is different.