Home » SQL & PL/SQL » SQL & PL/SQL » storing a image file in a table
storing a image file in a table [message #37826] Fri, 01 March 2002 00:34 Go to next message
Magesh Kumar .R
Messages: 1
Registered: March 2002
Junior Member
What is the query that one can use to store a image file in a table? after storing will it be possible to retrieve it through ADO and display it using .asp page. if yes, how?
Re: storing a image file in a table [message #37827 is a reply to message #37826] Fri, 01 March 2002 01:01 Go to previous messageGo to next message
pratap kumar tripathy
Messages: 660
Registered: January 2002
Senior Member
1.Create a table to store the blobs:
create table blobs
( id varchar2(255),
blob_col blob
);

2. Create a logical directory in the database to the physical file system:

create or replace directory MY_FILES as 'c:images';

3.
Create a procedure to load the blobs from the file system using the logical
directory. The gif "aria.gif" must exist in c:images.

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', 'aria.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;
/
Re: storing a image file in a table [message #343381 is a reply to message #37827] Wed, 27 August 2008 02:15 Go to previous messageGo to next message
diwakarsharma
Messages: 8
Registered: August 2008
Junior Member
hi
i followed above process but images are not dumping into database
i checked it using query
select count(id) from blobs
please tell me the solution
thank you in advance
Re: storing a image file in a table [message #343383 is a reply to message #343381] Wed, 27 August 2008 02:33 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is it giving an error message when you run it?
Re: storing a image file in a table [message #343616 is a reply to message #37826] Wed, 27 August 2008 11:39 Go to previous messageGo to next message
diwakarsharma
Messages: 8
Registered: August 2008
Junior Member
no it is showing answer as 0
Re: storing a image file in a table [message #343619 is a reply to message #343616] Wed, 27 August 2008 11:58 Go to previous messageGo to next message
tyler_durden
Messages: 14
Registered: August 2008
Location: http://tinyurl.com/63fmwx
Junior Member
Would you like to paste your sqlplus session that shows the following ?

(1) the creation of the table
(2) the creation of the directory
(3) the creation of the procedure (with the actual code displayed)
(4) the execution of the procedure
(5) the final select from the table that returns a count of 0

It will be much easier for us and we'd be more inclined to look into your problem.

tyler_durden

Re: storing a image file in a table [message #343706 is a reply to message #37826] Wed, 27 August 2008 20:12 Go to previous messageGo to next message
diwakarsharma
Messages: 8
Registered: August 2008
Junior Member
The follwing is the SQL plus SESSION


SQL> create table blobs
2 ( id varchar2(255),
3 blob_col blob
4 );

Table created.

SQL> create or replace directory FILES as 'c:images';

Directory created.

SQL> create or replace procedure insert_img as
2 f_lob bfile;
3 b_lob blob;
4 begin
5 insert into blobs values ( 'MyGif', empty_blob() )
6 return blob_col into b_lob;
7
8 f_lob := bfilename( 'FILES', 'east.gif' );
9 dbms_lob.fileopen(f_lob, dbms_lob.file_readonly);
10 dbms_lob.loadfromfile( b_lob, f_lob,dbms_lob.getlength(f_lob) );
11 dbms_lob.fileclose(f_lob);
12 commit;
13 end;
14 /

Procedure created.

SQL> select count(id) from blobs;

COUNT(ID)
----------
0

Re: storing a image file in a table [message #343708 is a reply to message #37826] Wed, 27 August 2008 20:19 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>(4) the execution of the procedure
It appears you skipped a step.
Re: storing a image file in a table [message #343725 is a reply to message #343706] Wed, 27 August 2008 22:58 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Diwakar,

Insert some data into that table by executing that procedure insert_img. You just have created the procedure, and it will not fill data into table for you. That is why your "count(id)" is giving value 0. Do data insertion and then try that query.

Regards,
Dipali.
Re: storing a image file in a table [message #343907 is a reply to message #343725] Thu, 28 August 2008 07:41 Go to previous messageGo to next message
diwakarsharma
Messages: 8
Registered: August 2008
Junior Member
can any one please tell me the query to store data using already created procedures
Re: storing a image file in a table [message #343918 is a reply to message #343907] Thu, 28 August 2008 08:00 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Diwakar,

you have created following procedure as you said:
Quote:
SQL> create or replace procedure insert_img as
2 f_lob bfile;
3 b_lob blob;
4 begin
5 insert into blobs values ( 'MyGif', empty_blob() )
6 return blob_col into b_lob;
7 
8 f_lob := bfilename( 'FILES', 'east.gif' );
9 dbms_lob.fileopen(f_lob, dbms_lob.file_readonly);
10 dbms_lob.loadfromfile( b_lob, f_lob,dbms_lob.getlength(f_lob) );
11 dbms_lob.fileclose(f_lob);
12 commit;
13 end;
14 /

Procedure created.



You have hardcoded the image file name in your procedure.

-->Now put some image file with name 'east.gif' in the directory which is being pointed by you Directory object 'FILES' (which you have used in your procedure).

-->Then just execute this procedure:
i.e something like.
SQL> exec insert_img

That's it..

Do this and paste here the session if any problem occurs..

Regards,
Dipali..
Re: storing a image file in a table [message #344069 is a reply to message #343918] Thu, 28 August 2008 13:03 Go to previous messageGo to next message
diwakarsharma
Messages: 8
Registered: August 2008
Junior Member
thank u frns
i got it
Re: storing a image file in a table [message #347694 is a reply to message #343708] Fri, 12 September 2008 21:58 Go to previous messageGo to next message
diwakarsharma
Messages: 8
Registered: August 2008
Junior Member
once again i am facing same problem
now till procedure creation it was success full
but procedure is not executing
the code is as follows

create or replace directory images as 'c:\program files\apache software foundation\tomcat 5.5\webapps\temples\images;

Directory created.

SQL> ed
Wrote file afiedt.buf

1 create or replace procedure insertimage as
2 f_lob bfile;
3 b_lob blob;
4 begin
5 insert into tempphoto values ( 's_ap1_p1','s_ap1', empty_blob() )
6 return tempphoto into b_lob;
7 f_lob := bfilename( 'images', 'tirupati.jpeg' );
8 dbms_lob.fileopen(f_lob, dbms_lob.file_readonly);
9 dbms_lob.loadfromfile( b_lob, f_lob,dbms_lob.getlength(f_lob) );
10 dbms_lob.fileclose(f_lob);
11 commit;
12* end;
SQL> /

Procedure created.



SQL> exec insertimage;
BEGIN insertimage; END;

*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "OOSE.INSERTIMAGE", line 8
ORA-06512: at line 1



can any please tell me the solution


thank you in advance
Re: storing a image file in a table [message #347695 is a reply to message #347694] Fri, 12 September 2008 22:33 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
The Oracle directory object, IMAGES, must be in upper case:

f_lob := bfilename( 'IMAGES', 'tirupati.jpeg' );
Re: storing a image file in a table [message #347696 is a reply to message #37826] Fri, 12 September 2008 22:38 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

22285, 00000, "non-existent directory or file for %s operation"
// *Cause: Attempted to access a directory that does not exist, or attempted
//         to access a file in a directory that does not exist.
// *Action: Ensure that a system object corresponding to the specified
//          directory exists in the database dictionary, or
//          make sure the name is correct.


Please provide proof that
'c:\program files\apache software foundation\tomcat 5.5\webapps\temples\images'
exists & is writable by user Oracle.

Why does your posted "code" have unbalanced single quote marks?
>create or replace directory images as 'c:\program files\apache software foundation\tomcat 5.5\webapps\temples\images;

Re: storing a image file in a table [message #347697 is a reply to message #37826] Fri, 12 September 2008 22:51 Go to previous messageGo to next message
diwakarsharma
Messages: 8
Registered: August 2008
Junior Member
TO ANACEDENT:

sorry for inconvenience
SQL> create or replace directory images as 'c:\program files\apache software foundation\tomcat5.5\we
bapps\temples\images';

Directory created.




TO Barbara Boehmer

i gave images in upper case and tried but no result

SQL> exec insertimage;
BEGIN insertimage; END;

*
ERROR at line 1:
ORA-22288: file or LOB operation FILEOPEN failed
The system cannot find the file specified.
ORA-06512: at "SYS.DBMS_LOB", line 504
ORA-06512: at "OOSE.INSERTIMAGE", line 8
ORA-06512: at line 1

Re: storing a image file in a table [message #347699 is a reply to message #37826] Fri, 12 September 2008 23:06 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
You choose to NOT follow Posting Guidelines, I choose to no longer respond.

You're On Your Own (YOYO)!
Re: storing a image file in a table [message #347701 is a reply to message #347697] Fri, 12 September 2008 23:21 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8620
Registered: November 2002
Location: California, USA
Senior Member
Since you got a different error message, it appears that solved the directory problem, but now it is unable to find or read the file. You need to make sure that the file is in the directory, that the extension is correct, and that Oracle has access to it at the operating system level and the Oracle user has been granted access through the directory object:

GRANT READ, WRITE ON DIRECTORY IMAGES TO ...;

Re: storing a image file in a table [message #347704 is a reply to message #37826] Sat, 13 September 2008 00:36 Go to previous messageGo to next message
diwakarsharma
Messages: 8
Registered: August 2008
Junior Member
u asked to grant privelages to user oose(my username)
we can grant privelages from system
but my directory IMAGES is in user oose
so from which i want to gran privelages
Re: storing a image file in a table [message #347706 is a reply to message #347704] Sat, 13 September 2008 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If user is creator of the Oracle directory, he does not need to be granted the read/write privileges.

Next steps:
Quote:
You need to make sure that the file is in the directory, that the extension is correct, and that Oracle has access to it at the operating system level

Regards
Michel

[Updated on: Sat, 13 September 2008 00:40]

Report message to a moderator

Re: storing a image file in a table [message #347708 is a reply to message #37826] Sat, 13 September 2008 01:37 Go to previous messageGo to next message
micheelgeorge
Messages: 1
Registered: September 2008
Junior Member
In this article, I had used SQL Server 2005 as back end and C# as front end. SQL Server has “Image” data type to store the image. In Oracle and some other database you can use a data type which is used to store binary value (may be BLOB). I have created a simple aspx which has File upload control and a button.
-------------
MICHEEL

[Updated on: Sat, 13 September 2008 02:06] by Moderator

Report message to a moderator

Re: storing a image file in a table [message #347711 is a reply to message #347708] Sat, 13 September 2008 02:09 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post what you did in Oracle.
What you did in SQL Server is irrelevant here.

And don't post marketing link, ads MUST be posted in Marketplace forum and ONLY if they are related to Oracle.

Regards
Michel
Previous Topic: to_date of timestamp field
Next Topic: ORA-02180: invalid option for CREATE TABLESPACE
Goto Forum:
  


Current Time: Fri Dec 02 12:38:19 CST 2016

Total time taken to generate the page: 0.06365 seconds