title: Pass Oracle clobs from and to Java
body: <p>I am writing a piece of code to apply a very customer specific encoding to XML data. At first I tried to do this using only PL-SQL, but ran into very poor performance (1 hour for 160 records). After that I tried it with only java, wich gave great performance (400.000 records in 1 minute)</p>
  
  <p>Now I want to see how the performance is when I build the code in Java and load it into the database.</p>
  
  <p>For that I have the following code:</p>
  
  <pre><code>create or replace and compile java source named XmlNumericEncoder as
  public class XmlNumericEncoder {
      public static String encode(String xmlstring) { 
          String xmlstring_out = "";
          for (int i = 0; i &lt; xmlstring.length(); i++){
              char ch = xmlstring.charAt(i);        
  
              if ((int)ch  &gt;=  192 &amp;&amp; (int)ch &lt;= 255){
                 xmlstring_out += "&amp;#" + (int)ch + ";"; 
              } else {
                 xmlstring_out += ch;
              }
          }
          return xmlstring_out;
      } 
  }
  
  
  
  CREATE OR REPLACE FUNCTION XmlNumericEncoder(xmlstring varchar2)
          RETURN String
          AS LANGUAGE JAVA
          NAME 'XmlNumericEncoder.encode(java.lang.String) return java.lang.String';
  </code></pre>
  
  <p>This code can be executed the following way: </p>
  
  <pre><code>select XmlNumericEncoder('Böhmer') from dual;
  </code></pre>
  
  <p>The problem I'm now facing is that I need the function to execute on larger parts of data, stored in clobs. I know there is a java.sql.Clob class, and I can get it to work as a input parameter, but I can't seem to get it to work as a output parameter, I keep running into nullpointer exceptions.</p>
  
  <p>This is the code I was working with:</p>
  
  <pre><code>import java.sql.Clob;
  import java.sql.SQLException;
  
  public class XmlNumericEncoder {
      public static Clob encode(Clob xmlclob) throws SQLException { 
          String xmlstring_out = "";
          Clob xmlclob_out = null;
          for (int i = 0; i &lt; xmlclob.length(); i++){
              char ch = xmlclob.getSubString(i,1).charAt(1);        
  
              if ((int)ch  &gt;=  192 &amp;&amp; (int)ch &lt;= 255){
                 xmlstring_out += "&amp;#" + (int)ch + ";"; 
              } else {
                 xmlstring_out += ch;
              }
          }
          int setString = xmlclob_out.setString(0, xmlstring_out);
  
          return xmlclob_out;
      } 
  </code></pre>
  
  <p>Any ideas where I'm going wrong?</p>