Bug #7096 Error on output with mysqldump when compatible=oracle
Submitted: 8 Dec 2004 6:07 Modified: 14 Apr 2005 9:31
Reporter: Michael Janos Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S3 (Non-critical)
Version:mysqldump 10.8 OS:Windows (win xp)
Assigned to: Elliot Murphy CPU Architecture:Any

[8 Dec 2004 6:07] Michael Janos
Description:
mysqlshow with compatible=oracle does not output code usable in oracle. The variable types are not translated an example line is 

"CONTRACT_ID" INT(11) NOT NULL DEFAULT '0' COMMENT 'Unique Contract Identifier',

instead of

"CONTRACT_ID" NUMBER(11) NOT NULL DEFAULT 0

and after the definition

COMMENT ON COLUMN AAA.CONTRACT_ID IS 'Unique Contract Identifier' 

How to repeat:
D:\GasNets>mysqldump --user=root --password=***** --compatible=oracle  

Suggested fix:
check for Oracle formatting
[1 Feb 2005 10:34] Guilhem Bichot
Hello,
Thank you for your bug report. Indeed, you pointed out that the output is not Oracle-compatible. It is also the case of the other --compatible modes (db2 etc). Fixing this requires finding a counterpart for each of the MySQL types (INT, ENUM etc) in the 5 following RDBMSs of --compatible: MaxDB, DB2, Oracle, SQL Server, PostgreSQL. We will work on that (it's not recorded in our internal work scheduling system) but cannot deliver quick results as this is a rather large task.
Until then, here is a clumsy workaround. Do the dump in two stages:
mysqldump --compatible=oracle --no-data > create.sql
mysqldump --compatible=oracle --no-create-info > data.sql
All CREATE TABLE will be put into create.sql, and all INSERT will be put into data.sql.
Then you will need to fix create.sql by hand (if your system has Perl or sed it could be automated in a script), fixing the "INT" and the "COMMENT".
We are sorry for the inconvenience - if it was an easy fix we would do it now.
Guilhem