Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 30529
  • 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
0
deepaliakolkar
Asked:
deepaliakolkar
2 Solutions
 
musduCommented:
Hi,

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

regards.
0
 
baonguyen1Commented:
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)
 IS
 BEGIN
 DECLARE
 v_bfile bfile := BFILENAME('DIR_OF_FILE', v_file_name); -- note that uppercase
 v_lob blob;
 BEGIN
 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.FILEOPEN(v_bfile, DBMS_LOB.FILE_READONLY);
 DBMS_LOB.LOADFROMFILE ( v_lob, v_bfile, DBMS_LOB.GETLENGTH(v_bfile));
 DBMS_LOB.CLOSE(v_bfile);
 commit;
 END;
 END;
/

5. Test it:


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

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

  COUNT(*)
----------
         1

SQL>

-----------

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.
 
 
Procedure
---------
 
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
    exist).
 
      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.
 
                   or
 
      SQL> insert into blobtab(index_col, blob_col)
         values(1, empty_blob());
 
      1 row created.
 
      SQL> select count(*) from blobtab
         ;
 
        COUNT(*)
      ----------
               1
 
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.
 
------

rbfile.sql:
--
-- A program that demonstrates that an operating system file containing
-- raw data may be read into a database BLOB column.
--
declare
  ablob blob;
  abfile bfile := bfilename('CARLDIR', 'cc');  -- Get a pointer to the file.
  amount integer;
  asize integer;
begin
--
-- 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;
  dbms_lob.fileopen(abfile);
  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);
  exception
    when others then
      dbms_output.put_line('An exception occurred');
      dbms_output.put_line(sqlcode || sqlerrm);
end;
 
Hope this helps
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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