loading images and sound files

[sumit malik]

hi folks...can somebody tell me how to insert images and sound files in oracle table (using lobs) and to retrive them....
thanx in advance...

[Beksy Kurian]

Sumit,
These are the steps to insert an image into a blob column.
Step 1.
-------
Create a table to store the blobs:
create table blobs
( id varchar2(255),
blob_col blob
);

Step 2.
-------
Create a logical directory in the database to the physical file system:
create or replace directory MY_FILES as 'c:\images';

Step 3.
-------
Create a procedure to load the blobs from the file system using the logical directory.

create or replace procedure insert_img as
f_lob bfile;
b_lob blob;
begin
insert into blobs values ( 'MyGif', empty_blob() )
return blob_col into b_lob;
f_lob := bfilename( 'MY_FILES', 'whateverimage.gif' );
dbms_lob.fileopen(f_lob, dbms_lob.file_readonly);
dbms_lob.loadfromfile( b_lob, f_lob, dbms_lob.getlength(f_lob) );
dbms_lob.fileclose(f_lob);
commit;
end;
/
Hope it helps
Beksy

[sumit malik]

hi Beksy,
Thanks for ths code.I tried to use this code , the procedure is compiling but when i am executing the procedure it's not executed because it's not finding the directory.
could u tell why is this so.
cheers
sumit malik

Originally posted by Beksy Kurian:
Sumit,
These are the steps to insert an image into a blob column.
Step 1.
-------
Create a table to store the blobs:
create table blobs
( id varchar2(255),
blob_col blob
);

Step 2.
-------
Create a logical directory in the database to the physical file system:
create or replace directory MY_FILES as 'c:\images';

Step 3.
-------
Create a procedure to load the blobs from the file system using the logical directory.

create or replace procedure insert_img as
f_lob bfile;
b_lob blob;
begin
insert into blobs values ( 'MyGif', empty_blob() )
return blob_col into b_lob;
f_lob := bfilename( 'MY_FILES', 'whateverimage.gif' );
dbms_lob.fileopen(f_lob, dbms_lob.file_readonly);
dbms_lob.loadfromfile( b_lob, f_lob, dbms_lob.getlength(f_lob) );
dbms_lob.fileclose(f_lob);
commit;
end;
/
Hope it helps
Beksy

[Chandra Sekhar Reddy. N]

Hi
When I run the above program it is giving below error...

BEGIN load_file; END;

*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "APPS.LOAD_FILE", line 25
ORA-06512: at line 1

I create a directory called MY_FILES also..

Still getting same error....

Cheers....
Chandu. N