1. Create a directory "DB_Files_test" under D:\SURESH\03_Working\ in Windows
2. Connect as SYSDBA and create a directory as DB_FILES
SQL> CREATE OR REPLACE DIRECTORY DB_FILES As 'D:\SURESH\03_Working\DB_Files_test';
Directory created.
--Grant read, write access on the directory to the user which you want to perform upload & download file
SQL> grant read, write on directory db_files to test;
Grant succeeded.
3. Create a table POC_FILE_STORAGE to store the files as BLOB
CREATE TABLE POC_FILE_STORAGE (
file_name VARCHAR2 (1000),
file_content BLOB)
/
4. Create the below procedure to convert a file to BLOB
CREATE OR REPLACE FUNCTION FILE_TO_BLOB(p_file_name VARCHAR2) RETURN BLOB AS
destination_location BLOB := empty_blob();
source_location BFILE := BFILENAME('DB_FILES', p_file_name);
BEGIN
DBMS_LOB.OPEN(source_location, DBMS_LOB.LOB_READONLY);
DBMS_LOB.CREATETEMPORARY(
lob_loc => destination_location
, cache => true
, dur => dbms_lob.session);
DBMS_LOB.OPEN(destination_location, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(
dest_lob => destination_location
, src_lob => source_location
, amount => DBMS_LOB.getLength(source_location));
DBMS_LOB.CLOSE(destination_location);
DBMS_LOB.CLOSE(source_location);
RETURN destination_location;
END FILE_TO_BLOB;
/
5. Use Below PL/SQL block to store the file in the POC_FILE_STORAGE tables
DECLARE
v_blob BLOB;
BEGIN
v_blob := FILE_TO_BLOB ('OCA.pdf');
INSERT INTO POC_FILE_STORAGE VALUES ('OCA.pdf', v_blob);
COMMIT;
END;
/
6. Use Below SQL to list the records in the table POC_FILE_STORAGE table
SQL> col file_name for a20
SQL> set lines 200
SQL> select * from POC_FILE_STORAGE
FILE_NAME FILE_CONTENT
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
OCA.pdf 255044462D312E360D25E2E3CFD30D0A363935342030206F626A0D3C3C2F46696C7465722F466C6174654465636F64652F466972737420313733372F4C656E67746820343933382F4E203136372F5479
7. Create the below procedure to convert BLOB to a file:
CREATE OR REPLACE PROCEDURE blob_to_file (i_dir IN VARCHAR2,
i_file IN VARCHAR2,
i_blob IN BLOB)
AS
l_file UTL_FILE.file_type;
l_buffer RAW (32767);
l_amount BINARY_INTEGER := 32767;
l_pos INTEGER := 1;
l_blob_len INTEGER;
BEGIN
l_blob_len := DBMS_LOB.getlength (i_blob);
l_file :=
UTL_FILE.fopen (i_dir,i_file,'WB',32767);
WHILE l_pos < l_blob_len
LOOP
DBMS_LOB.read (i_blob,
l_amount,
l_pos,
l_buffer);
UTL_FILE.put_raw (l_file, l_buffer, TRUE);
l_pos := l_pos + l_amount;
END LOOP;
UTL_FILE.fclose (l_file);
EXCEPTION
WHEN OTHERS
THEN
IF UTL_FILE.is_open (l_file)
THEN
UTL_FILE.fclose (l_file);
END IF;
RAISE;
END blob_to_file;
/
8. Use Below PL/SQL Block to download the file
DECLARE
v_blob BLOB;
BEGIN
select FILE_CONTENT into v_blob from POC_FILE_STORAGE where FILE_NAME='OCA.pdf';
blob_to_file ('DB_FILES', 'OCA.pdf', v_blob);
END;
/
