title: Spring Send Blob to Stored Procedure
body: <p>I'm trying to send a Blob to a stored procedure without success.
  First of all, I had some limitations, i. e. I cannot use a prepared statement.
  Secondly, I'm sure that the stored procedure works, because I've tried it with success calling manually the procedure and with prepared statement.
  If I execute this all works correctly:</p>
  
  <pre><code>PreparedStatement ps = con.prepareStatement("call t1000_adm.Mandamail.manda_mail(?)");
  file = new File(request.getPathAllegato());
  InputStream fis = new FileInputStream(file);
  ps.setBinaryStream(1, fis, (int)file.length());
  ps.execute();
  </code></pre>
  
  <p>But I'm forced to use Spring MVC with WebSphere application server and I cannot use directly a prepared statement.
  I have to use a javax.sql.DataSource and org.springframework.jdbc.core.namedparam.MapSqlParameterSource.</p>
  
  <p>This is what I have to do:</p>
  
  <pre><code>MapSqlParameterSource paramSource = new MapSqlParameterSource();
  paramSource.addValue(BLOB_NAME, blob);
  CustomStoredProcedure proc = new CustomStoredProcedure( dataSource, storedProcedure); 
  proc.execute(paramSource);
  </code></pre>
  
  <p>In CustomStoredProcedure, wich extends org.springframework.jdbc.object.StoredProcedure, I have to associate to BLOB_NAME an Oracle Data Type, in this way:</p>
  
  <pre><code>public CustomStoredProcedure(DataSource dataSource, String sprocName) {
      super(dataSource, sprocName);
      declareParameter(new SqlParameter(BLOB_NAME, OracleTypes.BLOB));
      compile();
  }
  </code></pre>
  
  <p>Now, here's the problems:</p>
  
  <p>1) If I try to create the blob object with dataSource.getConnection().createBlob() I receive the error:</p>
  
  <pre><code>java.sql.SQLFeatureNotSupportedException: DSRA1300E: Function not implemented: Connection.createBlob 
  </code></pre>
  
  <p>(the description may be incorrect but I have the localized version and I translated it in english).</p>
  
  <p>2) If I try to create the blob object with BLOB.createTemporary I receive the error </p>
  
  <pre><code>java.lang.ClassCastException: com.ibm.ws.rsadapter.jdbc.WSJdbcConnection incompatible with oracle.jdbc.OracleConnection
  </code></pre>
  
  <p>3) If I use </p>
  
  <pre><code>com.ibm.websphere.rsadapter.WSCallHelper.getNativeConnection( dataSource.getConnection)
  </code></pre>
  
  <p>I receive the compiling error (only with Ant, with Eclipse I have no problem):</p>
  
  <pre><code>error: package com.ibm.websphere.rsadapter not exists
  [javac] import com.ibm.websphere.rsadapter.WSCallHelper
  </code></pre>
  
  <p>(as before, I translated the error)</p>
  
  <p>4) If I ignore the Ant compiling error and use it anyway I receive the execution error:</p>
  
  <pre><code>[31/05/17 9.52.50:414 CEST] 0000007f SystemErr     R org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{call MANDAMAIL.MANDA_MAIL(?)}]; SQL state [99999]; error code [22922]; ORA-22922: valore LOB non esistente
  ORA-06512: a "SYS.DBMS_LOB", line 837
  ORA-06512: a "T1000_ADM.MANDAMAIL", line 190
  ORA-06512: a line 1
  ; nested exception is java.sql.SQLException: ORA-22922: valore LOB non esistente
  ORA-06512: a "SYS.DBMS_LOB", line 837
  ORA-06512: a "T1000_ADM.MANDAMAIL", line 190
  ORA-06512: a line 1
  
  [31/05/17 9.52.50:415 CEST] 0000007f SystemErr     R    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:83)
  [31/05/17 9.52.50:415 CEST] 0000007f SystemErr     R    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
  [31/05/17 9.52.50:416 CEST] 0000007f SystemErr     R    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:80)
  [31/05/17 9.52.50:416 CEST] 0000007f SystemErr     R    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1030)
  [31/05/17 9.52.50:416 CEST] 0000007f SystemErr     R    at org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1064)
  [31/05/17 9.52.50:417 CEST] 0000007f SystemErr     R    at org.springframework.jdbc.object.StoredProcedure.execute(StoredProcedure.java:144)
  [31/05/17 9.52.50:417 CEST] 0000007f SystemErr     R    at com.bpvn.vieniconnoi.dao.MandaMailStoredProcedure.execute(MandaMailStoredProcedure.java:42)...
  </code></pre>
  
  <p>The line 190 of the procedure is the first occurrance of DBMS_LOB.
  This is the stored procedure (in Java code I've removed other params for simplicity).</p>
  
  <pre><code>PROCEDURE manda_mail(   destinatario          IN VARCHAR2,
                          cc                    IN VARCHAR2,
                          mittente              IN VARCHAR2,
                          oggetto               IN VARCHAR2,
                          corpo                 IN VARCHAR2 DEFAULT NULL,
                          nomeAllegato          IN VARCHAR2 DEFAULT NULL,
                          allegato              IN BLOB DEFAULT NULL)
  AS
    l_mail_conn   UTL_SMTP.connection;
    l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
    l_step        PLS_INTEGER  := 12000; -- make sure you set a multiple of 3 not higher than 24573
    v_mime VARCHAR2(50) := 'application/pdf';
  BEGIN
    l_mail_conn := UTL_SMTP.open_connection(C_SMTP_SERVER, 25);
    UTL_SMTP.helo(l_mail_conn, C_SMTP_SERVER);
    UTL_SMTP.mail(l_mail_conn, mittente);
    UTL_SMTP.rcpt(l_mail_conn, destinatario);
  
    UTL_SMTP.open_data(l_mail_conn);
  
    UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'To: ' || destinatario || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'From: ' || mittente || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || oggetto || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || mittente || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
    UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/mixed; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
  
    IF corpo IS NOT NULL THEN
      UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
      UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/plain; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);
  
      UTL_SMTP.write_data(l_mail_conn, corpo);
      UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
    END IF;
  
    IF nomeAllegato IS NOT NULL THEN
      UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
      UTL_SMTP.write_data(l_mail_conn, 'Content-Type: ' || v_mime || '; name="' || nomeAllegato || '"' || UTL_TCP.crlf);
      UTL_SMTP.write_data(l_mail_conn, 'Content-Transfer-Encoding: base64' || UTL_TCP.crlf);
      UTL_SMTP.write_data(l_mail_conn, 'Content-Disposition: attachment; filename="' || nomeAllegato || '"' || UTL_TCP.crlf || UTL_TCP.crlf);
  
      FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(allegato) - 1 )/l_step) LOOP
        UTL_SMTP.write_data(l_mail_conn, UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(allegato, l_step, i * l_step + 1))));
      END LOOP;
  
      UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);
    END IF;
  
    UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
    UTL_SMTP.close_data(l_mail_conn);
  
    UTL_SMTP.quit(l_mail_conn);
  END;
  </code></pre>
  
  <p>To get this I've used the code written before creating the blob object in this way:</p>
  
  <pre><code>BLOB blob = BLOB.createTemporary(WSCallHelper.getNativeConnection(dataSource.getConnection()), false, BLOB.DURATION_SESSION);
  FileInputStream is = new FileInputStream(filePath);
  byte[] buffer = new byte[30000000];
  ByteArrayOutputStream baos = new ByteArrayOutputStream();
  
  int bytesRead;
  while ((bytesRead = is.read(buffer)) != -1) {
      baos.write(buffer, 0, bytesRead);
  }
  
  blob.setBytes(1, baos.toByteArray());       
  </code></pre>
  
  <p>Any suggestions?</p>