Loading and unloading files using Oracle Database

Problem :I need to store files in my Oracle database. When requested the files need to be unloaded to a server in their original format.

For storing files (gif/pdf/doc/jpg) a BLOB data type will be used.

Step 1: Create two directory objects. One which would be a landing-place to load all files and second to unload from the database

CREATE OR REPLACE DIRECTORY SRC_FILES AS ‘/src/files’;

GRANT READ, WRITE ON DIRECTORY SRC_FILES TO PUBLIC;

CREATE OR REPLACE DIRECTORY TGT_FILES AS ‘/tgt/files’;

GRANT READ, WRITE ON DIRECTORY TGT_FILES TO PUBLIC;

Step 2: In this example I ftp’ed a test.pdf file to /src/files folder on my Linux box.

Step 3: Create a table to hold the files

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

Step 4: Write a stored procedure that will take file id, file name as input and store in the file in 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 file id and file name in the table.

— Note the use of empty_blob(). This initializes the blob object first.

— Why: You need to initialize a blob object before you access or populate it.

INSERT INTO STORE_FILES (file_id, file_name, file_copy)
VALUES (p_file_id, p_file_name, empty_blob())
RETURN image INTO v_blob;

— v_blob contains the initialized blob object

— Get a BFILE locator that is associated with the physical file on the DIRECTORY

v_bfile := BFILENAME(‘SRC_FILES’, p_file_name);

— Open the file using DBMS_LOB

DBMS_LOB.FILEOPEN(v_bfile, Dbms_Lob.File_Readonly);

— Load the file into the BLOB pointer

DBMS_LOB..LOADFROMFILE(v_blob, v_bfile, Dbms_Lob.Getlength(v_bfile));

— Close the file

DBMS_LOB.FILECLOSE(v_bfile);
COMMIT;
END;
/

Step 5:

Load file test.pdf into table STORE_FILES

SQL> exec load_file_to_table(1,’test.pdf’) ;

Check table STORE_FILES to see a record.

Step 6: Unfortunately to unload the file back to the physical server there is no option using DBMS_LOB or BFILENAME. We have to use UTL_FILE package to write the file out.

We will create a stored proc to unload the file to a target directory.

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

Step 7: Execute the procedure to unload the file into a target location

SQL> exec load_file_to_target(1,’test.pdf’) ;

Step 8: On Linux box open directory /tgt/files and check if “test.pdf” exists.

Step 9: FTP the file to your desktop and open with Acrobat to check if file is in its original state.