When I take a dump of a particular table which has CLOB data type, the resulting dump doesn't handle escaping of special characters.

I get ' un escaped, so insert statements won't work.

Any ways to fix it?

I'm right clicking on the query result and getting the dump.

I'm using SQL Developer and am new to Oracle.

  • 1
    What does "take a dump" mean in this context? A "dump file" is a common term for the output of the Oracle export utility, either the regular version or the DataPump version. If you are using the Oracle export utility, however, the question doesn't make sense. That leads me to believe that you are using some other tool to generate some other sort of file. – Justin Cave Aug 21 '14 at 6:45
  • Edited. I'm using sql developer. – Hero Aug 21 '14 at 7:34
  • I'm running SQL developer. Run the command: select * from table and from the query result right click and export. – Hero Aug 21 '14 at 9:39
  • 1
    A 'dump' as you mention or 'query output' is not supposed to be used directly used in a insert statement. It is 'query output' not an insert statement. You have to do the escaping yourself (use '' for each ' in the output). Or if you feel super lazy when you export, choose the option to export as 'insert statement'. – Raj Aug 21 '14 at 11:35

As someone who spent 4 years of his life dealing with converting CLOB type data from legacy systems into Oracle, I feel your pain.

I highly recommend using an Oracle built function, such as REPLACE to go through and scrub all of the garbage characters from CLOB type fields. Additionally, remember that most systems consider a "return" to be a new line and carriage return in unison, so you might have to scrub ascii characters below 33. IIRC the new line/carriage return combo was char(10)char(13). You can easily google a list of of the "invisible" ascii characters, and hunt down the ones you want removed.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.