title: Oracle 8i Replace Linefeed characters in CLOB
body: <p>I have a CLOB which I'm trying to replace the linefeed chars in with <code>\r</code> or <code>\n</code> as appropriate. This is because I'm using SQL*Plus to export the data and it exports the linefeeds raw meaning my parser looking at the whitespace formatting of the output to determine field values does not work.</p>
  
  <p>I tried the command like so:</p>
  
  <p><code>SELECT REPLACE(DESCRIPTION, chr(10), '\n') FROM ORDESCRIPTION;</code></p>
  
  <p>But I get:</p>
  
  <p><code>ORA-00932: inconsistent datatypes</code></p>
  
  <p>Data:</p>
  
  <pre><code>SQL&gt; select DESCRIPTION from ORDESCRIPTION where or_id = 'FOO/BAR/OR_000002';
  
  DESCRIPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
  --------------------------------------------------------------------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
  1)  Why don't you take this and shove it
  
  
  SQL&gt; spool off
  </code></pre>
  
  <p>The field actually contains more data as revealed by a slightly better database browser GUI:</p>
  
  <pre><code>1)  Why don't you take this and shove it
  
  2)  This section is particularly crap
  
  3)   Do something
  </code></pre>