Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

insert into blob field through sql

Posted on 2004-04-19
4
Medium Priority
?
30,526 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 252 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 248 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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

886 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