ORACLE MASTERMINDS: Download Blob Files from Oracle Table in APEX using PL/SQL

Monday 2 January 2017

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

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

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

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

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

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

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

)

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

)

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

, 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 )

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 )

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

)

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.

Karkuvelraja T

1 comment:

Blogger27 September 2017 at 17:01
Are you trying to make money from your websites with popup ads?
In case you do, have you tried using PropellerAds?
ReplyDelete
Replies

Add comment