2 Replies Latest reply: Jul 26, 2012 8:57 AM by 794548 RSS

    PDF upload and download in oracle 11g database

    794548
      Hi,

      I need to upload multiple pdf files into 11g oracle database and once after upload I need to download those files as pdf files. Please help me...

      Regards,
      Rajesh Pachiyappan
        • 1. Re: PDF upload and download in oracle 11g database
          794548
          Hi friends i got the solution for the above question.


          a) Follow the below steps for uploading a pdf file into a oracle table as blob data type.

          step 1.
          -- assume the PDF files are in /data/documents filesystem directory

          create or replace directory pdfdir as 'D:\pdf';
          grant read on directory pdfdir to <USERNAME>;

          step 2.

          create table mydocs (id integer primary key, doc blob);

          step 3.

          declare
          bf bfile;
          b blob;
          src_offset integer := 1;
          dest_offset integer := 1;
          begin
          -- insert a new blob and return it to local variable
          insert into mydocs values(1, empty_blob()) returning doc into b;
          -- open the bfile for file "summary.pdf"
          bf := bfilename('PDFDIR', 'test.pdf');
          dbms_lob.open(bf, dbms_lob.file_readonly);
          dbms_lob.loadBlobFromFile(b, bf, dbms_lob.lobmaxsize, dest_offset, src_offset);
          commit;
          end;

          Once after performing the above. The pdf content will be available in the table mydocs.


          b) Follow the below steps for downloading the blob data type contents as a pdf file.

          step 1:

          SQL> CREATE OR REPLACE DIRECTORY BLOBS AS 'd:/pdf/';

          Directory created.

          step 2:

          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;
          BEGIN
          -- Get LOB locator
          SELECT doc
          INTO l_blob
          FROM mydocs
          WHERE rownum = 1;

          l_blob_len := DBMS_LOB.getlength(l_blob);

          -- Open the destination file.
          l_file := UTL_FILE.fopen('BLOBS','MyImage.pdf','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(l_blob, l_amount, l_pos, l_buffer);
          UTL_FILE.put_raw(l_file, l_buffer, TRUE);
          l_pos := l_pos + l_amount;
          END LOOP;

          -- Close the file.
          UTL_FILE.fclose(l_file);

          EXCEPTION
          WHEN OTHERS THEN
          -- Close the file if something goes wrong.
          IF UTL_FILE.is_open(l_file) THEN
          UTL_FILE.fclose(l_file);
          END IF;
          RAISE;
          END;
          /
          • 2. Re: PDF upload and download in oracle 11g database
            794548
            Hi ,

            By using the above code I can able to upload and download a pdf file from oracle database. Can any one please help me how to upload and download multiple pdf files from oracle database.

            Regards,
            Rajesh Pachiyappan

            Edited by: user12112391 on Jul 26, 2012 6:56 AM