Reading a BLOB using JDBC Spring without a result set

Question

I have an Oracle stored procedure that returns a BLOB in an output parameter:

PROCEDURE GET_IMAGE_DATA(i_image_type IN NUMBER, o_image_data OUT BLOB) IS
BEGIN
        SELECT IMAGE_DATA 
         INTO o_image_data 
         FROM IMAGES 
        WHERE IMAGE_TYPE = i_image_type;

END GET_IMAGE_DATA;

I want to use JDBC Spring to read this data. However, DefaultLobHandler (and I think OracleLobHandler) getBlobAsBytes() requires a resultset.

private static class QueryForBinaryCryptKey extends StoredProcedure {

     private static final String SQL = "IMAGE_PKG.GET_IMAGE_DATA";
     private DefaultLobHandler lobHandler;

     QueryForImageData(DataSource dataSource) {
      super(dataSource, SQL);
      setFunction(false);
      lobHandler = new DefaultLobHandler();

      declareParameter(new SqlParameter(KEY_TYPE, OracleTypes.NUMBER));
      declareParameter(new SqlOutParameter(KEY_BLOB, OracleTypes.BLOB));

     }

     public Map getImage(int keyType) {
      Map outParams = super.execute(inParams(keyType));
                    //how can I get the contents of the blob right here since
                    //getBlobAsBytes requires a resultSet???
      return outParams;
     }

     private Map inParams(int keyType) {
      Map params = new HashMap();
      params.put(KEY_TYPE, new Integer(keyType));
      return params;
     } 
    }

How can I get the blob data when all I have is an out parameter and not a resultset?

Answer 1

Maybe this link can help you.

Answer 2

The JDBC Spring API (DefaultLobHandler and OracleLobHandler) require a ResultSet object for their BLOB related methods.

You need to convert your GET_IMAGE_DATA procedure into a function:

FUNCTION ATTACHMENT_BLOB_GET(IN_IMAGE_TYPE IN IMAGES.IMAGE_TYPE%TYPE)
RETURN SYS_REFCURSOR AS

results_cursor SYS_REFCURSOR;

BEGIN

  OPEN results_cursor FOR
     SELECT t.image_data 
       FROM IMAGES t
      WHERE t.image_type = IN_IMAGE_TYPE;

  RETURN results_cursor;

END;

OUT parameters are always good for some grief, BLOBs especially.