Showing posts with label dbfs. Show all posts
Showing posts with label dbfs. Show all posts

Monday, January 19, 2015

PL SQL procedure to save a file from a Filesystem to BLOB column

DECLARE
   v_src_loc   BFILE := BFILENAME ('ORACLE_DIR', 'FILE_NAME');
   v_amount    INTEGER;
   v_b         BLOB;
BEGIN
   DBMS_LOB.OPEN (v_src_loc, DBMS_LOB.LOB_READONLY);
   v_amount := DBMS_LOB.GETLENGTH (v_src_loc);

      UPDATE BLOB_TABLE
         SET BLOB_COLUMN = EMPTY_BLOB ()
   RETURNING BLOB_COLUMN 
        INTO v_b;

   DBMS_LOB.LOADFROMFILE (v_b, v_src_loc, v_amount);
   DBMS_LOB.CLOSE (v_src_loc);
   commit;
END;
/