Skip to main content

Download Blob files as ZIP in Oracle APEX using PL/SQL

Objective:

To download blob files (ZIP Files) from table in APEX using Oracle PL/SQL.

Scenario:

Customer has asked us to give the provision in APEX, which is used to download the blob files from Oracle APEX application itself. 

Solution:

Step 1: Create APEX process using below PL/SQL anonymous block.

<< Anonymous Block <>

DECLARE
  v_mime      VARCHAR2(48);
  v_length    NUMBER(38);
  v_file_name VARCHAR2(38);
  p_src BLOB;
  p_pw VARCHAR2(200);
  v_return BLOB;
BEGIN
  p_pw := 'test';
  BEGIN
    SELECT
      'test' ,
      compressed_blob ,
      'Redemption_payout.RAR' ,
      dbms_lob.getlength(compressed_blob)
    INTO
      v_mime ,
      p_src ,
      v_file_name ,
      v_length
    FROM
      red_compressed_files;
  exception
  WHEN others THEN
    ROLLBACK;
  END;
  BEGIN
    v_return := zip_blobs.encrypt( p_pw => p_pw, p_src => p_src );
  END;
  owa_util.mime_header( nvl(v_mime,'application/octet'), FALSE );
  htp.p('Content-length: ' || v_length);
  htp.p('Content-Disposition:  attachment;filename="'||REPLACE(REPLACE(substr(
  v_file_name,instr(v_file_name,'/')+1),chr(10),NULL),chr(13),NULL)|| '"' );
  owa_util.http_header_close;
  wpg_docload.download_file(v_return);
  apex_application.stop_apex_engine;
END;
/
 

Step 2: Install the Package zip_blobs.

<< Package Specification <>

CREATE OR REPLACE PACKAGE zip_blobs
AS
  PROCEDURE add1file(
      p_zipped_blob IN out BLOB,
      p_name        IN VARCHAR2,
      p_content     IN BLOB);
  FUNCTION file2blob(
      p_dir       VARCHAR2,
      p_file_name VARCHAR2)
    RETURN BLOB;
  FUNCTION encrypt(
      p_pw VARCHAR2,
      p_src BLOB )
    RETURN BLOB;
  PROCEDURE add1file1(
      p_zipped_blob IN out BLOB,
      p_name        IN VARCHAR2,
      p_content     IN BLOB,
      p_password    IN VARCHAR2);
  PROCEDURE finish_zip(
      p_zipped_blob IN out BLOB);
  PROCEDURE finish_zip1(
      p_zipped_blob IN out BLOB );
  PROCEDURE save_zip(
      p_zipped_blob IN BLOB,
      p_dir         IN VARCHAR2,
      p_filename    IN VARCHAR2);
END zip_blobs;
/

<< Package Body <>

CREATE OR REPLACE PACKAGE BODY zip_blobs
AS
  c_local_file_header constant RAW(4) := hextoraw( '504B0304' ); -- Local file
  -- header signature
  c_end_of_central_directory constant RAW(4) := hextoraw( '504B0506' ); -- End
  -- of central directory signature
  --
FUNCTION little_endian(
    p_big   IN NUMBER,
    p_bytes IN pls_integer := 4 )
  RETURN RAW
IS
BEGIN
  RETURN utl_raw.substr( utl_raw.cast_from_binary_integer( p_big,
  utl_raw.little_endian ), 1, p_bytes );
END;
--
FUNCTION file2blob(
    p_dir VARCHAR2
    ,
    p_file_name VARCHAR2
  )
  RETURN BLOB
IS
  file_lob BFILE;
  file_blob BLOB;
BEGIN
  file_lob := bfilename( p_dir, p_file_name );
  dbms_lob.OPEN( file_lob, dbms_lob.file_readonly );
  dbms_lob.createtemporary( file_blob, TRUE );
  dbms_lob.loadfromfile( file_blob, file_lob, dbms_lob.lobmaxsize );
  dbms_lob.CLOSE( file_lob );
  RETURN file_blob;
exception
WHEN others THEN
  IF dbms_lob.isopen( file_lob ) = 1
    THEN
    dbms_lob.CLOSE( file_lob );
  END IF;
  IF dbms_lob.istemporary( file_blob ) = 1
    THEN
    dbms_lob.freetemporary( file_blob );
  END IF;
  raise;
END;
--
FUNCTION blob2num(
    p_blob BLOB,
    p_len INTEGER,
    p_pos INTEGER )
  RETURN NUMBER
IS
BEGIN
  RETURN utl_raw.cast_to_binary_integer( dbms_lob.substr( p_blob, p_len, p_pos
  ), utl_raw.little_endian );
END;
--
FUNCTION encrypt(
    p_pw VARCHAR2,
    p_src BLOB )
  RETURN BLOB
IS
  t_salt RAW(16);
  t_key RAW(32);
  t_pw RAW(32767)          := utl_raw.cast_to_raw( p_pw );
  t_key_bits pls_integer   := 256;
  t_key_length pls_integer := t_key_bits / 8 * 2 + 2;
  t_cnt pls_integer        := 1000;
  t_keys RAW(32767);
  t_sum RAW(32767);
  t_mac RAW(20);
  t_iv RAW(16);
  t_block RAW(16);
  t_len pls_integer;
  t_rv BLOB;
  t_tmp BLOB;
BEGIN
  t_salt := dbms_crypto.randombytes( t_key_bits / 16 );
  FOR i                                        IN 1 .. ceil( t_key_length / 20
  )
  loop
    t_mac := dbms_crypto.mac( utl_raw.concat( t_salt, to_char( i, 'fm0xxxxxxx'
    ) ), dbms_crypto.hmac_sh1, t_pw );
    t_sum := t_mac;
    FOR j IN 1 .. t_cnt - 1
    loop
      t_mac := dbms_crypto.mac( t_mac, dbms_crypto.hmac_sh1, t_pw );
      t_sum := utl_raw.bit_xor( t_mac, t_sum );
    END loop;
    t_keys := utl_raw.concat( t_keys, t_sum );
  END loop;
  t_keys := utl_raw.substr( t_keys, 1, t_key_length );
  t_key  := utl_raw.substr( t_keys, 1, t_key_bits           / 8 );
  t_rv   := utl_raw.concat( t_salt, utl_raw.substr( t_keys, -2, 2 ) );
  --
  FOR i IN 0 .. trunc( ( dbms_lob.getlength( p_src ) - 1 ) / 16 )
  loop
    t_block := dbms_lob.substr( p_src, 16, i * 16 + 1 );
    t_len   := utl_raw.LENGTH( t_block );
    IF t_len < 16
      THEN
      t_block := utl_raw.concat( t_block, utl_raw.copies( '00', 16 - t_len ) );
    END IF;
    t_iv := utl_raw.REVERSE( to_char( i + 1,
    'fm000000000000000000000000000000x' ) );
    dbms_lob.writeappend( t_rv, t_len, dbms_crypto.encrypt( t_block,
    dbms_crypto.encrypt_aes256 + dbms_crypto.chain_cfb + dbms_crypto.pad_none,
    t_key, t_iv ) );
  END loop;
  --
  dbms_lob.createtemporary( t_tmp, TRUE );
  dbms_lob.copy( t_tmp, t_rv, dbms_lob.getlength( p_src ), 1, t_key_bits / 16 +
  2                                                                      + 1 );
  t_mac := dbms_crypto.mac( t_tmp, dbms_crypto.hmac_sh1, utl_raw.substr( t_keys
  , 1 + t_key_bits / 8, t_key_bits / 8 ) );
  dbms_lob.writeappend( t_rv, 10, t_mac );
  dbms_lob.freetemporary( t_tmp );
  RETURN t_rv;
END;
--
PROCEDURE add1file(
    p_zipped_blob IN out BLOB
    ,
    p_name IN VARCHAR2
    ,
    p_content IN BLOB
  )
IS
  t_now DATE;
  t_blob BLOB;
  t_clen INTEGER;
BEGIN
  t_now := SYSDATE;
  BEGIN
    dbms_lob.createtemporary( t_blob, TRUE );
    t_blob := utl_compress.lz_compress( p_content );
  exception
  WHEN others THEN
    raise_application_error(-20001,'test');
  END;
  t_clen           := dbms_lob.getlength( t_blob );
  IF p_zipped_blob IS NULL
    THEN
    dbms_lob.createtemporary( p_zipped_blob, TRUE );
  END IF;
  dbms_lob.APPEND( p_zipped_blob
  , utl_raw.concat( hextoraw( '504B0304' ) -- Local file header signature
  , hextoraw( '1400' )                     -- version 2.0
  , hextoraw( '0000' )                     -- no General purpose bits
  , hextoraw( '0800' )                     -- deflate
  , little_endian( to_number( to_char( t_now, 'ss' ) ) / 2
                                                       + to_number( to_char(
  t_now, 'mi' ) )                                      * 32
                                                       + to_number( to_char(
  t_now, 'hh24' ) )                                    * 2048
  , 2
  )
  -- File last modification time
  , little_endian( to_number( to_char( t_now, 'dd' ) )
  + to_number( to_char( t_now, 'mm' ) ) * 32
  + ( to_number( to_char( t_now, 'yyyy' ) ) - 1980 ) * 512
  , 2
  )                                                  -- File last modification date
  , dbms_lob.substr( t_blob, 4, t_clen - 7 )         -- CRC-32
  , little_endian( t_clen              - 18 )        -- compressed size
  , little_endian( dbms_lob.getlength( p_content ) ) -- uncompressed size
  , little_endian( LENGTH( p_name ), 2 )             -- File name length
  , hextoraw( '0000' )                               -- Extra field length
  , utl_raw.cast_to_raw( p_name )                    -- File name
  )
  );
  --dbms_lob.append( p_zipped_blob, dbms_lob.substr( t_blob, t_clen - 18, 11 )
  -- );     -- compressed content
  dbms_lob.copy( p_zipped_blob, t_blob, t_clen - 18, dbms_lob.getlength(
  p_zipped_blob )                              + 1, 11 );
  dbms_lob.freetemporary( t_blob );
END;
--
PROCEDURE add1file1(
    p_zipped_blob IN out BLOB
    ,
    p_name VARCHAR2
    ,
    p_content BLOB
    ,
    p_password VARCHAR2
  )
IS
  t_now DATE;
  t_blob BLOB;
  t_len  INTEGER;
  t_clen INTEGER;
  t_crc32 RAW(4)       := hextoraw( '00000000' );
  t_compressed boolean := FALSE;
  t_encrypted  boolean := FALSE;
  t_name RAW(32767);
  t_extra RAW(11);
BEGIN
  t_now   := SYSDATE;
  t_len   := nvl( dbms_lob.getlength( p_content ), 0 );
  IF t_len > 0
    THEN
    dbms_lob.createtemporary( t_blob, TRUE );
    dbms_lob.copy( t_blob, utl_compress.lz_compress( p_content ),
    dbms_lob.lobmaxsize , 1, 11 );
    t_clen       := dbms_lob.getlength( t_blob ) - 8;
    t_compressed := t_clen < t_len;
    t_crc32      := dbms_lob.substr( t_blob, 4, t_clen + 1 );
    dbms_lob.trim( t_blob, t_clen );
  END IF;
  IF NOT t_compressed
    THEN
    t_clen := t_len;
    t_blob := p_content;
  END IF;
  --
  IF p_zipped_blob IS NULL
    THEN
    dbms_lob.createtemporary( p_zipped_blob, TRUE );
  END IF;
  --
  IF p_password IS NOT NULL AND t_len > 0
    THEN
    t_encrypted := TRUE;
    t_crc32     := hextoraw( '00000000' );
    t_extra     := hextoraw( '019907000200414503' ||
    CASE
    WHEN t_compressed
      THEN
      '0800' -- deflate
    ELSE
      '0000' -- stored
    END
    );
    t_blob := encrypt( p_password, t_blob );
    t_clen := dbms_lob.getlength( t_blob );
  END IF;
  t_name := utl_i18n.string_to_raw( p_name, 'AL32UTF8' );
  dbms_lob.APPEND( p_zipped_blob
  , utl_raw.concat( utl_raw.concat( c_local_file_header -- Local file header
  -- signature
  , hextoraw( '3300' ) -- version 5.1
  )
  ,
  CASE
  WHEN t_encrypted
    THEN
    hextoraw( '01' ) -- encrypted
  ELSE
    hextoraw( '00' )
  END
  ,
  CASE
  WHEN t_name = utl_i18n.string_to_raw( p_name, 'US8PC437' )
    THEN
    hextoraw( '00' )
  ELSE
    hextoraw( '08' ) -- set Language encoding flag (EFS)
  END
  ,
  CASE
  WHEN t_encrypted
    THEN
    '6300'
  ELSE
    CASE
    WHEN t_compressed
      THEN
      hextoraw( '0800' ) -- deflate
    ELSE
      hextoraw( '0000' ) -- stored
    END
  END
  , little_endian( to_number( to_char( t_now, 'ss' ) ) / 2
                                                       + to_number( to_char(
  t_now, 'mi' ) )                                      * 32
                                                       + to_number( to_char(
  t_now, 'hh24' ) )                                    * 2048
  , 2
  ) -- File last modification time
  , little_endian( to_number( to_char( t_now, 'dd' ) )
  + to_number( to_char( t_now, 'mm' ) ) * 32
  + ( to_number( to_char( t_now, 'yyyy' ) ) - 1980 ) * 512
  , 2
  )                                                         -- File last modification date
  , t_crc32                                                 -- CRC-32
  , little_endian( t_clen )                                 -- compressed size
  , little_endian( t_len )                                  -- uncompressed size
  , little_endian( utl_raw.LENGTH( t_name ), 2 )            -- File name length
  , little_endian( nvl( utl_raw.LENGTH( t_extra ), 0 ), 2 ) -- Extra field
  -- length
  , utl_raw.concat( t_name -- File name
  , t_extra
  )
  )
  );
  IF t_len > 0
    THEN
    dbms_lob.copy( p_zipped_blob, t_blob, t_clen, dbms_lob.getlength(
    p_zipped_blob ) + 1, 1 ); -- (compressed) content
  END IF;
  dbms_lob.freetemporary( t_blob );
END;
--
PROCEDURE finish_zip(
    p_zipped_blob IN out BLOB )
IS
  t_cnt pls_integer := 0;
  t_offs            INTEGER;
  t_offs_dir_header INTEGER;
  t_offs_end_header INTEGER;
  t_comment RAW(32767) := utl_raw.cast_to_raw(
  'Implementation by Anton Scheffer' );
BEGIN
  t_offs_dir_header := dbms_lob.getlength( p_zipped_blob );
  t_offs            := dbms_lob.instr( p_zipped_blob, hextoraw( '504B0304' ), 1
  );
  while t_offs > 0
  loop
    t_cnt := t_cnt + 1;
    dbms_lob.APPEND( p_zipped_blob
    , utl_raw.concat( hextoraw( '504B0102' ) -- Central directory file header
    -- signature
    , hextoraw( '1400' ) -- version 2.0
    , dbms_lob.substr( p_zipped_blob, 26, t_offs + 4 )
    , hextoraw( '0000' )          -- File comment length
    , hextoraw( '0000' )          -- Disk number where file starts
    , hextoraw( '0100' )          -- Internal file attributes
    , hextoraw( '2000B681' )      -- External file attributes
    , little_endian( t_offs - 1 ) -- Relative offset of local file header
    , dbms_lob.substr( p_zipped_blob
    , utl_raw.cast_to_binary_integer( dbms_lob.substr( p_zipped_blob, 2, t_offs
             + 26 ), utl_raw.little_endian )
    , t_offs + 30
    ) -- File name
    )
    );
    t_offs := dbms_lob.instr( p_zipped_blob, hextoraw( '504B0304' ), t_offs +
    32 );
  END loop;
  t_offs_end_header := dbms_lob.getlength( p_zipped_blob );
  dbms_lob.APPEND( p_zipped_blob
  , utl_raw.concat( hextoraw( '504B0506' ) -- End of central directory
  -- signature
  , hextoraw( '0000' )        -- Number of this disk
  , hextoraw( '0000' )        -- Disk where central directory starts
  , little_endian( t_cnt, 2 ) -- Number of central directory records on this
  -- disk
  , little_endian( t_cnt, 2 )                              -- Total number of central directory records
  , little_endian( t_offs_end_header - t_offs_dir_header ) -- Size of central
  -- directory
  , little_endian( t_offs_dir_header )                        -- Relative offset of local file header
  , little_endian( nvl( utl_raw.LENGTH( t_comment ), 0 ), 2 ) -- ZIP file
  -- comment length
  , t_comment
  )
  );
END;
--
PROCEDURE finish_zip1(
    p_zipped_blob IN out BLOB )
IS
  t_cnt pls_integer := 0;
  t_offs            INTEGER;
  t_offs_dir_header INTEGER;
  t_offs_end_header INTEGER;
  t_comment RAW(32767) := utl_raw.cast_to_raw(
  'Implementation by Anton Scheffer' );
  t_len pls_integer;
BEGIN
  t_offs_dir_header := dbms_lob.getlength( p_zipped_blob );
  t_offs            := 1;
  while dbms_lob.substr( p_zipped_blob, utl_raw.LENGTH( c_local_file_header ),
  t_offs ) = c_local_file_header
  loop
    t_cnt := t_cnt                              + 1;
    t_len := blob2num( p_zipped_blob, 2, t_offs + 28 );
    dbms_lob.APPEND( p_zipped_blob
    , utl_raw.concat( hextoraw( '504B0102' ) -- Central directory file header
    -- signature
    , hextoraw( '3F00' ) -- version 6.3
    , dbms_lob.substr( p_zipped_blob, 26, t_offs + 4 )
    , hextoraw( '0000' ) -- File comment length
    , hextoraw( '0000' ) -- Disk number where file starts
    , hextoraw( '0000' ) -- Internal file attributes =>
    --     0000 binary file
    --     0100 (ascii)text file
    ,
    CASE
    WHEN dbms_lob.substr( p_zipped_blob
      , 1
      , t_offs + 30 + blob2num( p_zipped_blob, 2, t_offs + 26 ) - 1
      )       IN ( hextoraw( '2F' ) -- /
      , hextoraw( '5C' )            -- \
      )
      THEN
      hextoraw( '10000000' ) -- a directory/folder
    ELSE
      hextoraw( '2000B681' )      -- a file
    END                           -- External file attributes
    , little_endian( t_offs - 1 ) -- Relative offset of local file header
    , dbms_lob.substr( p_zipped_blob
    , blob2num( p_zipped_blob, 2, t_offs + 26 ) + t_len
    , t_offs                             + 30
    ) -- File name + extra data field
    )
    );
    t_offs := t_offs + 30 + blob2num( p_zipped_blob, 4, t_offs + 18 ) --
    -- compressed size
    + blob2num( p_zipped_blob, 2, t_offs + 26 )  -- File name length
    + blob2num( p_zipped_blob, 2, t_offs + 28 ); -- Extra field length
  END loop;
  t_offs_end_header := dbms_lob.getlength( p_zipped_blob );
  dbms_lob.APPEND( p_zipped_blob
  , utl_raw.concat( c_end_of_central_directory -- End of central directory
  -- signature
  , hextoraw( '0000' )        -- Number of this disk
  , hextoraw( '0000' )        -- Disk where central directory starts
  , little_endian( t_cnt, 2 ) -- Number of central directory records on this
  -- disk
  , little_endian( t_cnt, 2 )                              -- Total number of central directory records
  , little_endian( t_offs_end_header - t_offs_dir_header ) -- Size of central
  -- directory
  , little_endian( t_offs_dir_header ) -- Offset of start of central directory,
  -- relative to start of archive
  , little_endian( nvl( utl_raw.LENGTH( t_comment ), 0 ), 2 ) -- ZIP file
  -- comment length
  , t_comment
  )
  );
END;
--
PROCEDURE save_zip(
    p_zipped_blob IN BLOB
    ,
    p_dir IN VARCHAR2
    ,
    p_filename IN VARCHAR2
  )
IS
  t_fh utl_file.file_type;
  t_len pls_integer := 32767;
BEGIN
  t_fh := utl_file.fopen( p_dir, p_filename, 'wb' );
  FOR i IN 0 .. trunc( ( dbms_lob.getlength( p_zipped_blob ) - 1 ) / t_len )
  loop
    utl_file.put_raw( t_fh, dbms_lob.substr( p_zipped_blob, t_len, i * t_len +
    1 ) );
  END loop;
  utl_file.fclose( t_fh );
END;
END zip_blobs;
/

Conclusion: This Package will help us to download blob files from Oracle.

Comments

Post a Comment

Popular posts from this blog

Printing Page Numbers in RTF Template [Oracle BI Publisher]

Here I am giving an example to print the page numbers dynamically in the RTF (Rich Text Format) template. Step 1:  Go to page footer and copy and paste the below script. Page |  <?fo:page-number?>  of  <?fo:page-number-citation:xdofo:lastpage-joinseq?> <fo:page-number> :   This is the object, which is used to represent the current page-number. <?fo:page-number-citation:xdofo:lastpage-joinseq?> :  This is the syntax, which is used to represent the total number of pages. Step 2:  Load the XML and preview the result. Output: That's it. References: fo:page-number Printing Page Number Code in Oracle XMLP RTF Template
2 comments
Read more

Oracle Application Express Views (APEX)

Application Express Views Search SELECT * FROM apex_dictionary WHERE column_id = 0; View Comment Parent View APEX_APPLICATIONS Applications defined in the current workspace or database user. APEX_WORKSPACES APEX_APPLICATION_ALL_AUTH All authorization schemes for all components by Application APEX_APPLICATIONS ...
Post a Comment
Read more

Save Selected Interactive Grid Records into a Collection - Oracle APEX

Here I am giving an example to save selected interactive grid records into a oracle apex collection. Step 1: Create a new blank page. Note: Mine was page 20. You will need to update reference to " P20 " with your page number if it's different. Step 2: Create a new interactive grid report region to the page using below query. Set Static Id "EmpDetails" to the region. SELECT  *     FROM   ( SELECT  emp . empno ,                emp . ename ,                emp . JOB ,                dept . dname department ,                dept . loc  LOCATION ,                mgr . ename  ...
9 comments
Read more