title: What is the best way to encrypt a clob?
body: <p>I am using Oracle 9 and JDBC and would like to encyrpt a clob as it is inserted into the DB.  Ideally I'd like to be able to just insert the plaintext and have it encrypted by a stored procedure:</p>
  
  <pre><code>String SQL = "INSERT INTO table (ID, VALUE) values (?, encrypt(?))";
  PreparedStatement ps = connection.prepareStatement(SQL);
  ps.setInt(id);
  ps.setString(plaintext);
  ps.executeUpdate();
  </code></pre>
  
  <p>The plaintext is not expected to exceed 4000 characters but encrypting makes text longer.  Our current approach to encryption uses dbms_obfuscation_toolkit.DESEncrypt() but we only process varchars.  Will the following work?</p>
  
  <pre><code>FUNCTION encrypt(p_clob IN CLOB) RETURN CLOB
  IS
      encrypted_string        CLOB;
      v_string                CLOB;
  BEGIN
    dbms_lob.createtemporary(encrypted_string, TRUE);
    v_string := p_clob;
    dbms_obfuscation_toolkit.DESEncrypt(
      input_string =&gt; v_string,
      key_string =&gt; key_string,
      encrypted_string =&gt; encrypted_string );
    RETURN UTL_RAW.CAST_TO_RAW(encrypted_string);
  END;
  </code></pre>
  
  <p>I'm confused about the temporary clob; do I need to close it?  Or am I totally off-track?</p>
  
  <p>Edit:
  The purpose of the obfuscation is to prevent trivial access to the data.  My other purpose is to obfuscate clobs in the same way that we are already obfuscating the varchar columns.  The oracle sample code does not deal with clobs which is where my specific problem lies; encrypting varchars (smaller than 2000 chars) is straightforward.</p>