title: Unable to insert string into Oracle DB if it has carriage return/new line characters
body: <p>I have a Java program which reads from the body of an email and inserts selected data into my Oracle 11g database. </p>
  
  <p>However, I have found I am getting this error upon attempting to insert the string of data into my Oracle DB: </p>
  
  <blockquote>
    <p>ORA-01461: can bind a LONG value only for insert into a LONG column</p>
  </blockquote>
  
  <p>The database column is of type VARCHAR2(4000) and I do not wish to change the datatype. </p>
  
  <p>I have set a character limit of 500, and if the string is longer than that, I'm using the <code>substring</code> method to cut the length of the string to 500. However, I've found that when the character limit is exceeded and I've used the substring method to cut it down to 500 characters, the ORA-01461 error appears if there is new line and carriage returns present in the string. </p>
  
  <p>I tried removing the new line and carriage return characters but strangely this did not stop this error from occurring, even though when debugging I could clearly see that the \r and \n characters had been removed from the string: </p>
  
  <pre><code>if(myString.length() &gt; 500)
  {
      myString= myString.substring(0, 500);
  }
  
  // Remove any carriage return and new lines
  myString= myString.replaceAll("[\n\r]", "");
  </code></pre>
  
  <p>When I exceed the character limit but there are no EOL or carriage return characters present in the string, it is inserted into the database with no issues. 
  When I don't exceed the character limit but there is EOL or carriage return characters present, it will also be inserted into the database with no issues. </p>
  
  <p>So the issue only seems to occurs when I exceed 500 characters for a string which contains EOL and carriage returns, even when I attempt to replace those characters before inserting into database. </p>
  
  <p>I'm using a parameterised <code>PreparedStatement</code> to insert the data, something like this: </p>
  
  <pre><code>preparedStatement.setString(10, myString);
  </code></pre>
  
  <p>The SQL is something like this, col10 is the column I'm having a problem with (not real column names): </p>
  
  <pre><code>INSERT INTO myDatabase (col1, col2, col3, col4, col5, col6, col7, col8, col9,col10,col11,col12) VALUES (?,?,?,?,?,?,?,TO_DATE(?,'YYYY-MM-DD HH24:MI:SS'),SYSTIMESTAMP,?,?,?)
  </code></pre>