Home » SQL & PL/SQL » SQL & PL/SQL » File operation(UTL_FILE) (Oracle 10g,Win XP)
File operation(UTL_FILE) [message #383906] Sat, 31 January 2009 02:38 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

Hi All , i have a sample file a.txt i am reading data from it and writing to another file but
if there is a data after an enter character it is not getting those datas from the file
a.txt .How can i solve this issue

declare
   kml UTL_FILE.FILE_TYPE;
   kmz UTL_FILE.FILE_TYPE;
   strbufferbl blob ;
   strbuffer clob;
   stbuffer blob ;
  
begin
  
   kml:=UTL_FILE.FOPEN('SDO_ROUTER_LOG_DIR','a.txt','R',32767); 
   UTL_FILE.GET_LINE (kml,strbuffer);
   
   strbufferbl:=TO_BLOB(UTL_RAW.CAST_TO_RAW(strbuffer));
    
    kmz:=UTL_FILE.FOPEN('SDO_ROUTER_LOG_DIR','a.zip','w',32767);
   UTL_FILE.put (kmz, strbuffer );
   utl_file.fclose(kml);
   utl_file.fclose(kmz);
    
  end;  


Thanks in Advance
Re: File operation(UTL_FILE) [message #383908 is a reply to message #383906] Sat, 31 January 2009 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
By default, UTL_FILE and especially GET_LINE works on text file and so on line (as the name says it).
If you want to work on binary file use GET_RAW.
You also have to change your open mode (see doc above).

Same thing for writing, your current code is not correct.

Regards
Michel

[Updated on: Sat, 31 January 2009 02:55]

Report message to a moderator

Re: File operation(UTL_FILE) [message #383910 is a reply to message #383908] Sat, 31 January 2009 03:42 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


Ok thanks Michel . I have one more query regarding this

I am writing data from a table which contains 1 million records to a file which good
option experts recommend utl_file or dbms_lob
Re: File operation(UTL_FILE) [message #383911 is a reply to message #383910] Sat, 31 January 2009 03:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I recommend that you benchmark both solutions.
It is the only way to know which one will work best in your environment.

Regards
Michel
Re: File operation(UTL_FILE) [message #383916 is a reply to message #383911] Sat, 31 January 2009 04:07 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

K Thanks Ya its working get_raw & put_raw .Here how can i incoporate UTL_COMPRESS.lz_compress

declare
   kml UTL_FILE.FILE_TYPE;
   kmz UTL_FILE.FILE_TYPE;
  strbufferdd raw(4000);
begin 
   kml:=UTL_FILE.FOPEN('SDO_ROUTER_LOG_DIR','a.txt','R',32767);   
   utl_file.get_raw(kml, strbufferdd,9090006);  
    
   kmz:=UTL_FILE.FOPEN('SDO_ROUTER_LOG_DIR','a.zip','w',32767);
          -- UTL_COMPRESS.lz_compress 
   Utl_File.Put_Raw (kmz,strbufferdd ); 
   
   utl_file.fclose(kml);     
   utl_file.fclose(kmz);      
  end;  
Re: File operation(UTL_FILE) [message #383919 is a reply to message #383916] Sat, 31 January 2009 05:00 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

how to incorporate utl_compress.lz_compress in the above code .Experts Please Help

Thanks in Advance
Re: File operation(UTL_FILE) [message #383922 is a reply to message #383919] Sat, 31 January 2009 05:45 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

I got it
declare
   kml UTL_FILE.FILE_TYPE;
   kmz UTL_FILE.FILE_TYPE;
    strbufferdd raw(4000);
begin 
   kml:=UTL_FILE.FOPEN('SDO_ROUTER_LOG_DIR','a.txt','R',32767);   
   utl_file.get_raw(kml, strbufferdd,9999999);      
   strbufferdd:=UTL_COMPRESS.lz_compress (strbufferdd,6);     
   kmz:=UTL_FILE.FOPEN('SDO_ROUTER_LOG_DIR','a.zip','w',32767);
   Utl_File.Put_Raw (kmz,strbufferdd ); 
   utl_file.fclose(kml);    
   utl_file.fclose(kmz);       
  end;  
Re: File operation(UTL_FILE) [message #383923 is a reply to message #383922] Sat, 31 January 2009 06:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback... and formatting your post.

Regards
Michel
Re: File operation(UTL_FILE) [message #384141 is a reply to message #383923] Mon, 02 February 2009 05:32 Go to previous message
_jum
Messages: 508
Registered: February 2008
Senior Member
I think its better to write BYTE-Format:
kmz:=UTL_FILE.FOPEN('SDO_ROUTER_LOG_DIR','a.zip','wb',32767);

Previous Topic: I think I need a pivot table. Not sure...
Next Topic: Hierarchical Query
Goto Forum:
  


Current Time: Fri Dec 02 20:38:22 CST 2016

Total time taken to generate the page: 0.16448 seconds