Out of one Oracle into another

Question

A project that I have been working on required data to be transferred from an Oracle database into MySQL. The process I devised for that transfer involved a query (included below) on the Oracle source database to make it dump out the schema for the relevant tables to be replicated, in the form of CREATE TABLE statements to be executed in the MySQL destination database.

I'm also exporting the column names, parameter placeholders (a string of ?, ?, ?, ..., ?, with as many symbols as columns), and mysqli type codes in preparation for the next step, which is to allow each row to be INSERTed using mysqli.

I'm not particularly concerned about preserving constraints and foreign key relationships. Just enforcing the primary keys should suffice.

WITH constraints AS (
 SELECT cols.*, cons.constraint_type
 FROM all_constraints cons
 INNER JOIN all_cons_columns cols
 ON cols.constraint_name = cons.constraint_name
 AND cols.owner = cons.owner
 WHERE
 cons.status = 'ENABLED'
 AND cons.constraint_type IN ('P', 'U')
 ORDER BY cons.table_name, cols.position
), relevant_schema AS (
 SELECT *
 FROM all_tab_cols
), relevant_schema_mysql_types AS (
 SELECT relevant_schema.*
 , CASE data_type
 WHEN 'NUMBER' THEN 'NUMERIC(' || COALESCE(data_precision, 38) || ',' || COALESCE(data_scale, 0) || ')'
 WHEN 'VARCHAR2' THEN 'VARCHAR(' || data_length || ')'
 WHEN 'CHAR' THEN 'CHAR(' || data_length || ')'
 WHEN 'CLOB' THEN 'BLOB'
 WHEN 'TIMESTAMP(6)' THEN 'TIMESTAMP'
 ELSE data_type
 END
 ||
 CASE nullable
 WHEN 'N' THEN ' NOT NULL'
 END AS mysql_type
 , CASE data_type
 WHEN 'BLOB' THEN 'b'
 WHEN 'CLOB' THEN 'b'
 ELSE 's'
 END AS mysqli_placeholder_type
 FROM relevant_schema
), mysql_constraint_defs AS (
 SELECT owner
 , table_name
 , constraint_type
 , constraint_name
 , ', CONSTRAINT ' || constraint_name
 || CASE constraint_type
 WHEN 'P' THEN ' PRIMARY KEY'
 WHEN 'U' THEN ' UNIQUE'
 END
 || ' ('
 || LISTAGG('`' || column_name || '`', ', ') WITHIN GROUP (ORDER BY position)
 || ')'
 || CHR(10) AS constraint_def
 FROM constraints
 GROUP BY owner, table_name, constraint_type, constraint_name
), mysql_col_defs AS (
 SELECT owner
 , table_name
 -- , LISTAGG('`' || column_name || '`', ', ') WITHIN GROUP (ORDER BY column_id) AS col_names
 , RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, '`' || column_name || '`' || ', ') ORDER BY column_id),
 '/E/text()').getCLOBVal(), ', ') AS col_names
 , LISTAGG('?', ', ') WITHIN GROUP (ORDER BY column_id) AS col_placeholders
 , LISTAGG(mysqli_placeholder_type) WITHIN GROUP (ORDER BY column_id) AS mysqli_placeholder_types
 --, LISTAGG('`' || column_name || '`' || ' ' || mysql_type, CHR(10) || ', ') WITHIN GROUP (ORDER BY column_id) || CHR(10) AS col_defs
 , RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, '`' || column_name || '`' || ' ' || mysql_type, CHR(10) || ', ') ORDER BY column_id),
 '/E/text()').getCLOBVal(), ', ') AS col_defs
 FROM relevant_schema_mysql_types
 GROUP BY owner, table_name
), mysql_table_constraints AS (
 SELECT owner
 , table_name
 , LISTAGG(constraint_def, CHR(10)) WITHIN GROUP (ORDER BY constraint_type, constraint_name) AS constraints
 FROM mysql_constraint_defs
 GROUP BY owner, table_name
), mysql_defs AS (
 SELECT mysql_col_defs.owner
 , mysql_col_defs.table_name
 , mysql_col_defs.col_names
 , mysql_col_defs.col_placeholders
 , mysql_col_defs.mysqli_placeholder_types
 , mysql_col_defs.col_defs
 , mysql_table_constraints.constraints
 FROM mysql_col_defs
 LEFT OUTER JOIN mysql_table_constraints
 ON mysql_table_constraints.owner = mysql_col_defs.owner
 AND mysql_table_constraints.table_name = mysql_col_defs.table_name
)
SELECT table_name
 , col_names
 , col_placeholders
 , mysqli_placeholder_types
 , 'CREATE TABLE IF NOT EXISTS `' || table_name || '`' || CHR(10)
 || '( '
 || col_defs
 || constraints
 || ') ENGINE=INNODB;' AS create_table_stmt
 FROM mysql_defs
 WHERE
 owner = ...
 AND table_name IN (...)
 ORDER BY
 owner,
 table_name

Two of the LISTAGG() calls resulted in strings that exceeded Oracle's string length limit. I've used an ugly workaround involving XML serialization to generate CLOBs instead.

What would you do differently?

• cimmanon: Is "not convert to MySQL" a valid answer? :p
• 200_success: @cimmanon I wish it were, but the replication is necessary for integrating existing systems.

Answer 1

There are three general comments to make:

1. don't select columns you don't need (the select * is bothersome)
2. (削除) you should restrict the data much further before you start. The Oracle schema views are slow, and you should limit your schema selection in the first CTE, and then join that to the views for subsequent selections. (削除ここまで)
3. order-by clauses in the CTE are often unnecessary.

Originally I said the following, but I am going to retract that. It appears that, in Oracle, this does not help performance much at all.... in fact, it makes it worse.

Your relevant_schema and relevant_schema_mysql_types should be merged in to one CTE, the second is a pure extension of the first, and there's no other value add.

I messed about with your query, and came up with:

WITH relevant_schema AS (
 SELECT
 atc.owner
 , atc.table_name
 , atc.column_name
 , column_id
 , CASE data_type
 WHEN 'NUMBER' THEN 'NUMERIC(' || COALESCE(data_precision, 38) || ',' || COALESCE(data_scale, 0) || ')'
 WHEN 'VARCHAR2' THEN 'VARCHAR(' || data_length || ')'
 WHEN 'CHAR' THEN 'CHAR(' || data_length || ')'
 WHEN 'CLOB' THEN 'BLOB'
 WHEN 'TIMESTAMP(6)' THEN 'TIMESTAMP'
 ELSE data_type
 END
 ||
 CASE nullable
 WHEN 'N' THEN ' NOT NULL'
 END AS mysql_type
 , CASE data_type
 WHEN 'BLOB' THEN 'b'
 WHEN 'CLOB' THEN 'b'
 ELSE 's'
 END AS mysqli_placeholder_type
 FROM all_tab_cols atc
)
, mysql_col_defs AS (
 SELECT owner
 , table_name
 -- , LISTAGG('`' || column_name || '`', ', ') WITHIN GROUP (ORDER BY column_id) AS col_names
 , RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, '`' || column_name || '`' || ', ') ORDER BY column_id),
 '/E/text()').getCLOBVal(), ', ') AS col_names
 , LISTAGG('?', ', ') WITHIN GROUP (ORDER BY column_id) AS col_placeholders
 , LISTAGG(mysqli_placeholder_type) WITHIN GROUP (ORDER BY column_id) AS mysqli_placeholder_types
 --, LISTAGG('`' || column_name || '`' || ' ' || mysql_type, CHR(10) || ', ') WITHIN GROUP (ORDER BY column_id) || CHR(10) AS col_defs
 , RTRIM(EXTRACT(XMLAGG(XMLELEMENT(e, '`' || column_name || '`' || ' ' || mysql_type, CHR(10) || ', ') ORDER BY column_id),
 '/E/text()').getCLOBVal(), ', ') AS col_defs
 FROM relevant_schema
 GROUP BY owner, table_name
)
, constraints AS (
 SELECT cons.owner as owner,
 cons.table_name as table_name,
 cols.column_name,
 cols.position,
 cons.constraint_type,
 cons.constraint_name
 FROM all_constraints cons 
 INNER JOIN all_cons_columns cols
 ON cols.constraint_name = cons.constraint_name
 AND cols.owner = cons.owner
 AND cols.position is not null
 WHERE cons.status = 'ENABLED'
 AND cons.constraint_type IN ('P', 'U')
)
, mysql_constraint_defs AS (
 SELECT owner
 , table_name
 , constraint_type
 , constraint_name
 , ', CONSTRAINT ' || constraint_name
 || CASE constraint_type
 WHEN 'P' THEN ' PRIMARY KEY'
 WHEN 'U' THEN ' UNIQUE'
 END
 || ' ('
 || LISTAGG('`' || column_name || '`', ', ') WITHIN GROUP (ORDER BY position)
 || ')'
 || CHR(10) AS constraint_def
 FROM constraints
 GROUP BY owner, table_name, constraint_type, constraint_name
)
, mysql_table_constraints AS (
 SELECT owner
 , table_name
 , LISTAGG(constraint_def, CHR(10)) WITHIN GROUP (ORDER BY constraint_type, constraint_name) AS constraints
 FROM mysql_constraint_defs
 GROUP BY owner, table_name
)
, mysql_defs AS (
 SELECT mysql_col_defs.owner
 , mysql_col_defs.table_name
 , mysql_col_defs.col_names
 , mysql_col_defs.col_placeholders
 , mysql_col_defs.mysqli_placeholder_types
 , mysql_col_defs.col_defs
 , mysql_table_constraints.constraints
 FROM mysql_col_defs
 LEFT OUTER JOIN mysql_table_constraints
 ON mysql_table_constraints.owner = mysql_col_defs.owner
 AND mysql_table_constraints.table_name = mysql_col_defs.table_name
)
SELECT table_name
 , col_names
 , col_placeholders
 , mysqli_placeholder_types
 , 'CREATE TABLE IF NOT EXISTS `' || table_name || '`' || CHR(10)
 || '( '
 || col_defs
 || constraints
 || ') ENGINE=INNODB;' AS create_table_stmt
 FROM mysql_defs
 where table_name in ('HEADER', 'DETAIL')
 and owner = (select user from dual)
 ORDER BY
 owner,
 table_name

Note that I put the column level items first, followed by the constraints. I removed the one redundant CTE. I put the colum level queries first because that's the order of building the create-table and I feel that makes the query flow better.

I got the query working in the following SQLFiddle.

As for the XML hack, this is a common hack to need when confronted by your circumstances. The right solution is to remove this logic entirely from the query.... and process the data in either a cursor, or in a third-party application. SQL is not designed to solve the problem you are throwing at it, and you are thus running in to its brittle components.

Pull the data in to a tool like Java, python, whatever, and process it better that way.

• rolfl: Updated with working SQLFiddle, and revised suggestion about CTE.

Answer 2

One thing you'll need to be aware of is that MySQL can't guarantee uniqueness on columns over a certain size, depending on the engine type. Attempting to create a unique constraint on a column that's bigger than the limit will cause MySQL to throw an error. You can specify a length when defining the constraint like so:

UNIQUE (`column_name`(100))

Not so important on numeric type columns, but very relevant to larger types like TEXT or even VARCHAR.

See also: https://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length