Out of one Oracle into another

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'

There are three general comments to make:

• don't select columns you don't need (the select * is bothersome)

• 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.

• 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 t

StackExchange Code Review Q#68053, answer score: 4