How to create a table with Blob data type and insert data

Working with Blob Datatypes

Step 1: 

To work with lobs you must Create a table with columns of  lob data types.

SQL> create table tab1 (col1 number(4),
  2  col2 blob);

Table created.

Step 2:

Insert a row and initialize the blob with Empty_blob() so that it will generate a lob locator.

SQL> insert into tab1 values(1,empty_blob());

1 row created.

Step 3: 

Create a procedure / anonymous block to write the data into the blob. Here i am creating an anonymous block. The below procedure will load the data into blob.

SQL> declare

  2  v_lob blob;

  3  vamt integer:=32767;

  4  offst integer:=1;

  5  vbuff raw(32767):=utl_raw.cast_to_raw(rpad('NUTHAN',32767,'A'));

  6  begin

  7  select col2 into v_lob from tab1 for update;

  8  dbms_lob.write(v_lob,vamt,offst,vbuff);

  9  exception

 10  when others then

 11  dbms_output.put_line(dbms_utility.format_error_stack);

 12  end;

 13  /

PL/SQL procedure successfully completed.

Step 4:

Query the table now you can see data is populated into the blob and it will show the data in binary format.

Key points to remember: 

1. Blob's store the data only in binary format. Hence the parameters like(amount,offset,length ) are specified in terms of bytes.
2. The values of amount , length, offset values must not be more than maxlobsize (264).
3. Only Positive values of parameters are allowed. negative numbers from the tail of a lob are not allowed.
4. Buffer used for Blob is of Raw datatype. and max size of raw allowed is (32767).
5. Must lock the row of the blob that needs to be modified by using any of the procedure (Write, Append, Copy, Erase, Trim) else oracle will raise an error.