[askdba] Re: [dba_gurus] How to load & extract an Image(TIF) file into/from Oracle-BLOB?
- From: Thiagoo <thiagoodba@xxxxxxxxx>
- To: jcave@xxxxxxxxxxx, subodh_deshpande@xxxxxxxxx, dba_gurus@xxxxxxxxxxxxxxx, askdba@xxxxxxxxxxxxx
- Date: Fri, 13 May 2005 08:01:04 -0700 (PDT)
HI,
Thanks Justin Cave & Subodh!
Justin your suggestion to use put_raw helped. Moreover when I open the file
thru utl_file.fopen, the maxlinesize was default(1000 bytes) this gave file
write error, I changed it to the max(32767), this also helped to solve it.
Now, my source of my image file(tif) is 36130 bytes, when I read and load into
Oracle its still 36130 bytes, I checked using dbms_lob.getlength(blob_obj).
10:40:04 SQL> SELECT dbms_lob.getlength(blob_obj) from thiagu;
DBMS_LOB.GETLENGTH(BLOB_OBJ)
----------------------------
36130
1 row selected.
But, when I read this TIF file and write in a different file, it's one byte
more 36131 bytes. How can I resolve this?
The code which I used to read from DB is:-
====Code Starts here=====
CREATE OR REPLACE PROCEDURE retrieve_blob IS
temp_blob BLOB;
data_buffer RAW (1);
temp_buffer VARCHAR2(1);
amount BINARY_INTEGER := 1;
position INTEGER := 1;
filehandle utl_file.file_type;
error_number NUMBER;
error_message VARCHAR2(100);
length_count INTEGER;
BEGIN
SELECT blob_obj INTO temp_blob FROM thiagu WHERE id = 1;
length_count := dbms_lob.getlength(temp_blob);
dbms_output.put_line('Internal LOB size is: ' || length_count);
filehandle := utl_file.fopen('DIR1','lob_flat.out','W',32767);
WHILE length_count <> 0 LOOP
dbms_lob.read (temp_blob, amount, position, data_buffer);
--temp_buffer := utl_raw.cast_to_varchar2(data_buffer);
utl_file.put_raw(filehandle, data_buffer);
--utl_file.put(filehandle, temp_buffer);
position := position + 1;
length_count := length_count - 1;
data_buffer := null;
END LOOP;
dbms_output.put_line('Exit the loop');
utl_file.fclose(filehandle);
dbms_output.put_line('Close the file');
EXCEPTION
WHEN OTHERS THEN
BEGIN
error_number := sqlcode;
error_message := substr(sqlerrm ,1 ,100);
dbms_output.put_line('Error #: ' || error_number);
dbms_output.put_line('Error Message: ' || error_message);
utl_file.fclose_all;
END;
END;
/
=====Code ends here====
SQL> execute retrieve_blob;
Internal LOB size is: 36130
Exit the loop
Close the file
PL/SQL procedure successfully completed.
-rw-r--r-- 1 uuuu gggg 36130 May 13 09:13 MER046084.tif
-rw-r--r-- 1 oracle gggg 36131 May 13 10:12 lob_flat.out
Thanks & Best Regards,
Thiagu
"Justin Cave (DDBC)" <jcave@xxxxxxxxxxx> wrote:
First, error numbers and a code sample would be helpful.
Second, you need to be using 9i or later to be able to read & write
binary data via UTL_FILE. You also need to use the PUT_RAW function
rather than using PUT.
That doesn't explain the file handle error-- are you sure that you
opened the file first?
Justin Cave <jcave@xxxxxxxxxxx>
Distributed Database Consulting, Inc.
http://www.ddbcinc.com
-----Original Message-----
From: dba_gurus@xxxxxxxxxxxxxxx [mailto:dba_gurus@xxxxxxxxxxxxxxx] On
Behalf Of Thiagoo
Sent: Thursday, May 12, 2005 6:45 PM
To: dba_gurus@xxxxxxxxxxxxxxx; askdba@xxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: [dba_gurus] How to load & extract an Image(TIF) file into/from
Oracle-BLOB?
Hi All,
I want to load few image files into Oracle BLOB columns and later would
Iike to extract it & write in a file.
How to load & extract an Image(TIF) file into/from Oracle-BLOB?, am
using dbms_lob.loadfromfile() to load into Oracle.
I'm retrieving thru dbms_lob.read and writing using utl_file.put, I'm
not able to write in a file.
How can I retrieve this..?When I try to retrieve it say File Handle
error.
If this image file operation is successful, I have few Word/Execl files
to follow-on!
Any help in this regard is highly appreciated!
TIA,
Best Regards,
THIAGU
---------------------------------
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
[Non-text portions of this message have been removed]
Yahoo! Groups Links
---------------------------------
Yahoo! Groups Links
To visit your group on the web, go to:
http://groups.yahoo.com/group/dba_gurus/
To unsubscribe from this group, send an email to:
dba_gurus-unsubscribe@xxxxxxxxxxxxxxx
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
==================================================
Thanks & Best Regards,
Thiagu
==================================================
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Other related posts:
