Solved

insert into blob field through sql

Posted on 2004-04-19
4
30,506 Views
Last Modified: 2007-12-19
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
Comment
Question by:deepaliakolkar
4 Comments
 
LVL 6

Accepted Solution

by:
musdu earned 63 total points
ID: 10857239
Hi,

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

regards.
0
 
LVL 8

Assisted Solution

by:baonguyen1
baonguyen1 earned 62 total points
ID: 10857295
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now