Storing Files in Oracle DB's CLOB field

I have a column in my oracle table with CLOB datatype. How do I store .txt file in this column and how can I retrieve the same file?

Below is the table definition

fileID Number
logFile CLOB

Thanks in advance

1 Answer

Loading a file into a CLOB in PL/SQL is pretty easy-- you just need to use the DBMS_LOB.LoadCLOBFromFile procedure

CREATE DIRECTORY file_dir AS <<path on database server file system>>;
GRANT read, write ON file_dir TO your_user_name;
DECLARE l_src_file bfile := BFileName( 'FILE_DIR', <<name of file>> ); l_dest_lob clob; l_dest_offset integer := 1; l_src_offset integer := 1; l_lang_context number := dbms_lob.default_lang_ctx; l_warning number;
BEGIN dbms_lob.open( l_src_file, dbms_lob.lob_readonly ); INSERT INTO some_table( fileID, logFile ) VALUES( fileIDSeq.nextval, empty_clob() ) RETURNING logFile INTO l_dest_lob; dbms_lob.LoadCLOBFromFile( dest_lob => l_dest_lob, src_bfile => l_src_file, amount => dbms_lob.getLength( l_src_file ), dest_offset => l_dest_offset, src_offset => l_src_offset, bfile_csid => dbms_lob.default_csid, lang_context => l_lang_context, warning => l_warning ); dbms_lob.close( l_src_file );
END;
/

Writing the file from the CLOB to the file system again is a bit more involved. I would use something like Tom Kyte's clob_to_file procedure