Export BLOB from database to physical directory.

 I have seen many people are failed to extract image from database to physical directory (in my article comments ) for this reason today I have make this procedure for export blob file.

You can export BLOB file using the following instruction.

1. Create an directory .

CREATE OR REPLACE DIRECTORY
DATA_DIR AS
'D:\DUMP\';

2. Create procedure.

CREATE OR REPLACE PROCEDURE SP_EXPORT_IMAGE (P_BLOB_DATA IN BLOB,P_FILE_NAME VARCHAR2,P_DIRECTORY VARCHAR2 )
AS
V_CLOB_DATA CLOB;
V_DATA VARCHAR2(32767);
V_START PLS_INTEGER := 1;
V_END PLS_INTEGER := 32767;
  V_OUTPUT UTL_FILE.FILE_TYPE;
  V_CHNK_SIZE PLS_INTEGER;
BEGIN
DBMS_LOB.CREATETEMPORARY(V_CLOB_DATA, TRUE);

FOR I IN 1..CEIL(DBMS_LOB.GETLENGTH(P_BLOB_DATA) / V_END)
LOOP

   V_DATA := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(P_BLOB_DATA, V_END, V_START));

DBMS_LOB.WRITEAPPEND(V_CLOB_DATA, LENGTH(V_DATA), V_DATA);
V_START := V_START + V_END;
END LOOP;

V_CHNK_SIZE := 3000;

V_OUTPUT := UTL_FILE.FOPEN(P_DIRECTORY, P_FILE_NAME, 'wb', MAX_LINESIZE => 32767 );

  FOR I IN 1 .. CEIL( LENGTH( V_CLOB_DATA ) / V_CHNK_SIZE )
  LOOP
    UTL_FILE.PUT_RAW( V_OUTPUT, UTL_RAW.CAST_TO_RAW( SUBSTR( V_CLOB_DATA, ( I - 1 ) * V_CHNK_SIZE + 1, V_CHNK_SIZE ) ) );
    UTL_FILE.FFLUSH(V_OUTPUT);
  END LOOP;
       
  UTL_FILE.FCLOSE(V_OUTPUT);

END SP_EXPORT_IMAGE;


---------------- Export Blob File in physical directory. ------------------


DECLARE
V_BLOB BLOB;
 V_FILE_NAME VARCHAR2(300);
BEGIN
SELECT IMAGE_FRONT, DATA_NO INTO V_BLOB, V_FILE_NAME
FROM OUTWDCLR_REP
WHERE DATA_NO=77771613;

SP_EXPORT_IMAGE(V_BLOB,V_FILE_NAME||'.JPG','DATA_DIR');
END;