Thanks Mason John,

Its great help
-----Original Message-----
From: Mason John
[mailto:oracledba-ezmlmshield-x4907754.[Email address protected]
Sent: Friday, April 04, 2008 5:41 PM
To: LazyDBA Discussion
Subject: RE: Binary format file creation in Oracle with UTL_FILE
possible?

I had to do the same thing a little over a year ago. Here's the
procedure I wrote (with help from Metalink/Oracle Forums). Make sure
your FilePath is setup in the utl_file_dir database parameter.

create or replace
PROCEDURE blob2file (FileName in varchar2, FilePath in varchar2,
MyTableKey in number) IS
vblob BLOB;
vstart NUMBER := 1;
bytelen NUMBER := 32000;
len NUMBER;
my_vr RAW(32000);
x NUMBER;
l_output utl_file.file_type;
BEGIN
-- define output directory
l_output := utl_file.fopen(FilePath, FileName, 'WB', 32760);
-- get length of blob
SELECT dbms_lob.getlength(MyBlobField)
INTO len
FROM MyBlobTable
WHERE MyUniqueKey = MyTableKey;
-- select blob into variable
SELECT MyBlobField
INTO vblob
FROM MyBlobTable
WHERE MyUniqueKey = MyTableKey;
-- save blob length
x := len;

-- if small enough for a single write
IF len < 32760 THEN
utl_file.put_raw(l_output,vblob);
utl_file.fflush(l_output);
ELSE -- write in pieces
vstart := 1;
WHILE vstart < len
LOOP
dbms_lob.read(vblob,bytelen,vstart,my_vr);
utl_file.put_raw(l_output,my_vr);
utl_file.fflush(l_output);

-- set the start position for the next cut
vstart := vstart + bytelen;

-- set the end position if less than 32000 bytes
x := x - bytelen;
IF x < 32000 THEN
bytelen := x;
END IF;
END LOOP;
END IF;
utl_file.fclose(l_output);
END blob2file;
/

-----Original Message-----
From: chandrashekar
[mailto:oracledba-ezmlmshield-x97899774.[Email address protected]
Sent: Friday, April 04, 2008 12:58 AM
To: LazyDBA Discussion
Subject: Binary format file creation in Oracle with UTL_FILE possible?



Hi Gurus,

I have idea that how to create Text from table by using UTL_FILE.

My table has BLOB Field, So I need to write file in Binary format. Is
there any suggestions?

Thanks,
Chandu

***********************
No virus was detected in the attachment no filename

Your mail has been scanned by InterScan.
***********-***********


************************************************************************
************************************************************************
*********************
"This message and any attachments are solely for the intended recipient
and may contain Birlasoft confidential or privileged information. If you
are not the intended recipient,any disclosure,copying, use, or
distribution of the information included in this message and any
attachments is
prohibited. If you have received this communication in error, please
notify us by reply e-mail at ([Email address protected] and permanently
delete this message and any attachments. Thank you."
************************************************************************
************************************************************************
*********************


---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



***********************
No virus was detected in the attachment no filename

Your mail has been scanned by InterScan.
***********-***********


*********************************************************************************************************************************************************************
"This message and any attachments are solely for the intended recipient and may contain Birlasoft confidential or privileged information. If you are not the intended recipient,any disclosure,copying, use, or distribution of the information included in this message and any attachments is
prohibited. If you have received this communication in error, please notify us by reply e-mail at ([Email address protected] and permanently delete this message and any attachments. Thank you."
*********************************************************************************************************************************************************************

Oracle LazyDBA home page