title: Oracle JDBC charset and 4000 char limit
body: <p>We are trying to store an UTF-16 encoded String into an AL32UTF8 Oracle database.</p>
  
  <p>Our program works perfectly on a database that uses <code>WE8MSWIN1252</code> as charset. When we try to run it on a database that uses <code>AL32UTF8</code> it gets to a <code>java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column</code>.</p>
  
  <p>In the testcase below everything works fine as long as our input data doesn't get too long.</p>
  
  <p>The input String can exceed 4000 chars. We wish to retain as much information as possible, even though we realise the input will have to be cut off.</p>
  
  <p>Our database tables are defined using the <code>CHAR</code> keyword (see below). We hoped that this would allow us to store up to 4000 chars of any character set. <strong><em>Can this be done? If so, how?</em></strong></p>
  
  <p>We have tried converting the String to <code>UTF8</code> using a <code>ByteBuffer</code> without success. <code>OraclePreparedStatement.setFormOfUse(...)</code> also didn't help us out.</p>
  
  <p>Switching to a <code>CLOB</code> is not an option. If the string is too long it needs to be cut.</p>
  
  <p>This is our code at the moment:</p>
  
  <pre><code>public static void main(String[] args) throws Exception {
      String ip ="193.53.40.229";
      int port = 1521;
      String sid = "ora11";
      String username = "obasi";
      String password = "********";
  
      String driver = "oracle.jdbc.driver.OracleDriver";
      String url = "jdbc:oracle:thin:@" + ip + ":" + port + ":" + sid;
      Class.forName(driver);
  
      String shortData = "";
      String longData = "";
      String data;
  
      for (int i = 0; i &lt; 5; i++)
          shortData += "é";
  
      for (int i = 0; i &lt; 4000; i++)
          longData += "é";
  
      Connection conn = DriverManager.getConnection(url, username, password);
  
      PreparedStatement stat = null;
      try  {
          stat = conn.prepareStatement("insert into test_table_short values (?)");
          data = shortData.substring(0, Math.min(5, shortData.length()));
          stat.setString(1, data);
          stat.execute();
  
          stat = conn.prepareStatement("insert into test_table_long values (?)");
          data = longData.substring(0, Math.min(4000, longData.length()));
          stat.setString(1, data);
          stat.execute();
      } finally {
          try {
              stat.close();
          } catch (Exception ex){}
      }
  }
  </code></pre>
  
  <p>This is the create script of the simple table:</p>
  
  <pre><code>CREATE TABLE test_table_short (
      DATA    VARCHAR2(5 CHAR);
  );
  
  CREATE TABLE test_table_long (
      DATA    VARCHAR2(4000 CHAR);
  );
  </code></pre>
  
  <p>The test case works perfectly on the short data. On the long data however it keeps getting the error. Even when our <code>longData</code> is only 3000 characters long, it still doesn't execute successfully.</p>
  
  <p>Thanks in advance!</p>