PLS-00222: no function with name 'PR_UNCOMPRESS' exist in the scope

Question

I have to unzip a folder with name say TEST.zip and automate the process. I tried multiple things, after searching on internet i came to a better way is to create Java utility and then write a procedure.

Here is the Java utility which I ran first.

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "Zlib"
AS
import java.util.zip.*;

import oracle.sql.BLOB;

public class Zlib {

    public static void compress(oracle.sql.BLOB p_src, oracle.sql.BLOB[] p_dst) throws Exception {

        byte[] src = p_src.getBytes(1, (int) p_src.length());

        Deflater compresser = new Deflater(Deflater.BEST_COMPRESSION);

        compresser.setInput(src);

        compresser.finish();

        byte[] dst = new byte[102400];

        int resultLength = compresser.deflate(dst);

        compresser.end();

        int setBytesLength = p_dst[0].setBytes(1, dst, 0, resultLength);

    }

    public static void uncompress(oracle.sql.BLOB p_src, oracle.sql.BLOB[] p_dst) throws Exception {

        byte[] src = p_src.getBytes(1, (int) p_src.length());

        Inflater decompresser = new Inflater();

        decompresser.setInput(src);

        byte[] dst = new byte[102400];

        int resultLength = decompresser.inflate(dst);

        decompresser.end();

        int setBytesLength = p_dst[0].setBytes(1, dst, 0, resultLength);

    }

}
/

CREATE OR REPLACE PACKAGE pg_zlib
IS
  PROCEDURE pr_compress
  ( p_src IN     BLOB
  , p_dst IN OUT BLOB
  );

  PROCEDURE pr_uncompress
  ( p_src IN     BLOB
  , p_dst IN OUT BLOB
  );

END pg_zlib;
/

CREATE OR REPLACE PACKAGE BODY pg_zlib
IS
  PROCEDURE pr_compress
  ( p_src IN     BLOB
  , p_dst IN OUT BLOB
  )
  IS LANGUAGE JAVA
  NAME 'Zlib.compress(oracle.sql.BLOB, oracle.sql.BLOB[])';

  PROCEDURE pr_uncompress
  ( p_src IN     BLOB
  , p_dst IN OUT BLOB
  )
  IS LANGUAGE JAVA
  NAME 'Zlib.uncompress(oracle.sql.BLOB, oracle.sql.BLOB[])';

END pg_zlib;
/

This worked and the zip and unzip utility got compiled.

Next I executed the below procedure:

DECLARE
   in_filename   VARCHAR2 (100);
   src_file      BFILE;
   v_content     BLOB;
   v_blob_len    INTEGER;
   v_file        UTL_FILE.file_type;
   v_buffer      RAW (32767);
   v_amount      BINARY_INTEGER     := 32767;
   v_pos         INTEGER            := 1;
BEGIN
   in_filename := 'Test.zip';
   src_file := BFILENAME ('DATA_PUMP_DIR', in_filename);
   DBMS_LOB.fileopen (src_file, DBMS_LOB.file_readonly);
   v_file := UTL_FILE.fopen ('MONSTERDMP', 'Test' || '.txt', 'wb');
   v_content := pg_zlib.pr_uncompress (src_file,v_file);
   v_blob_len := DBMS_LOB.getlength (v_content);

   WHILE v_pos < v_blob_len
   LOOP
      DBMS_LOB.READ (v_content, v_amount, v_pos, v_buffer);
      UTL_FILE.put_raw (v_file, v_buffer, TRUE);
      v_pos := v_pos + v_amount;
   END LOOP;

   UTL_FILE.fclose (v_file);
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (v_file)
      THEN
         UTL_FILE.fclose (v_file);
      END IF;

      RAISE;

END;

When I execute the procedure I get the error

PLS-00222: no function with name 'PR_UNCOMPRESS' exist in the scope.

user_429100 · Answer

You created a procedure, not function.

Fixed code:

DECLARE
   in_filename   VARCHAR2 (100);
   src_file      BFILE;
   v_src_blob    BLOB;
   v_content     BLOB;
   v_blob_len    INTEGER;
   v_file        UTL_FILE.file_type;
   v_buffer      RAW (32767);
   v_amount      BINARY_INTEGER     := 32767;
   v_pos         INTEGER            := 1;
BEGIN
   in_filename := 'Test.zip';
   src_file := BFILENAME ('FILES', in_filename);
   --DBMS_LOB.fileopen (src_file, DBMS_LOB.file_readonly);
   DBMS_LOB.CREATETEMPORARY(v_src_blob,TRUE, DBMS_LOB.SESSION);
   dbms_lob.open(src_file, dbms_lob.lob_readonly);
   dbms_lob.open(v_src_blob, dbms_lob.lob_readwrite);
   dbms_lob.loadfromfile(
       dest_lob      => v_src_blob, 
       src_lob       => src_file, 
       amount        => dbms_lob.getlength(src_file));
   
   v_file := UTL_FILE.fopen ('FILES', 'Test' || '.txt', 'wb');
   pg_zlib.pr_uncompress (v_src_blob,v_content);
   v_blob_len := DBMS_LOB.getlength (v_content);

   WHILE v_pos < v_blob_len
   LOOP
      DBMS_LOB.READ (v_content, v_amount, v_pos, v_buffer);
      UTL_FILE.put_raw (v_file, v_buffer, TRUE);
      v_pos := v_pos + v_amount;
   END LOOP;

   UTL_FILE.fclose (v_file);
   
EXCEPTION
   WHEN OTHERS
   THEN
      IF UTL_FILE.is_open (v_file)
      THEN
         UTL_FILE.fclose (v_file);
      END IF;

      RAISE;
END;
/

But you should know that java.util.zip.inflater decodes the zlib format, not the zip format: java.util.zip.DataFormatException: unknown compression method at java.util.zip.Inflater.inflateBytes(Native Method)

PLS-00222: no function with name 'PR_UNCOMPRESS' exist in the scope

1 Answers