1

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.

Improve this question
4
  • 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
    Commented Aug 21, 2014 at 6:45
  • Edited. I'm using sql developer.
    – Hero
    Commented Aug 21, 2014 at 7:34
  • I'm running SQL developer. Run the command: select * from table and from the query result right click and export.
    – Hero
    Commented Aug 21, 2014 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
    Commented Aug 21, 2014 at 11:35

1 Answer 1

Reset to default
-1

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.

HTH.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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