CREATE USER ZAPROS
  IDENTIFIED BY <password>
  DEFAULT TABLESPACE USERS
  TEMPORARY TABLESPACE TEMP
  PROFILE DEFAULT
  ACCOUNT UNLOCK;
  -- 1 Role for ZAPROS 
  GRANT DBA TO ZAPROS;
  ALTER USER ZAPROS DEFAULT ROLE ALL;
  -- 1 System Privilege for ZAPROS 
  GRANT UNLIMITED TABLESPACE TO ZAPROS;
  -- 1 Tablespace Quota for ZAPROS 
  ALTER USER ZAPROS QUOTA UNLIMITED ON USERS;
  -- 7 Java Privileges for ZAPROS
  
DECLARE
 KEYNUM NUMBER;
BEGIN
  SYS.DBMS_JAVA.GRANT_PERMISSION(
     grantee           => 'ZAPROS'
    ,permission_type   => 'SYS:java.util.PropertyPermission'
    ,permission_name   => '*'
    ,permission_action => 'read,write'
    ,key               => KEYNUM
    );
END;
/

CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED ZAPROS.MY_JAVA as import java.io.*;
import java.util.zip.*;
import java.util.*;
import java.sql.*;
import oracle.sql.*;
import java.net.*;
import static java.lang.Math.abs;
import java.util.ArrayList;
import java.util.Arrays;

 
      public class MY_ZIP
      {
        public static byte[] packRaw(byte[] b, String s) {
          ByteArrayOutputStream outBuffer = new ByteArrayOutputStream();
          try {
            ZipOutputStream zip = new ZipOutputStream(outBuffer);
            zip.putNextEntry(new ZipEntry (s)); 
            zip.write(b);
            zip.close();
          }
          catch (IOException e) {
            System.err.println(e);     
          }
          return outBuffer.toByteArray();
        }
 
        public static byte[] unpackRaw(byte[] b) {
          ByteArrayOutputStream outBuffer = new ByteArrayOutputStream();
          ByteArrayInputStream inBuffer = new ByteArrayInputStream(b);
          try {
            ZipInputStream zip = new ZipInputStream(inBuffer);
            byte[] tmpBuffer = new byte[256];
            int n;
            while ((n = zip.read(tmpBuffer)) >= 0)
              outBuffer.write(tmpBuffer, 0, n);
          }
          catch (IOException e) {
            System.err.println(e);     
          }
          return outBuffer.toByteArray();
        }
 
        public static void packBlob(String filename, oracle.sql.BLOB srcBlob, oracle.sql.BLOB dstBlob) {
          try {
            //OutputStream outBuffer = dstBlob.getBinaryOutputStream();
            InputStream inBuffer = srcBlob.getBinaryStream();
            ZipOutputStream zip = new ZipOutputStream(dstBlob.getBinaryOutputStream());
            byte[] tmpBuffer = new byte[srcBlob.getBufferSize()];
            zip.putNextEntry(new ZipEntry(filename));
            int n;
            while ((n = inBuffer.read(tmpBuffer)) >= 0)
              {zip.write(tmpBuffer, 0, n);
              }
            inBuffer.close();
            zip.close();
          }
          catch (SQLException e) {
            System.err.println(e);     
          }
          catch (IOException e) {
            System.err.println(e);     
          }
        }
 
        public static void unpackBlob(oracle.sql.BLOB srcBlob, oracle.sql.BLOB dstBlob) {
          try {
            OutputStream outBuffer = dstBlob.getBinaryOutputStream();
            //InputStream inBuffer = srcBlob.getBinaryStream();
            ZipInputStream zip = new ZipInputStream(srcBlob.getBinaryStream());
            byte[] tmpBuffer = new byte[srcBlob.getBufferSize()];
            ZipEntry ze = zip.getNextEntry();
            int n;
            while ((n = zip.read(tmpBuffer)) >= 0)
              outBuffer.write(tmpBuffer, 0, n);
            zip.close();  
            outBuffer.close();
          }
          catch (SQLException e) {
            System.err.println(e);     
          }
          catch (IOException e) {
            System.err.println(e);     
          }
        }
 
      };
/

create or replace procedure zapros.my_packBlob (filename varchar2, srcBlob Blob, zipBlob Blob)
as LANGUAGE java NAME 'MY_ZIP.packBlob(java.lang.String, oracle.sql.BLOB, oracle.sql.BLOB)';
/
create or replace procedure zapros.my_unpackBlob (zipBlob Blob, dstBlob Blob)
as LANGUAGE java NAME 'MY_ZIP.unpackBlob(oracle.sql.BLOB, oracle.sql.BLOB)';
/

declare 
    --file_blob BLOB;
    --handle   BINARY_INTEGER;
    --datatxt BLOB;
    
    --l_compressed_blob    BLOB;
    --l_uncompressed_blob  BLOB;
    --xmldoc  xmltype;
  s_txt varchar2(4000);
  v_clob clob;
  v_blob blob;
  v_zipBlob blob;
  v_unzipBlob blob;
  dest_offset     INTEGER := 1; 
  src_offset      INTEGER := 1;
  lang_context    INTEGER := dbms_lob.default_lang_ctx;
  p_csid          INTEGER := 871;
  v_error INTEGER;
begin
    select 
        ed.elado_bytes into v_zipBlob
    from 
        app_applications a,
        app_doc adoc,
        doc_foundations d,
        ela_document ed,
        ela_document_props ep
    where
        a.id = adoc.app_id(+)
        and adoc.doc_id = d.id(+)
        and d.id = ED.ELADO_EXT_ID(+)
        and ED.ELADO_DOCUMENT_ID = ep.elado_doc_id(+)
        and ed.ELADO_EXT_ENT_NAME(+) = 'DOC_FOUNDATIONS'
        and d.id = 19966375000; 
    
    dbms_lob.createtemporary(v_blob, true);
    zapros.my_unpackBlob (v_zipBlob, v_blob);
    
    dbms_lob.createtemporary(v_clob, true);
    dest_offset := 1; src_offset := 1;
    DBMS_LOB.CONVERTTOCLOB(v_CLOB, v_BLOB, DBMS_LOB.LOBMAXSIZE, dest_offset, src_offset, 871, lang_context, v_error);
    s_txt:= dbms_lob.substr(v_clob,4000);
    dbms_output.put_line(s_txt);
    --dbms_output.put_line(DBMS_LOB.default_csid);
end;