How to pass BLOB to Oracle stored procedure

[Misha Kezherashvili]

Hello,

I am trying to store file into database using oracle stored procedure. I am using Struts.
here is the code.

  
FormFile file = form.getTheFile();

try
{

    oraConn.setAutoCommit( false );

    stmt = (OracleCallableStatement) oraConn.prepareCall( "begin " + Database.SCHEMA + "my_pkg.save_blob_test(?);end;" );

    stmt.setBinaryStream( 1, file.getInputStream(), file.getFileSize() );
    
    stmt.execute();
    stmt.close();
     
}
catch( Exception ex )
{
   ex.printStackTrace();
}
finally
{

    try
    {
        oraConn.commit();
    }
    catch( SQLException ex )
    {
        ex.printStackTrace();
    }
     
}

and here is the stored procedure

 procedure save_blob_test(b in out blob) is
  v_id int;
  
  begin
    select blob_test_gen.nextval into v_id from dual;
    
    insert into blob_test
      (id, file_blob)
    values
      (v_id, empty_blob())
    returning file_blob into b;
  end;

The record is inserted but the blob field is empty. Where did i go wrong?

[Misha Kezherashvili]

Found the answer.

Here is the working example:

 

FormFile file = form.getTheFile();  
   
 try  
 {  
   
     oraConn.setAutoCommit( false );  
   
     stmt = (OracleCallableStatement) oraConn.prepareCall( "begin " + Database.SCHEMA + "my_pkg.save_blob_test(?);end;" );  
   
     stmt.registerOutParameter( 1, OracleTypes.BLOB); 
     
     stmt.execute();
       
     BLOB b = stmt.getBLOB( 1 );
	 b.setBytes( 1, file.getFileData() );

	 oraConn.commit();
	 stmt.close();
        
 }  
 catch( Exception ex )  
 {  
    ex.printStackTrace();  
 }