Storing PDF Files inside Database

Create Source (D:\temp) and output (D:\temp\tgt) directories.

CRATE OR REPLACE DIRECTORY MY_FILES AS 'D:\temp';

GRANT READ, WRITE ON DIRECTORY MY_FILES TO PUBLIC;

CREATE OR REPLACE DIRECTORY TGT_FILES AS 'D:\temp\tgt';

GRANT READ, WRITE ON DIRECTORY TGT_FILES TO PUBLIC;

-------------------------------------------------------------------------------------
Create table with blob data type which will store file inside table column.

CREATE TABLE STORE_FILES (
FILE_ID NUMBER,
FILE_NAME VARCHAR2(20),
FILE_COPY BLOB);

--------------------------------------------------------------------
Create procedure which will load files from source directory(D:\temp) to database table STORE_FILES

CREATE OR REPLACE PROCEDURE load_file_to_table (p_file_id IN STORE_FILES.FILE_ID%TYPE, p_file_name IN STORE_FILES.FILE_NAME%TYPE) AS
v_bfile BFILE;
v_blob BLOB;
BEGIN
INSERT INTO STORE_FILES (file_id, file_name, file_copy)
VALUES (p_file_id, p_file_name, empty_blob())
RETURN file_copy INTO v_blob;
v_bfile := BFILENAME('SRC_FILES', p_file_name);
DBMS_LOB.FILEOPEN(v_bfile, Dbms_Lob.File_Readonly);
DBMS_LOB.LOADFROMFILE(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));
DBMS_LOB.FILECLOSE(v_bfile);
COMMIT;
END;
/

--------------------------------------------------------------------
Copy a pdf file in source directory D:\temp (I copied test.pdf) You must replace name of file with your own file name in below code

exec load_file_to_table(1,'test.pdf') ;

---------------------------------------------------------------------
Procedure to Unload files from DB to Server File System

CREATE OR REPLACE PROCEDURE load_file_to_target (p_file_id IN STORE_FILES.FILE_ID%TYPE, p_file_name IN STORE_FILES.FILE_NAME%TYPE) AS
v_blob BLOB;
v_start NUMBER := 1;
v_bytelen NUMBER := 32000;
v_len NUMBER ;
v_len_copy NUMBER;
v_raw_var RAW(32000);
v_output utl_file.file_type;
BEGIN
--define output directory and open the file in write byte mode
v_output := utl_file.fopen('TGT_FILES', p_file_name,'wb', 32760);
v_start := 1;
v_bytelen := 32000;
-- get length of blob
SELECT DBMS_LOB.GETLENGTH(file_copy)
INTO v_len
FROM STORE_FILES
where file_name = p_file_name and file_id = p_file_id  ;
-- save blob length
v_len_copy := v_len;
-- Get the blob
select file_copy
into v_blob
FROM STORE_FILES
where file_name = p_file_name and file_id = p_file_id  ;
-- Maximum size of buffer parameter is 32767 before which you have to flush your buffer
IF v_len < 32760 THEN
utl_file.put_raw(v_output,v_blob);
utl_file.fflush(v_output);
ELSE
-- write in separate buffers
v_start := 1;
WHILE v_start < v_len and v_bytelen > 0
LOOP
DBMS_LOB.READ(v_blob,v_bytelen,v_start,v_raw_var);
utl_file.put_raw(v_output,v_raw_var);
utl_file.fflush(v_output);
-- set the start position for next flush
v_start := v_start + v_bytelen;
-- set the end position if less than 32000 bytes
v_len_copy := v_len_copy - v_bytelen;
IF v_len_copy < 32000 THEN
v_bytelen := v_len_copy;
END IF;
end loop;
utl_file.fclose(v_output);
END IF;
END;
/

----------------------------------------------------------------------
Execute procedure to unload files from DB to D:\temp\tgt folder.

exec load_file_to_target(1,'test.pdf') ;