[askdba] Resolved: Re: [dba_gurus] How to load & extract an Image(TIF) file into/from Oracle-BLOB?

  • From: Thiagoo <thiagoodba@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx, jcave@xxxxxxxxxxx, subodh_deshpande@xxxxxxxxx, dba_gurus@xxxxxxxxxxxxxxx
  • Date: Fri, 13 May 2005 08:10:31 -0700 (PDT)

HI all,
 
Thanks. The one byte errors has been resolved, by manipulating the position..!
 
Thanks,
Thiagu.
Thiagoo <thiagoodba@xxxxxxxxx> wrote:
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)" 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 
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 




==================================================
 
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:

  • » [askdba] Resolved: Re: [dba_gurus] How to load & extract an Image(TIF) file into/from Oracle-BLOB?