How to resolve deprecation on Unzip Blob Java Source running on PL/SQL?

Question

Based on this answer: How to read and extract zip entries from java.sql.Blob type zip file without having FileInputStream or filepath as a string java.

An error is being thrown about a deprecated API.

Reproduction of steps:

1. Database Oracle JDK Version:

SELECT dbms_java.get_jdk_version FROM dual;

Result: 1.8.0_341

2. loadjava

call dbms_java.loadjava('/tmp/xz-1.8.jar');

Result: Call completed

call dbms_java.loadjava('/tmp/commons-compress-1.10.jar');

Result: Call completed

3. Create the java source CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED UNZIP AS ... like the answer the thread referenced.

4. The following error is presented:

Java Source UNZIP criado

LINE/COL ERROR --------- ------------------------------------------------------------- 0/0 Note: Recompile with -Xlint:deprecation for details. 0/0 Note: UNZIP uses or overrides a deprecated API. Erros: verifique o log do compilador

How could I solve this deprecated APIs problem?

Answer 1

Download (and if necessary build) xz-1.9, commons-compress-1.21 and ojdbc11 (these are the latest versions but you can download earlier versions if required, and the OJDBC jar should match the database version you are using). Then, within the /path/to/your/code/ directory, put the built jar files into the lib sub-directory.

Create the file Unzip.java, again within the /path/to/your/code/ directory, that has the contents:

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 java.sql.Array;
import java.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.getBinaryStream();
        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 ((oracle.jdbc.OracleConnection) conn).createOracleArray("SYS.ODCIVARCHAR2LIST", 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.getBinaryStream();
        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 = conn.createBlob();

          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;
  }
}

Then run javac -cp "lib/*" Unzip.java to build it; it should compile without errors.

If it does then:

1. Unload any existing versions of the XZ and Commons Compress libraries from your Oracle database.

2. Use loadjava (or an equivalent) to load the XZ and Commons Compress jars that you used as dependencies to compile the java class.

3. In Oracle, load the java using

   CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED UNZIP AS
   <paste the working java code here>
   /
   

4. Create the wrapper functions:

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

Then you should be able to use it in your code.

---

Note: The Java code works outside the database but loading into the database is untested as I do not currently have a Java-enabled database; however the principal is to test the Java outside the database using the correct versions of the Jar files and then when it is working load it into the database.