Oracle Insert BLOB

Shiji Pan

2 months ago

Inserting BLOB data into an Oracle database can be achieved through several methods.

1. Inserting Directly from a Buffer/Variable:

  DECLARE
      v_blob_data BLOB;
  BEGIN
      -- Populate v_blob_data with your binary data
      -- For example, from a RAW variable or a file read into memory
      v_blob_data := UTL_RAW.CAST_TO_RAW('Your binary data here'); 

      INSERT INTO your_table (id, blob_column) VALUES (2, v_blob_data);
      COMMIT;
  END;
  /

2. Inserting from another LOB Column:

INSERT INTO target_table (id, blob_column)
SELECT id, blob_column FROM source_table WHERE id = 123;
COMMIT;

3. Inserting from a File using BFILE and DBMS_LOB:

  CREATE DIRECTORY images_dir AS 'D:\Images'; -- Or '/home/bob/images' for Unix/Linux
  GRANT READ, WRITE ON DIRECTORY images_dir TO your_user;

  DECLARE
      f_lob BFILE;
      b_lob BLOB;
  BEGIN
      INSERT INTO your_table (id, blob_column) VALUES (1, EMPTY_BLOB())
      RETURN blob_column INTO b_lob;

      f_lob := BFILENAME('IMAGES_DIR', 'your_file.bin');
      DBMS_LOB.FILEOPEN(f_lob, DBMS_LOB.FILE_READONLY);
      DBMS_LOB.LOADFROMFILE(b_lob, f_lob, DBMS_LOB.GETLENGTH(f_lob));
      DBMS_LOB.FILECLOSE(f_lob);

      COMMIT;
  END;
  /

And you can use DBBlobEditor to Batch insert Oracle table BLOB data from files.