Storing Images in Oracle Blob [message #101078] |
Tue, 20 April 2004 22:35 |
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 #116510 is a reply to message #110072] |
Wed, 20 April 2005 08:20 |
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 |
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 |
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 #254492 is a reply to message #124608] |
Thu, 26 July 2007 23:17 |
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.
|
|
|
|
|