• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 30534
  • Last Modified:

insert into blob field through sql

i want to insert into a blob field of my table and unable to do it by using sql. will u please help regarding same that what sql stamtement will help me doing the same
2 Solutions

you should use dbms_lob package to insert data into a blob column.

You can use dbms_lob package as musdu:

This is my demo, using dbms_lob.loadfromfile and BLOB datatype:

1. Create a folder, '/db01/test' (Unix) or c:\temp if NT. This folder should be on the host where running the  RDBMS. Do not create on the client :

cd /db01
mkdir test

2. Create a table:

SQL> create table test(id integer, blob_col blob);
table created

3. Create directory:

SQL>create or replace directory dir_of_file as '/db01/test/';
Directory created.

4. Create a procedure:

 CREATE procedure file_into_blob(v_id IN varchar2,v_file_name IN varchar2)
 v_bfile bfile := BFILENAME('DIR_OF_FILE', v_file_name); -- note that uppercase
 v_lob blob;
 insert into test(id,blob_col) values(v_id,empty_blob());
 select blob_col into v_lob from test where id = v_id for update;
 DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));

5. Test it:

SQL>  exec file_into_blob(1, 'image_file.jpg');

PL/SQL procedure successfully completed.

SQL> select count(*) from test;




And from Oracle:

This article contains information on how to read the contents of a binary
file into a database BLOB using PL/SQL.  This is also possible with Pro*C
and OCI, however, it involves a greater cost in time and expertise.
The following is the only method available to read the contents of a binary
file into a database BLOB using PL/SQL:
1.  Create a database directory object to point to the operating system
    directory which contains the file (or files) that you wish to read  
    into a database blob.
      SQL> create or replace directory carldir as '/tmp';
      Directory created.
2.  Create a table that contains a blob column (If one does not already
      SQL> create table blobtab(index_col integer, blob_col blob);
      Table created.
3.  Insert something into the blob column.
      SQL> insert into blobtab(index_col, blob_col)
         values(1, utl_raw.cast_to_raw('this is only a test'));
      1 row created.
      SQL> insert into blobtab(index_col, blob_col)
         values(1, empty_blob());
      1 row created.
      SQL> select count(*) from blobtab
4.  Run the program.  The program is described below.
SQL> @rbfile
Size of input file: 154240
After loadfromfile
Size of blob: 154240
PL/SQL procedure successfully completed.

-- A program that demonstrates that an operating system file containing
-- raw data may be read into a database BLOB column.
  ablob blob;
  abfile bfile := bfilename('CARLDIR', 'cc');  -- Get a pointer to the file.
  amount integer;
  asize integer;
-- Note:  Above, the blobtab table was initialized with one entry.  This
-- is needed because the PL/SQL BLOB locator (ablob) must point to a  
-- specific EXISTING NON-NULL database BLOB.  Also, the select statement  
-- may only return one entry.  If more than one row is returned  
-- (more than one row with index_col set equal to 1), then the script  
-- will fail.
  select blob_col into ablob from blobtab where index_col = 1;
  asize := dbms_lob.getlength(abfile);
  dbms_output.put_line('Size of input file: ' || asize);
  dbms_lob.loadfromfile(ablob, abfile, asize);
  dbms_output.put_line('After loadfromfile');
  asize := dbms_lob.getlength(ablob);
  dbms_output.put_line('Size of blob: ' || asize);
    when others then
      dbms_output.put_line('An exception occurred');
      dbms_output.put_line(sqlcode || sqlerrm);
Hope this helps
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now