How Can i Store photo (image) in column of table

This assumes that the images in question currently reside on the database server. If you want to upload images into the database from a client machine, you will need an extra step where you move the files to the appropriate directory or directories on the server. This example will store the images in a BLOB column, rather than a much more flexible interMedia ORDImage column. A BLOB column is fine if you only want to store and retrieve the image, but interMedia gives you significantly more flexibility to manipulate the image, extract metadata, etc.

Create the directory

First, you will want to tell Oracle where it can find the images you want to insert into the database. The easiest way to do this is to create a directory object. In this case, I will be loading images from 'c:/temp/img/'.



CREATE DIRECTORY images AS 'c:/temp/img/'


If you don't have the CREATE DIRECTORY privilege, you will need to have someone else, like your DBA, create the directory and grant you READ access to it.



GRANT READ ON DIRECTORY images TO scott;


Create the Table to Hold the Images



CREATE TABLE images (
image_name VARCHAR2( 100 ),
mime_type VARCHAR2( 100 ),
content BLOB
)


Create a Stored Procedure to Load the Image



CREATE OR REPLACE PROCEDURE load_image( file_name VARCHAR2 )
AS
file_lob BFILE;
binary_lob BLOB;
mime_type images.mime_type%type;
extension_pos NUMBER;
BEGIN
-- Use the extension of the file name to determing the MIME-type
-- The MIME-type for a .JPG file will be image/jpg
extension_pos := INSTR( file_name, '.' );
mime_type := 'image/' || SUBSTR( file_name,
extension_pos + 1,
LENGTH( file_name ) );

-- Insert a new row into the images table. Get the LOB locator
-- for the newly inserted row-- we will be using that to insert
-- the content from the file.
INSERT INTO IMAGES( image_name, mime_type, content )
VALUES( file_name, mime_type, empty_blob() )
RETURNING content INTO binary_lob;

-- Open up the file in the IMAGES directory named file_name,
-- load that file into the newly created LOB locator, and
-- close the file
file_lob := BFILENAME( 'IMAGES', file_name );
dbms_lob.fileOpen ( file_lob, dbms_lob.file_readOnly );
dbms_lob.loadFromFile( binary_lob,
file_lob,
dbms_lob.getLength( file_lob ) );
dbms_lob.fileClose ( file_lob );
END;


Load the Image



scott@jcave > exec load_image( 'sample.jpg' );

PL/SQL procedure successfully completed.

scott@jcave > column image_name format a30;
scott@jcave > column mime_type format a15;
scott@jcave > column length format 999999;
scott@jcave >
1 SELECT image_name, mime_type,dbms_lob.getLength( content ) length
2 FROM images;

no rows selected

Elapsed: 00:00:00.00
scott@jcave > /

IMAGE_NAME MIME_TYPE LENGTH
------------------------------ --------------- -------
sample.jpg image/jpg 9894


Regards
Lakmal