java - Read Blob from PL/SQL through Spring

I am trying to retrieve a Blob value thorugh PL/SQL, Spring and JDBC.

Here is my PL/SQL

function GETBLOB(pjobid in number)
RETURN bobrecCur
vbobrecCur bobrecCur;
   OPEN vbobrecCur FOR
   SELECT jobid, filecontent
   WHERE jobid = pjobid;
   RETURN vbobrecCur;

And my Java code is

this.getDataJdbcCall =
            new SimpleJdbcCall( this.jdbcTemplate )
                    .withFunctionName(  SQL_READ_DATA )
                            new SqlOutParameter( "abc", OracleTypes.CURSOR ),
                            new SqlParameter( "pjobid", OracleTypes.INTEGER )

Map input = new HashMap();
    input.put( "pjobid", 99999 );

    ResultSet result = this.getDataJdbcCall.executeFunction(ResultSet.class , input );
    DefaultLobHandler lob =  new DefaultLobHandler();
    InputStream is = lob.getBlobAsBinaryStream( result, 1 );

I am getting the following exception.. basically saying that Resultset is null.

Exception in thread "main" java.lang.NullPointerException at org.springframework.jdbc.core.RowMapperResultSetExtractor.extractData( at org.springframework.jdbc.core.JdbcTemplate.processResultSet( at org.springframework.jdbc.core.JdbcTemplate.extractOutputParameters( at org.springframework.jdbc.core.JdbcTemplate$5.doInCallableStatement( at org.springframework.jdbc.core.JdbcTemplate.execute( at at org.springframework.jdbc.core.simple.AbstractJdbcCall.executeCallInternal( at org.springframework.jdbc.core.simple.AbstractJdbcCall.doExecute( at org.springframework.jdbc.core.simple.SimpleJdbcCall.executeFunction(

I went through this question which should work for me. But I think the way I am using OracleLobHandler is not right.

Can anybody shed any light on where I am going wrong?

2 Answers

  1. Leander- Reply


    Never mind, I figured out how to do it with a BLOB return type rather than a Cursor.

    CREATE OR REPLACE function GETDATA(pjobid in number)
        SELECT filecontent into pblob
        from TESTDATA
        where jobid = pjobid;
        return pblob;

    And in Java I did this

    this.getDataJdbcCall =
                new SimpleJdbcCall( this.jdbcTemplate )
                        .withFunctionName( SQL_READ_DATA )
                        .declareParameters( new SqlOutParameter( "abc", OracleTypes.BLOB ),
                                new SqlParameter( "pjobid", OracleTypes.INTEGER ) );
        System.out.println( "Reading data" );
        Map input = new HashMap();
        input.put( "pjobid", 99999 );
        Blob result = this.getDataJdbcCall.executeFunction( Blob.class, input );
        InputStream is = result.getBinaryStream();
        byte[] b = new byte[1000];
        while ( true ) {
            if ( b ) == -1 )
            System.out.print( new String( b ) );
  2. Lee- Reply


    package test;
    import java.util.List;
    import java.util.Map;
    import org.springframework.jdbc.core.JdbcTemplate;
    import org.springframework.jdbc.datasource.DriverManagerDataSource;
    public class SelectBlobBug {
        public static void main(String[] args) {
            JdbcTemplate jdbcTemplate = new JdbcTemplate(new DriverManagerDataSource("jdbc:derby:test-db;create=true"));
            jdbcTemplate.execute("DROP TABLE blob_test");
            jdbcTemplate.execute("CREATE TABLE blob_test (DATA BLOB NOT NULL)");
            byte[] binaryData = new byte[32700];
            for (int i = 0; i < binaryData.length; i++) {
                binaryData[i] = (byte) i;
            jdbcTemplate.update("INSERT INTO blob_test VALUES (?)", binaryData);
            List<Map<String, Object>> result = jdbcTemplate.queryForList("SELECT * FROM blob_test");
            System.out.println(((byte[]) result.get(0).get("DATA")).length); // should be 32700

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>