This discussion is archived
2 Replies Latest reply: Jul 26, 2012 6:57 AM by 794548 RSS

PDF upload and download in oracle 11g database

794548 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points