Jason Trewin at FreshGravity provided this Oracle to Snowflake Table DDL conversion script. FreshGravity is a great Snowflake partner, and Jason is working on his second Snowflake deployment for our shared customers.
He shared a great approach to DDL conversion from Oracle to Snowflake. I thought it was so useful that after some discussion he agreed to let me write up and post on it.
You can modify the where clauses to select the tables you need to convert, and modify the mappings from Oracle table owners to Snowflake owners.
Thanks and credit to Jason Trewin for contributing this useful script to the Snowflake community.
-- Script contributed to the Snowflake community courtesy of Jason Trewin at FreshGravity.
SELECT 'CREATE OR REPLACE TABLE '
|| NEW_SCHEMA_NAME
|| '.' || TABLE_NAME || ' (' || LISTAGG(COLUMN_NAME || ' ' || CONVERTED_DATA_TYPE || CASE WHEN NULLABLE = 'N' THEN ' NOT NULL' ELSE ''END , ' , ') WITHIN GROUP (ORDER BY COLUMN_ID) || ');' --AS COLUMN_LIST
FROM (
SELECT DATA_TYPE, COLUMN_NAME, ALL_TABLES.TABLE_NAME, ALL_TABLES.OWNER,DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE,
CASE WHEN INSTR(DATA_TYPE,'WITH LOCAL TIME ZONE') > 0 THEN 'TIMESTAMP_TZ'
WHEN INSTR(DATA_TYPE,'TIMESTAMP') > 0 THEN 'TIMESTAMP_NTZ'
WHEN DATA_TYPE = 'DATE' THEN 'DATE'
WHEN DATA_TYPE = 'XMLTYPE' THEN 'VARIANT'
WHEN DATA_TYPE IN ('BINARY_DOUBLE','NUMBER') THEN 'NUMBER'||
CASE WHEN DATA_PRECISION IS NOT NULL THEN '('|| DATA_PRECISION || ',' || NVL(DATA_SCALE,0) || ')'
ELSE '' END
WHEN DATA_TYPE IN ('NVARCHAR2','VARCHAR2','VARCHAR','NVARCHAR') THEN 'TEXT(' || DATA_LENGTH || ')'
WHEN DATA_TYPE IN ('NCHAR','CHAR') THEN 'TEXT(1)'
WHEN DATA_TYPE = 'CLOB' THEN 'TEXT(16777216)'
ELSE NULL END AS CONVERTED_DATA_TYPE
,CASE WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_1' THEN 'SNOWFLAKE_OWNER_1' -- Map Oracle owners to Snowflake owners.
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_2' THEN 'SNOWFLAKE_OWNER_2'
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_3' THEN 'SNOWFLAKE_OWNER_3'
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_4' THEN 'SNOWFLAKE_OWNER_4'
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_5' THEN 'SNOWFLAKE_OWNER_5'
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_6' THEN 'SNOWFLAKE_OWNER_6'
ELSE NULL END AS NEW_SCHEMA_NAME
,COLUMN_ID
FROM ALL_TAB_COLUMNS
INNER JOIN ALL_TABLES
ON ALL_TABLES.TABLE_NAME = ALL_TAB_COLUMNS.TABLE_NAME
AND ALL_TABLES.OWNER = ALL_TAB_COLUMNS.OWNER
WHERE -- Insert a where clause to decide which tables to include or exclude. This is just part of one approach:
ALL_TABLES.TABLE_NAME NOT IN ('TABLE1','TABLE2','TABLE3')
)
GROUP BY NEW_SCHEMA_NAME, TABLE_NAME
SELECT TEXT_LINE FROM (
SELECT 1 AS INDEXING_VALUE,
TABLE_NAME ,
OWNER,
'CREATE OR REPLACE TABLE '
|| CASE WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_1' THEN 'SNOWFLAKE_OWNER_1' -- Map Oracle table ownwers to Snowflake table owners
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_2' THEN 'SNOWFLAKE_OWNER_2'
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_3' THEN 'SNOWFLAKE_OWNER_3'
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_4' THEN 'SNOWFLAKE_OWNER_4'
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_5' THEN 'SNOWFLAKE_OWNER_5'
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_6' THEN 'SNOWFLAKE_OWNER_6'
ELSE NULL END || '.' || TABLE_NAME || '(' AS TEXT_LINE, 1 AS COLUMN_ID
FROM ALL_TABLES
WHERE ALL_TABLES.OWNER LIKE 'ORACLE_OWNER_%' -- Set where condition for tables to process.
AND ALL_TABLES.TABLE_NAME IN ('TABLE1', 'TABLE2', 'TABLE3')
UNION ALL
SELECT 2 AS INDEXING_VALUE, ALL_TABLES.TABLE_NAME , ALL_TABLES.OWNER,
CASE WHEN COLUMN_ID = 1 THEN '' ELSE ',' END || COLUMN_NAME || ' ' ||
CASE WHEN INSTR(DATA_TYPE,'WITH LOCAL TIME ZONE') > 0 THEN 'TIMESTAMP_TZ'
WHEN INSTR(DATA_TYPE,'TIMESTAMP') > 0 THEN 'TIMESTAMP_NTZ'
WHEN DATA_TYPE = 'DATE' THEN 'DATE'
WHEN DATA_TYPE = 'XMLTYPE' THEN 'VARIANT'
WHEN DATA_TYPE IN ('BINARY_DOUBLE','NUMBER') THEN 'NUMBER'||
CASE WHEN DATA_PRECISION IS NOT NULL THEN '('|| DATA_PRECISION || ',' || NVL(DATA_SCALE,0) || ')'
ELSE '' END
WHEN DATA_TYPE IN ('NVARCHAR2','VARCHAR2','VARCHAR','NVARCHAR') THEN 'TEXT(' || DATA_LENGTH || ')'
WHEN DATA_TYPE IN ('NCHAR','CHAR') THEN 'TEXT(1)'
WHEN DATA_TYPE = 'CLOB' THEN 'TEXT(16777216)'
ELSE NULL END || CASE WHEN NULLABLE = 'N' THEN ' NOT NULL' ELSE ''END AS CONVERTED_DATA_TYPE
,COLUMN_ID
FROM ALL_TAB_COLUMNS
INNER JOIN ALL_TABLES
ON ALL_TABLES.TABLE_NAME = ALL_TAB_COLUMNS.TABLE_NAME
AND ALL_TABLES.OWNER = ALL_TAB_COLUMNS.OWNER
WHERE ALL_TABLES.OWNER LIKE 'ODS_%'
AND ALL_TABLES.TABLE_NAME IN ('TABLE1', 'TABLE2', 'TABLE3')
UNION ALL SELECT 3 AS INDEXING_VALUE,
TABLE_NAME , OWNER,
');', 1 AS COLUMN_ID
FROM ALL_TABLES
WHERE ALL_TABLES.OWNER LIKE 'ORACLE_OWNER_%'
AND ALL_TABLES.TABLE_NAME IN ('TABLE1', 'TABLE2', 'TABLE3')
)
ORDER BY TABLE_NAME, OWNER, INDEXING_VALUE, COLUMN_ID
SELECT 'ALTER TABLE '
|| CASE WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_1' THEN 'SNOWFLAKE_OWNER_1' -- Map Oracle table ownwers to Snowflake table owners
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_2' THEN 'SNOWFLAKE_OWNER_2'
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_3' THEN 'SNOWFLAKE_OWNER_3'
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_4' THEN 'SNOWFLAKE_OWNER_4'
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_5' THEN 'SNOWFLAKE_OWNER_5'
WHEN ALL_TABLES.OWNER = 'ORACLE_OWNER_6' THEN 'SNOWFLAKE_OWNER_6'
ELSE NULL END || '.' || TABLE_NAME
|| ' ADD PRIMARY KEY (' ||PK_COLUMMN_LIST || ');'
FROM
(
SELECT --'ALTER TABLE '
COLS.OWNER, COLS.TABLE_NAME, LISTAGG(cols.COLUMN_NAME , ',') WITHIN GROUP (ORDER BY POSITION) AS PK_COLUMMN_LIST
-- cols.table_name, cols.column_name, cols.POSITION, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cols.OWNER LIKE 'ORACLE_OWNER_%'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
GROUP BY COLS.OWNER, COLS.TABLE_NAME
)
Great script! Few tweaks …
1. Oracle DATE data type should be a Snowflake DATETIME (or TIMESTAMP) data type
2. Oracle needs a sql terminator character after each statement. (Either ‘;’ or ‘/’ on a new line)
3. Last statement does not work as written. Outer select needs to drop references to “ALL_TABLES.”
Thanks!
-brad
… and don’t forget about tables with column defaults!
🙂