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_nameTwo 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?
Out of one Oracle into another
sql;mysql;oracle
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_nameNote 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.