BLOB datatype

[ramya_ram]

hai friends

I have a question:

I wanted to store the picture files in the database. I have created a table with the column datatype BLOB. I would like
to know the sql command to insert a row which will insert a picture in that column and how to display the same once inserted? I have gone through the documentation, but its confusing me!


Thanks friends, i have inserted a row! but what are the options to view the picture which is stored in the database?




[Edited by ramya_ram on 04-04-2001 at 12:48 AM]

[hrishy]

no as far as i know you cannot insert a lob value using simple sql commands u can do it thru sql loader or dbms_lob package or oci if interested mail me at hrishys@yahoo.co.uk

[Ispirer]

Hi,

You can also use SQL Loader to insert rows with pictures. Place a picture to file, specify its name in text file.

CREATE TABLE SAMPLE.EMP_PHOTO
(
EMPNO CHAR(6) NOT NULL,
PHOTO_FORMAT VARCHAR2(10) NOT NULL,
PICTURE BLOB
);

Text file:

000150,bitmap,lobs\sample.emp_photo.0
000150,gif,lobs\sample.emp_photo.1
000150,xwd,lobs\sample.emp_photo.2
000190,bitmap,lobs\sample.emp_photo.3
000190,gif,lobs\sample.emp_photo.4
000190,xwd,lobs\sample.emp_photo.5

LOAD DATA
INFILE 'sample.emp_photo.txt'
INTO TABLE sample.emp_photo
INSERT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(EMPNO,
PHOTO_FORMAT,
PICTURE_FILE FILLER,
PICTURE LOBFILE (PICTURE_FILE) TERMINATED BY EOF)


Best regards, Dmitry
--
http://www.ispirer.com - Database migration tools and services for Oracle and IBM DB2.
http://www.ispirer.com/chyfo.html - Tool exports data to CSV flat file, generates CREATE TABLE, CREATE INDEX scripts for Oracle and control files for SQL Loader.

[tamilselvan]

Additonal info: Always use default value for BLOB/CLOB column.

CREATE TABLE SAMPLE.EMP_PHOTO
(
EMPNO CHAR(6) NOT NULL,
PHOTO_FORMAT VARCHAR2(10) NOT NULL,
PICTURE BLOB DEFAULT EMPTY_BLOB()
);

Then use SQL*LOADER to insert data.

The other option is using JAVA, or Developer 2000 to insert new data in the table.

Keep BLOB data in a separate tablespace away from the default tablespace.