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:
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();
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.
This is what I have to do:
MapSqlParameterSource paramSource = new MapSqlParameterSource();
paramSource.addValue(BLOB_NAME, blob);
CustomStoredProcedure proc = new CustomStoredProcedure( dataSource, storedProcedure);
proc.execute(paramSource);
In CustomStoredProcedure, which extends org.springframework.jdbc.object.StoredProcedure, I have to associate to BLOB_NAME an Oracle Data Type, in this way:
public CustomStoredProcedure(DataSource dataSource, String sprocName) {
super(dataSource, sprocName);
declareParameter(new SqlParameter(BLOB_NAME, OracleTypes.BLOB));
compile();
}
Now, here's the problems:
java.sql.SQLFeatureNotSupportedException: DSRA1300E: Function not implemented: Connection.createBlob
(the description may be incorrect but I have the localized version and I translated it in English).
java.lang.ClassCastException: com.ibm.ws.rsadapter.jdbc.WSJdbcConnection incompatible with oracle.jdbc.OracleConnection
If I use
com.ibm.websphere.rsadapter.WSCallHelper.getNativeConnection( dataSource.getConnection)
I receive the compiling error (only with Ant, with Eclipse I have no problem):
error: package com.ibm.websphere.rsadapter not exists [javac] import com.ibm.websphere.rsadapter.WSCallHelper
(as before, I translated the error)
[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)...
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).
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;
To get this I've used the code written before creating the blob object in this way:
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());
Any suggestions?
Problem solved: I do not need to use Blob object: I can use simple byte[] on Java-side, put it in paramSource and bind that array with OracleTypes.BINARY. That works.