CODEWITHSUNDEEP
oracle-databaseunicodeoracle10g
AllOutOfSalt
AllOutOfSalt

Reputation: 1492

Replace invalid character in oracle (by editing dmp file)

We have a portal written in php/mysql and an enterprise application based on Java EE and Oracle. Recently we found out that a certain Unicode character (0643 to be precise) is invalid (due to improper data entry by end users) in text columns and must be changed to another character (06A9).

In MySQL I simply changed the export file using a text editor's find and replace tool. But in oracle, the dmp file is a binary file and i have no idea about how to edit the dmp file.

How can I change the invalid character?

Is there an alternative to iterating through all text columns in all tables? (I have saved that as a last resort!)

Upvotes: 3

Views: 784

Answers (1)

Alex Poole
Alex Poole

Reputation: 191560

Editing an Oracle dump file may be possible but isn't practical; even if you could get in and change something you'd risk corrupting it, and I doubt Oracle support would be impressed. (See this AskTom question for example).

If you're using data pump and you know which column(s) the data is in you might be able to use the REMAP_DATA parameter to change it on the fly, or the QUERY parameter to skip the data, but it doesn't sound like you're in that situation. You could potentially add temporary constraints to the relevant column(s) to block the value, so import would reject (and log) the affected rows, but that's painful and messy.

If you do have to check all columns on all tables, this link may be helpful.

Upvotes: 2

Related Questions