oracle - How to read and extract zip entries from java.sql.Blob type zip file without having FileInputStream or filepath as a string java

Question

public static void unzipFiles(java.sql.Blob zip) throws Exception{
 String paths = "";
 byte[] blobAsBytes = zip.getBytes(1, (int) zip.length());
 ZipInputStream zis = new ZipInputStream(zip.getBinaryStream(), StandardCharsets.UTF_8);
 ZipEntry zipEntry = null;
 while ((zipEntry = zis.getNextEntry()) != null) {
      paths=zipEntry.getName()+" ";
 }
 Connection conn = DriverManager.getConnection("jdbc:default:connection:");
 String sql = "INSERT INTO E (FILENAME) VALUES (:paths)";
 PreparedStatement pstmt = conn.prepareStatement(sql);
 pstmt.setString(1, paths);
 pstmt.executeUpdate();}

I'm trying to pass a zip file (zip) as java.sql.Blob to the java method from pl/sql and read file names and contents inside the files in the zip. I want to retrieve them as Zip entries and then insert them to a temporary table in the oracle database. But the issue is when I convert the blob into a byte array it doesn't take it as zip entries. returns null. Any solution would be really appreciated.

Answer 1

This is a more generic solution than just zip files (also Java's native ZIP support does not handle all ZIP formats [1]).

Use the loadjava utility to load commons-compress and it's dependency xz:

loadjava -user USERNAME/PASSWORD@SID xz.jar commons-compress-1.10.jar

(Update as needed for newer versions)

Then you can create a Java source inside the database:

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED UNZIP AS
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import org.apache.commons.compress.archivers.ArchiveException;
import org.apache.commons.compress.archivers.ArchiveInputStream;
import org.apache.commons.compress.archivers.ArchiveStreamFactory;
import org.apache.commons.compress.archivers.ArchiveEntry;
import oracle.jdbc.driver.OracleDriver;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.BLOB;
import org.apache.commons.compress.compressors.CompressorException;
import org.apache.commons.compress.compressors.CompressorStreamFactory;

public class Unzip {
  public static ARRAY listFiles(
      final BLOB file
  )
  throws java.sql.SQLException
  {
    final ArrayList<String> files = new ArrayList<String>();
    if ( file != null &&  file.length() > 0 )
    {
      ArchiveInputStream ais = null;
      try
      {
        InputStream stream = file.binaryStreamValue();
        try {
          stream = new CompressorStreamFactory().createCompressorInputStream( stream );
        }
        catch ( CompressorException e ){}
        ais = new ArchiveStreamFactory().createArchiveInputStream( stream );
        ArchiveEntry entry;
        while( ( entry = ais.getNextEntry() ) != null )
        {
          if ( entry.isDirectory() || entry.getSize() == 0 )
            continue;
          files.add( entry.getName() );
        }
      }
      catch ( ArchiveException e ){
        files.add( "ERROR: " + e.getMessage() );
      }
      catch ( IOException e ){
        files.add( "ERROR: " + e.getMessage() );
      }
      finally
      {
        try { if ( ais != null ) ais.close(); } catch( IOException e ){}
      }
    }

    final String[] fs = new String[ files.size() ];
    files.toArray( fs );
    final Connection conn = new OracleDriver().defaultConnection();
    return new ARRAY(
      ArrayDescriptor.createDescriptor( "SYS.ODCIVARCHAR2LIST", conn ),
      conn,
      fs
    );      
  }

  public static BLOB unzip(
    final BLOB file,
    final String path
  )
  throws java.sql.SQLException
  {
    ArchiveInputStream ais = null;
    BLOB extractedFile     = null;
    Connection conn        = null;
    if ( file != null && file.length() > 0 && path != null )
    {
      try {
        InputStream stream = file.binaryStreamValue();
        try {
          stream = new CompressorStreamFactory().createCompressorInputStream( stream );
        }
        catch ( CompressorException e ){}
        ais = new ArchiveStreamFactory().createArchiveInputStream( stream );
        ArchiveEntry entry;
        while( ( entry = ais.getNextEntry() ) != null ){
          if ( !entry.getName().equals( path ) )
            continue;

          final byte[] bytes = new byte[8096];
          long pos = 1;
          int len;

          conn = new OracleDriver().defaultConnection();
          extractedFile = BLOB.createTemporary( conn, false, BLOB.DURATION_CALL );

          while ( ( len = ais.read( bytes ) ) > 0 ) {
            extractedFile.setBytes( pos, bytes, 0, len );
            pos += len;
          }
          break;
        }
      }
      catch ( final ArchiveException e ){}
      catch ( final IOException e ){}
      finally
      {
        try { if ( ais != null ) ais.close(); } catch( final IOException e ){}
        try { if ( conn != null ) conn.close(); } catch( final SQLException e ){}
      }
    }
    return extractedFile;
  }
}
/
SHOW ERRORS;
/

(Add in appropriate exception handling code as required.)

Then you can create wrapper functions so you can call the code from the database:

CREATE OR REPLACE FUNCTION UNZIP_LIST_FILES(
  zipfile  IN BLOB
)
RETURN SYS.ODCIVARCHAR2LIST
AS LANGUAGE JAVA
NAME 'Unzip.listFiles( oracle.sql.BLOB ) return oracle.sql.ARRAY';
/
SHOW ERRORS;
/

CREATE OR REPLACE FUNCTION UNZIP(
  zipfile  IN BLOB,
  filePath IN VARCHAR2
)
RETURN BLOB
AS LANGUAGE JAVA
NAME 'Unzip.unzip( oracle.sql.BLOB, java.lang.String ) return oracle.sql.BLOB';
/
SHOW ERRORS;
/

Then you can use them to insert into the new table:

INSERT INTO your_table_of_unzipped_files ( filepath, file )
SELECT n.COLUMN_VALUE,
       UNZIP( t.your_blob, n.COLUMN_VALUE )
FROM   your_table_of_zipped_files t
       CROSS JOIN TABLE( UNZIP_LIST_FILES( t.your_blob ) ) n