Download Blob Files from Oracle Table in 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
<>
<< Begins >>
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;

<< End >>

Step 2: Install the Package
ZIP_BLOBS
<< Package
Specification <>
<< Begins >>
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;
<< Ends>>
<< Package Body
<>
<< Begins >>
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;
<< Ends>>
Conclusion: This
Package will help us to download blob files from Oracle.

By
Karkuvelraja T

  • January 2, 2017 | 19 views