Download RTF Template from Database

1 BY 1

========

SELECT file_name, file_content_type, file_data  FROM XDO_LOBS

WHERE xdo_file_type          = 'RTF'

AND   application_short_name = 'XXTEST'

ONCE IN A WHILE (Not guarntee to download all files)

====================================================

DECLARE

  l_file      UTL_FILE.FILE_TYPE;

  l_buffer    RAW(32767);

  l_amount    BINARY_INTEGER := 32767;

  l_pos       INTEGER := 1;

  l_blob      BLOB;

  l_blob_len  INTEGER;

  v_directory VARCHAR2(20) := 'ECX_UTL_LOG_DIR_OBJ';

  CURSOR c1 IS

   SELECT file_name, file_content_type, file_data  

   FROM   xdo_lobs

   WHERE  xdo_file_type          = 'RTF'

   AND    application_short_name = 'XXTEST';

BEGIN

 FOR r1 IN c1 LOOP

  IF UTL_FILE.is_open(l_file) THEN

      UTL_FILE.fclose(l_file);

    END IF;

  l_blob_len := DBMS_LOB.getlength(r1.file_data);

  l_file := UTL_FILE.fopen(v_directory,r1.file_name,'wb', 32767);

  -- Read chunks of the BLOB and write them to the file until complete.

  --

  WHILE l_pos < l_blob_len LOOP

   DBMS_LOB.read(r1.file_data, l_amount, l_pos, l_buffer);

   UTL_FILE.put_raw(l_file, l_buffer);

   l_pos := l_pos + l_amount;

  END LOOP;

  UTL_FILE.fclose(l_file);

 END LOOP;

 EXCEPTION

  WHEN OTHERS THEN

    -- Close the file if something goes wrong.

    DBMS_OUTPUT.PUT_LINE ( SQLERRM );

    IF UTL_FILE.is_open(l_file) THEN

      UTL_FILE.fclose(l_file);

    END IF;

END;

Kindly Bookmark this Post using your favorite Bookmarking service: