Out of one Oracle into another

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.