0

I'm trying to export a blob from DB and write to pdf on unix server. But what I'm getting is an empty pdf on unix server. My code is as follows:

*

CREATE OR REPLACE PROCEDURE write_to_file ()
IS
v_cn_letter_name       VARCHAR2 (100);
v_buffer               RAW (32767);
v_amount               BINARY_INTEGER := 32767
v_pos                  INTEGER            := 1;
v_blob_len             INTEGER;
v_file                 UTL_FILE.FILE_TYPE;
ftp_fldr_loc_i         VARCHAR2 := '/devl/apps/my_dir';
BEGIN
v_cn_letter_name := 'ConsolidatedConsumerNotificationLetter_'|| v_date ||'.pdf';
BEGIN
         SELECT cnsmr_letter
           INTO data_to_write
           FROM my_table
          WHERE letter_emailed_ind = 0;

 EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            ent_logging_pkg.handle_exception_prc
                          (SQLCODE,
                           SQLERRM,
                           'No Records for letters');
 END;
BEGIN
v_blob_len := DBMS_LOB.getlength(data_to_write);
-- Open the destination file.
v_file := UTL_FILE.fopen(ftp_fldr_loc_i, v_cn_letter_name, 'WB', 32767);
WHILE v_pos <= v_blob_len
LOOP
  IF v_pos + v_amount - 1 > v_blob_len
  THEN
      v_amount := v_blob_len - v_pos + 1;
  END IF;
DBMS_LOB.read(data_to_write, v_amount, v_pos, v_buffer);
UTL_FILE.put_raw(v_file, v_buffer, TRUE);
utl_file.fflush(v_file);
v_pos := v_pos + v_amount;
END LOOP;
-- Close the file.
UTL_FILE.fclose(v_file);
EXCEPTION
        WHEN OTHERS
        THEN
           IF utl_file.is_open(v_file)
           THEN
              utl_file.fclose(v_file);
           END if;
           ent_logging_pkg.handle_exception_prc
                            (SQLCODE,
                             SQLERRM,
                             'EXCEPTION WRITING TO FILE');
END;
END write_to_file;

*

I have sufficient privileges on the directory as well. I'm using Oracle 11g On execution, a pdf file is created on server but is empty. I tried to write to txt file and that works fine. Why is an empty pdf is created? Please help!

ab19
  • 1
  • Where is `data_to_write` declared? Is the query actually finding a record, or do you get the exception logged? What value does `v_blob_len` get? – Alex Poole Jun 05 '15 at 18:42
  • @Alex Poole I tried to display value of v_buffer using following sql query : dbms_output.put_line('v_buffer: '|| utl_raw.cast_to_varchar2(v_buffer)); It gave me following output: v_buffer: %PDF-1.4 %âãÏÓ 4 0 obj <>stream xVmoÚ0´où'M6PÛy﷾зè k×&1Ô+$ÔI`üû]ÜEjQMé"Ûïyósy²ù!$MÀñÙó4ÃÁ³©6P3û+|K~ãrÖl¦~»ùÿþs(eíÌÿ¡ãEóÓ4_ïp%`­d%óTðl%KÕ¯`-`©T¥ÈOµ(+l`SÔ ÒZ)W0ã%¬D N¥i½\¨Ùú\= |-²Y§a(«b©å´RZtÜQ µ9¯Ä{xÌZ n®k)e½\Î¥P ¦St+©XLÐäÒ#ÔëBò Jt^«xp-çsHµÐç¥ÏgË eÓ W;r9mPà£jo`Z« ¥>Yä%,çc jÎý3 I dont think data_to_write needs to be declared because v_buffer is not null – ab19 Jun 05 '15 at 21:03

0 Answers0