SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to insert blob file into the database

941614Jun 7 2012 — edited Jun 7 2012

HI i have a table with the following structure:

SQL> desc category_details;
Name Null? Type
----------------------------------------- -------- ----------------------------
CATEGORY_ID NOT NULL NUMBER(20)
CATEGORY_NAME NOT NULL VARCHAR2(25)
DESCRIPTION NOT NULL CLOB
PICTURE NOT NULL BLOB

Now i want to insert values into the table..What is the syntax for it???

how to insert the blob value??????

Manguilibe KAO

Hi,

It's well explained in the documentation:

http://docs.oracle.com/cd/E11882_01/appdev.112/e18294/adlob_ddl.htm#g1012364

Edited by: Manguilibè KAO on 6 juin 2012 22:31

Hoek

Use the TO_LOB function. See:
http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions205.htm#SQLRF06134

You can find more examples if you do a search on http://www.oracle.com/pls/db112/homepage
or http://asktom.oracle.com

908002

here is the example..

CREATE TABLE lob_table (id NUMBER, doc BLOB);
INSERT INTO lob_table VALUES (1, EMPTY_BLOB()); 

DECLARE
  src_lob  BFILE := BFILENAME('MY_DIR', '/tmp/me.gif');
  dest_lob BLOB;
BEGIN
  INSERT INTO lob_table VALUES(2, EMPTY_BLOB())
     RETURNING doc INTO dest_lob;

  DBMS_LOB.OPEN(src_lob, DBMS_LOB.LOB_READONLY);
  DBMS_LOB.LoadFromFile( DEST_LOB => dest_lob,
                         SRC_LOB  => src_lob,
                         AMOUNT   => DBMS_LOB.GETLENGTH(src_lob) );
  DBMS_LOB.CLOSE(src_lob);

  COMMIT;
END;
/

ShankarViji

Hi,

Step to Follow :

1. Create a directory object pointing to a physical directory on the database server that the "oracle" user has read/write permissions on.

CONN / AS SYSDBA
CREATE OR REPLACE DIRECTORY IMAGES ost/';
GRANT READ, WRITE ON DIRECTORY images TO db_user_name;

2. Procedure to Load the BLOB File.

DECLARE
  l_dir    VARCHAR2(10) := 'IMAGES'; -------Is the Directory Object Created Above.
  l_file   VARCHAR2(20) := 'site_logo.gif'; ------ Is the BLOB File that is present in the Directory mentioned.
  l_bfile  BFILE;
  l_blob   BLOB;
BEGIN
  INSERT INTO images (id, name, image)
  VALUES (1,l_file, empty_blob())
  RETURN image INTO l_blob;

  l_bfile := BFILENAME(l_dir, l_file);
  DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
  DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));
  DBMS_LOB.fileclose(l_bfile);

  COMMIT;
END;

Thanks,
Shankar

1 person found this helpful

1 - 4

Locked Post

New comments cannot be posted to this locked post.

Locked on Jul 5 2012

Added on Jun 7 2012

4 comments

177,949 views

SQL & PL/SQL

How to insert blob file into the database

Comments

Post Details