Bug #55965 Special Character creating errors while inserting into oracle
Submitted: 13 Aug 2010 7:55 Modified: 15 Aug 2010 8:26
Reporter: Elizabeth Antoine Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Charsets Severity:S1 (Critical)
Version: OS:Windows
Assigned to: CPU Architecture:Any

[13 Aug 2010 7:55] Elizabeth Antoine 
Description:
I am trying to create a data file using mysqldump to import into the oracle tables. One of the columns in a table has an & in the data and creates an error while executing the insert statements saying

ERROR:
ORA-01756: quoted string not properly terminated.

For example, if the table name is contacts and the column name is contact description; the value 'Bill & Smith' comes up with

Enter value for Smith:

and the sqldump waits for the input.

I tried converting the & to 'and' using sed but it seems not to work for some of the cases like if the & comes at the end of the data (eg. 'Bill &'). Though I have specified --compatible=oracle; it does not seem to work. Is there a fix available for this?

How to repeat:
For example, if the table name is contacts and the column name is contact description; the value 'Bill & Smith' comes up with

Enter value for Smith:

and the sqldump waits for the input.
[13 Aug 2010 8:35] Elizabeth Antoine 
I am also getting ' character converted to \'. Can you please suggest some workaround for this.
[15 Aug 2010 8:26] Sveta Smirnova 
Thank you for the report.

According to http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html:

----
This option does not guarantee compatibility with other servers. It only enables those SQL mode values that are currently available for making dump output more compatible. For example, --compatible=oracle does not map data types to Oracle types or use Oracle comment syntax.
----

So this is not a bug.