Home » Infrastructure » Windows » Storing Images in Oracle Blob
Storing Images in Oracle Blob [message #101078] Tue, 20 April 2004 22:35 Go to next message
prashant
Messages: 122
Registered: September 2000
Senior Member
Hi,

I'm in need of a solution using Visual Basic to insert and retrieve images in .bmp format into Oracle Blob. The images are currently stored on local hard drive and need to load those into Oracle database 8i.

Prashant
Re: Storing Images in Oracle Blob [message #101147 is a reply to message #101078] Wed, 26 May 2004 05:46 Go to previous messageGo to next message
Bosetti kiran Soumya
Messages: 1
Registered: May 2004
Junior Member
Through Visual Basic I want to insert & read the images
from the oracle database

Plzzzzz Helpppppp Me!!

Bye!!
Re: Storing Images in Oracle Blob [message #101169 is a reply to message #101078] Wed, 02 June 2004 04:02 Go to previous messageGo to next message
Gurunathan
Messages: 3
Registered: June 2002
Junior Member
i need a step in insert picture in oracle database table. can u help me
Re: Storing Images in Oracle Blob [message #101320 is a reply to message #101169] Thu, 05 August 2004 05:33 Go to previous messageGo to next message
Dinesh
Messages: 31
Registered: October 2001
Member
i want to know that how pictures and big files are stored in a oracle table ? i didn't find a proper solution of it in the net .

Dinesh Chand
Re: Storing Images in Oracle Blob [message #101327 is a reply to message #101169] Mon, 09 August 2004 23:34 Go to previous messageGo to next message
subbu
Messages: 27
Registered: July 2002
Junior Member
hi,

how to insert the images and .mp3 files in the oracle database.

regards,
subbu
Re: Storing Images in Oracle Blob [message #101618 is a reply to message #101147] Sun, 09 January 2005 23:21 Go to previous messageGo to next message
jajjinakari janare
Messages: 1
Registered: January 2005
Junior Member
Take one image at a time
carefully store in to a table
Storing Images in Oracle Blob [message #101626 is a reply to message #101618] Thu, 20 January 2005 19:46 Go to previous messageGo to next message
srikanth
Messages: 40
Registered: July 2001
Member
hai,
i have a doubt on how to store images in oracle9i.
is it better to store url of the image or the image
can u clarrify it
thanks & regards
srikanth
Re: Storing Images in Oracle Blob [message #110072 is a reply to message #101078] Thu, 03 March 2005 05:34 Go to previous messageGo to next message
ajbuckle
Messages: 1
Registered: March 2005
Location: Sheffield
Junior Member
Does anybody know how to store images and mp3s in Oracle using SqlPlus for windows, im a bit stuck.

Thanks
Andy
Re: Storing Images in Oracle Blob [message #116510 is a reply to message #110072] Wed, 20 April 2005 08:20 Go to previous messageGo to next message
pradipdas99
Messages: 7
Registered: April 2005
Location: Kolkata
Junior Member
To insert image/vedio...
A.connect as sys
CREATE DIRECTORY IMAGES AS 'c:\images'; ( where physical file store in the same machine where database load)
GRANT READ ON DIRECTORY IMAGES TO SCOTT;
B. connect scott/tiger
create table my_book_diagrams (col1...);
B. Write a procedure
DECLARE
v_file_loc BFILE;
v_diagram_loc BLOB;
v_diagram_size INTEGER;
BEGIN
v_file_loc := BFILENAME('IMAGES','ch01_01.bmp');
v_diagram_loc := dbms_lob.fileopen(v_file_loc );
v_diagram_size := DBMS_LOB.GETLENGTH(v_file_loc);
INSERT INTO my_book_diagrams (chapter_descr, diagram_no,
diagram) VALUES ( 'Chapter 1', 1, EMPTY_BLOB() )
RETURNING diagram INTO v_diagram_loc;
DBMS_LOB.LOADFROMFILE(v_diagram_loc, v_file_loc, v_diagram_size);

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm);

END;
/

SELECT chapter_descr,
diagram_no,
dbms_lob.getlength(diagram) diagram_size
FROM my_book_diagrams
WHERE chapter_descr = 'Chapter 1';
This script produces the following:
Diagram size: 481078
Inserting Empty Diagram Row
Loading Diagram From File

CHAPTER_DESCR DIAGRAM_NO DIAGRAM_SIZE
--------------- ---------- ------------
Chapter 1 1 481078



Pkd
Re: Storing Images in Oracle Blob [message #117329 is a reply to message #101078] Tue, 26 April 2005 09:47 Go to previous messageGo to next message
shiren
Messages: 2
Registered: April 2005
Location: hyd
Junior Member
hi,
i did the same but i am having error saying
ORA-22288: file or LOB operation FILEOPEN failed
The filename, directory name, or volume label syntax is incorrect.
Please help me
Re: Storing Images in Oracle Blob [message #124248 is a reply to message #116510] Fri, 17 June 2005 02:08 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member
I tried that code but its giving an error.
The error is below :

1 DECLARE
2 v_file_loc BFILE;
3 v_diagram_loc BLOB;
4 v_diagram_size INTEGER;
5 BEGIN
6 v_file_loc := BFILENAME('IMAGES','4ded.jpeg');
7 v_diagram_loc := dbms_lob.fileopen(v_file_loc );
8 v_diagram_size := DBMS_LOB.GETLENGTH(v_file_loc);
9 INSERT INTO scott.my_images VALUES ( 'Chapter 1', 1, EMPTY_BLOB() )
10 RETURNING diagram INTO v_diagram_loc;
11 DBMS_LOB.LOADFROMFILE(v_diagram_loc, v_file_loc, v_diagram_size);
12 COMMIT;
13 EXCEPTION
14 WHEN OTHERS
15 THEN
16 DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm);
17* end;
SQL> /
v_diagram_loc := dbms_lob.fileopen(v_file_loc );
*
ERROR at line 7:
ORA-06550: line 7, column 19:
PLS-00222: no function with name 'FILEOPEN' exists in this scope
ORA-06550: line 7, column 2:
PL/SQL: Statement ignored


Now what to do
Re: Storing Images in Oracle Blob [message #124299 is a reply to message #124248] Fri, 17 June 2005 06:42 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
tarundba wrote on Fri, 17 June 2005 09:08

Now what to do

Now it's time to read the documentation.

FILEOPEN Procedure

This procedure opens a BFILE for read-only access. BFILEs may not be written through Oracle.
Syntax

DBMS_LOB.FILEOPEN (
file_loc IN OUT NOCOPY BFILE,
open_mode IN BINARY_INTEGER := file_readonly);

Parameters
Parameter   Description
---------   -----------
file_loc    Locator for the BFILE.
open_mode   File access is read-only.


So, DBMS_LOB.FILEOPEN is no functionn but a procedure. Hey, that's exactly what the PLS-000222 message said...that's odd Wink

Modify your code to use FILEOPEN as a procedure.

MHE
Re: Storing Images in Oracle Blob [message #124307 is a reply to message #124299] Fri, 17 June 2005 07:41 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member

Please send me the full code for storing image.

I would be really thankful
Re: Storing Images in Oracle Blob [message #124335 is a reply to message #124307] Fri, 17 June 2005 10:32 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Instead of copying and pasting the code, I'll show you the source (one of many).


HTH,
MHE
Re: Storing Images in Oracle Blob [message #124541 is a reply to message #124335] Mon, 20 June 2005 01:06 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member
Thanx it worked n the image is stored. now tell me , whether its a link to the image or the image is stored inside oracle itself.
Re: Storing Images in Oracle Blob [message #124577 is a reply to message #124541] Mon, 20 June 2005 06:11 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It's the image.
Again, from the documentation

LOADFROMFILE Procedure

This procedure copies all, or a part of, a source external LOB (BFILE) to a destination internal LOB
.

Again, I think the documentation should be the first place to look. If you're unsure about it's location: http://tahiti.oracle.com . Bookmark it. Now. The site requires a free subscription.

MHE
Re: Storing Images in Oracle Blob [message #124582 is a reply to message #124577] Mon, 20 June 2005 06:28 Go to previous messageGo to next message
tarundua
Messages: 1080
Registered: June 2005
Location: India
Senior Member
Thanks Maaher,

Thanks a lot for giving me the link. now i wil see the documentation also.

Re: Storing Images in Oracle Blob [message #124583 is a reply to message #124577] Mon, 20 June 2005 06:28 Go to previous messageGo to next message
pradipdas99
Messages: 7
Registered: April 2005
Location: Kolkata
Junior Member
dbms_lob.loadfromfile( l_blob, l_bfile,
dbms_lob.getlength( l_bfile ) );
This procedure copies all, not a part of .
pradip
Re: Storing Images in Oracle Blob [message #124608 is a reply to message #124577] Mon, 20 June 2005 08:29 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Maaher wrote on Mon, 20 June 2005 13:11

LOADFROMFILE Procedure

This procedure copies all, or a part of, a source external LOB (BFILE) to a destination internal LOB.
That's what the documentations says, yes: all, or a part of. Thanks for the echo Very Happy

MHE
Re: Storing Images in Oracle Blob [message #254492 is a reply to message #124608] Thu, 26 July 2007 23:17 Go to previous messageGo to next message
prakshep_b_jani
Messages: 1
Registered: July 2007
Location: gujarat
Junior Member
Hello Friends,
I want to store mp3 files in database. I used following code for storing images as an example but it giving me Exception ORA-22285.Codes i ran are as below please help me i m stucked in this.

CREATE DIRECTORY jani AS 'c:\Jani'

GRANT READ ON DIRECTORY jani TO SCOTT;

DECLARE
v_file_loc BFILE;
v_diagram_loc BLOB;
v_diagram_size INTEGER;
BEGIN
v_file_loc := BFILENAME('jani','our.bmp');
--dbms_output.put_line('first'||v_file_loc);
DBMS_LOB.FILEOPEN(v_file_loc,dbms_lob.lob_readonly );
v_diagram_size := DBMS_LOB.GETLENGTH(v_file_loc);
dbms_output.put_line('before'||v_diagram_size);
INSERT INTO my_book_diagrams(no,diagram) VALUES ( 1, EMPTY_BLOB() )
RETURNING diagram INTO v_diagram_loc;
dbms_output.put_line('after'||v_diagram_size);
DBMS_LOB.LOADFROMFILE(v_diagram_loc, v_file_loc, v_diagram_size);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm);
end;
/

OTHERS Exception ORA-22285: non-existent directory or file for FILEOPEN
operation

PL/SQL procedure successfully completed.
Re: Storing Images in Oracle Blob [message #254535 is a reply to message #254492] Fri, 27 July 2007 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ora-22285: 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.

1/ Directory must be on the SERVER
2/ CREATE DIRECTORY does not create an OS directory, it creates a reference to this directory in Oracle.

Regards
Michel
Re: Storing Images in Oracle Blob [message #255065 is a reply to message #101078] Mon, 30 July 2007 09:51 Go to previous message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Interesting that a 2 year old thread got revived .
Previous Topic: Error Occured During patching Oracle 10g Database on Windows
Next Topic: Oracle Job did not start after promoting to PDC
Goto Forum:
  


Current Time: Fri Dec 09 07:44:12 CST 2016

Total time taken to generate the page: 0.11354 seconds