title: Preserving line breaks when updating and reading from Oracle DB BLOB
body: <p>Here is what I am trying to do:</p>
  
  <p>-in Java, read a text file and create a <code>String</code> (preserving line breaks)</p>
  
  <p>-display that <code>String</code> in an SQL <code>BLOB</code> Update (or Insert) statement in the console (Eclipse), but only on one line (can't render the line breaks in the console)</p>
  
  <p>-copy/paste the SQL that printed in the console to Toad and execute</p>
  
  <p>then,</p>
  
  <p>-save the newly updated <code>BLOB</code> from Oracle to a file from Toad and have it render the line breaks</p>
  
  <p>Here is what i've tried so far:</p>
  
  <p>Here is where I read the file-</p>
  
  <pre><code>File file = new File(pathToFile);
  fileIn = new FileReader(pathToFile);
  BufferedReader br = new BufferedReader(fileIn);
  fileContents = new StringBuilder((int) file.length());
  String line;
  try {
      while ((line = br.readLine()) != null) {
      fileContents.append(line).append("\\n");
  } ...
  
  ...
  return fileContents.toString();
  </code></pre>
  
  <p>So far, so good.  I now have a <code>String</code> that I can put in an SQL statement.  So now I create the SQL statement like this-</p>
  
  <pre><code>String myStatement = "UPDATE ...... SET CONTENT = RAWTOHEX('" + myReturnedFileContentsString + "')...
  </code></pre>
  
  <p>I then print <code>myStatement</code> to the console so I can copy/paste the SQL into Toad (I know I can just programmitcally do it, but I need to do it this way).  The statement string prints correctly as one line with <code>\n</code> within the string.</p>
  
  <p>I then copy/paste the SQL into Toad, execute the command,... and it loads the <code>String</code> as a <code>BLOB</code> in the DB just fine.  </p>
  
  <p>Next, after refreshing the table, I save the <code>BLOB</code> to a text file, then open that file.  And... that's where I start failing.  The text renders with the <code>\n</code> in the text.  No new lines... it doesn't look like the file I read from to create the statement.  I've tried using <code>REGEXP_REPLACE</code> within the RAWTOHEX section to replace the <code>\n</code> with <code>\r\n</code>.  I've tried opening the file in different editors and so on...</p>
  
  <p>What am I missing?  Is this possible?  </p>
  
  <p>Any help is much appreciated.</p>
  
  <p><strong><em>EDIT</em></strong></p>
  
  <p>What I'm trying to accomplish:</p>
  
  <p>I have a table in Oracle DB that has the following 2 columns:</p>
  
  <p>-FILE_NAME (primary key)</p>
  
  <p>-CONTENT (BLOB)</p>
  
  <p>Let's just pretend like this table will only ever have one row.  I am writing a Java program that will grab the BLOB and covert it to a <code>String</code>.  Let's call that <code>String</code> String_Old.  Next, the program will read a file and convert its contents to a <code>String</code>.  Let's call that <code>String</code> String_New.  The program will then compare the two Strings, and if they are different, it will generate SQL to update the CONTENT column in the table with a new <code>Blob</code> created from String_New.  </p>
  
  <p>Let's pretend like if we converted the CONTENT Blob in the table right now to a <code>String</code>, it would be the value "a". Now, our file (text file) looks like this:</p>
  
  <pre><code>&lt;html&gt;
   &lt;body&gt;
    &lt;p&gt;Hi, I am Paragraph 1&lt;/p&gt;
    &lt;p&gt;Hi, I am Paragraph 2&lt;/p&gt;
   &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>So I need to read that file and convert it to a <code>String</code>, compare that to "a", and since it's different, I need to generate the SQL, and print it to the console (Eclipse), to update the CONTENT column for the row in the table with a new <code>Blob</code> created from String_New.</p>
  
  <p>So, if I use the approach to read the file that I put in my original answer, but append just <code>\n</code> after reading each line, compare, then create the SQL, I end up with this being printed to the console:</p>
  
  <pre><code>UPDATE table SET CONTENT = RAWTOHEX('&lt;html&gt;
   &lt;body&gt;
    &lt;p&gt;Hi, I am Paragraph 1&lt;/p&gt;
    &lt;p&gt;Hi, I am Paragraph 2&lt;/p&gt;
   &lt;/body&gt;
  &lt;/html&gt;
  ') WHERE ....
  </code></pre>
  
  <p>Okay, so let's ignore a requirement for now and roll with what we have.  So, I copy this output in the console, paste it in Toad, execute, and it updates the BLOB.  So far so good.</p>
  
  <p>Now, I need to be able to double click that BLOB in the table (within Toad) save it to a file, open the file, and see it as it appeared in the file we read in (it should have all the line breaks and tabs, spaces, etc.).</p>
  
  <p>Given the console output we just copy/pasted/executed, if I load that Blob to a file from the DB, it does indeed look the way I need it to.  So, if I was cool with the console output looking that way (with line breaks), I think I'm done.  However, I can't have the console SQL output have multiple lines.  I need that same SQL statement to look like this in the console (with whatever else might need to be there in order to accomplishment my requirement):</p>
  
  <pre><code>UPDATE table SET CONTENT = RAWTOHEX('&lt;html&gt;&lt;body&gt;&lt;p&gt;Hi, I am Paragraph 1&lt;/p&gt;&lt;p&gt;Hi, I am Paragraph 2&lt;/p&gt;&lt;/body&gt;&lt;/html&gt;') WHERE ...
  </code></pre>
  
  <p>Basically, it is all on one line in the console.  But I still need to be able to copy this SQL, paste/execute in Toad, and then when I save the Blob from the DB to a file and open it, look like the file I read in in the first place (ie, all spaces, tabs, line breaks, etc.)</p>
  
  <p>So now I have that 2 paragraph <code>HTML</code> stored as a Blob in the DB.  Now let's pretend I change my file to look like this:</p>
  
  <pre><code>&lt;html&gt;
   &lt;body&gt;
    &lt;p&gt;Hi, I am Paragraph 1&lt;/p&gt;
    &lt;p&gt;Hi, I am Paragraph 2&lt;/p&gt;
    &lt;p&gt;Hi, I am Paragraph 3&lt;/p&gt;
   &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>
  
  <p>I now need to be able to properly load the old 2 paragraph <code>HTML</code> from the DB, compare it to the file's new 3 paragraph <code>HTML</code>, and generate a single line SQL statement in Eclipse that will update the Blob in the DB and when loaded to file from DB preserves all breaks, spaces, tabs, etc.</p>
  
  <p>I hope this helps.  I am a bit vexed with this problem, so if I need to clarify any further, I will be more than happy to.</p>