Load File into Database as Blob

You can find lots of procedure or steps to load file from filesystem to database.
one of the easy method is to make a function.
Sample of function:-
CREATE OR REPLACE FUNCTION FN_FILE_BLOB(V_FILENAME VARCHAR2, V_DIRECTORY VARCHAR2) RETURN BLOB
IS
src_loc BFILE;
dest_loc BLOB:=empty_blob();
BEGIN
SRC_LOC := BFILENAME(V_DIRECTORY, V_FILENAME);
DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
DBMS_LOB.CREATETEMPORARY(lob_loc => dest_loc, cache => true , dur => dbms_lob.session);
DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
DBMS_LOB.LOADFROMFILE(dest_lob => dest_loc, src_lob => src_loc , amount => DBMS_LOB.getLength(src_loc));
DBMS_LOB.CLOSE(src_loc);
DBMS_LOB.CLOSE(dest_loc);
RETURN dest_loc;
EXCEPTION
WHEN Others THEN
Dbms_Output.Put_Line(SQLERRM);
RETURN NULL;
END;
/

Usage: SELECT FN_FILE_BLOB('image1.jpg','DUMP_DIR') FROM DUAL;

Leave a Reply

Your email address will not be published. Required fields are marked *