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

Friendly URL: Redirect to Different Page after Login in Oracle APEX 20.1

Oracle has updated apex.oracle.com to APEX 20.1 which includes among other features the new Friendly URL option. Here i am giving an example to redirect to different page after login in Oracle APEX 20.1 [Friendly URL Enabled] Step 1: Define home page for each user in emp master table as below Step 2: To enable Friendly URL Syntax, follow below steps, 1) On the Workspace home page, click the App Builder icon. 2) Select an application (The Application home page appears). 3) From Application home page, you can access the Definition page in TWO ways: Click the Edit Application Properties button. From Shared Components:              1) Click Shared Components .              2) Under Application Logic, click Application Definition Attributes . The Definition page appears. 4) Under Properties, configure the Friendly URL s attribute: Click Apply Changes to save your ch...
Post a Comment
Read more

Open modal/inline dialog in Oracle APEX using JS

Analysis: From Oracle Apex 4.2,  Inline dialog can be opened as modal dialog/popup using JS. Open inline dialog region as modal dialog: Step 1: Create static region. Step 2:  Set Template => Inline Dialog. Step 3:  Assign the static id to the region. Fig 1: Set template and Static ID Step 4:  Create button or change one of the report column type to Link. (Action: Redirect URL). Step 5:  Put the below JS to open the region as modal dialog.                javascript:openModal('unitpricedetails');            Fig 2:  Open simple modal dialog Open inline dialog region as modal dialog and pass values to the items: Common Steps:  Step 1 to Step 4. Step 5:  To pass value to that region, create P1_UNIT_ID in the inline  dialog.       javascript:$s('P1_UNIT_ID','#UNIT_ID#');javascript:openModal('unitpricedetai...
1 comment
Read more