2. How to upload and download a pdf file in oracle database?

how to upload and download a pdf file in oracle database?


here i have some procedures to upload and download a pdf file in oracle type database



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;
/

https://forums.oracle.com/forums/thread.jspa?messageID=10481109&#10481109