Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE extract to multiple files
UTL_FILE extract to multiple files [message #191875] Fri, 08 September 2006 07:58 Go to next message
dstinsman
Messages: 3
Registered: September 2006
Junior Member
I have a SQL script that uses UTL_FILE to extract data and put in a text file in XML format. Works great, but I need a way to make it put lines 1-5000 into .txt1, lines 5001-10000 into .txt2 and so on.

Here is a snippet of the code that actually produces the text file...
    l_file_hdl utl_file.file_type;

    procedure prt (str_in varchar2) is
      begin
          utl_file.put_line(l_file_hdl,str_in);
      end;

    procedure fmt (label_in varchar2,content varchar2) is
      begin
          prt('    <'||label_in||'>'||content||'</'||label_in||'>');
      end;,/code>

BEGIN
    l_file_hdl:= utl_file.fopen('/apps/heatstg/htcs/data','ccs45.txt','W',32767);
    prt('<?xml version="1.0" encoding="UTF-8"?>');
Re: UTL_FILE extract to multiple files [message #191877 is a reply to message #191875] Fri, 08 September 2006 08:04 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Sounds like You need a loop, a counter and then close + open a new file?
Re: UTL_FILE extract to multiple files [message #191905 is a reply to message #191875] Fri, 08 September 2006 09:35 Go to previous messageGo to next message
dstinsman
Messages: 3
Registered: September 2006
Junior Member
Problem is, I don't know how to do that. I already have a loop to produce the xml tags for each record. I'm thinking that I need:

A procedure to count the number of records
A procedure to increment the text file (file1.txt, file2.txt...)
A new loop (existing one nested inside new one) to call those procedures
 PROCEDURE prt (str_in varchar2) is
      begin
          utl_file.put_line(l_file_hdl,str_in);
    END;

    PROCEDURE fmt (label_in varchar2,content varchar2) is
      begin
          prt('    <'||label_in||'>'||content||'</'||label_in||'>');
    END;


BEGIN
    l_file_hdl:= utl_file.fopen('/apps/heatstg/htcs/data','ccs45.txt','W',32767);
    prt('<?xml version="1.0" encoding="UTF-8"?>');
    prt('<merchant_import xsi:noNamespaceSchemaLocation="http://xml.test.com/import_v1.2.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-

instance">');
    for t in (select * from tbl1 
               where fld1 = 'sample') 
loop

 prt('<merchant>');
    fmt(	'action',action);
 prt('</merchant>');
end loop;
prt( '</merchant_import>');
utl_file.fclose(l_file_hdl);
end;



Does this sound accurate?
Re: UTL_FILE extract to multiple files [message #191919 is a reply to message #191875] Fri, 08 September 2006 11:24 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Try this...


create directory 'dir_path' as '/apps/heatstg/htcs/data'


declare
      v_blob                    BLOB;
      blob_length               INTEGER;
      out_file                  UTL_FILE.file_type;
      v_buffer                  RAW (5000);
      chunk_size                BINARY_INTEGER     := 5000;
      blob_position             INTEGER            := 1;
      directory_name   CONSTANT VARCHAR2 (80)      := 'DIR_PATH';
      i                         number:= 0;
begin
      SELECT physical_file_blob
        INTO v_blob
        FROM file_load_temp_inlt
       WHERE infl_load_log_id = 24;
	   
      --Retrieve the sixe of the BLOB
      blob_length := DBMS_LOB.getlength (v_blob);
      --open a handle to the location where you are going to write the BLOB to file
      
      -- write the BLOB to file in chunks
      WHILE blob_position <= blob_length  
      LOOP
         i := i+1;
		 out_file := UTL_FILE.fopen (directory_name, 'orafaq'||i||'.txt', 'w', chunk_size);
		 IF blob_position + chunk_size - 1 > blob_length
         THEN
            chunk_size := blob_length - blob_position + 1;
         END IF;

         DBMS_LOB.READ (v_blob, chunk_size, blob_position, v_buffer);
         UTL_FILE.put_raw (out_file, v_buffer, TRUE);
         blob_position := blob_position + chunk_size;
	
	--close the file handle 
         UTL_FILE.fclose (out_file);
		 
      END LOOP;
end;



Naveen

[Updated on: Fri, 08 September 2006 12:40]

Report message to a moderator

Re: UTL_FILE extract to multiple files [message #191938 is a reply to message #191919] Fri, 08 September 2006 15:01 Go to previous messageGo to next message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
That demonstrated the loop + counter.

All You need is to do test for when You reach each 5000 lines.
- and then close the file + open a new one....

Re: UTL_FILE extract to multiple files [message #192410 is a reply to message #191875] Tue, 12 September 2006 04:15 Go to previous messageGo to next message
quytc
Messages: 81
Registered: November 2005
Location: Viet Nam
Member

.
Re: UTL_FILE extract to multiple files [message #192476 is a reply to message #191875] Tue, 12 September 2006 09:43 Go to previous messageGo to next message
dstinsman
Messages: 3
Registered: September 2006
Junior Member
Naveen, that's a little over my head.

Kimant, I understand what you're saying, but I don't know how to do it. Here's what I have so far:

 row_cnt number;
   max_row number;
   file_cnt number;
   l_file_hdl utl_file.file_type;

procedure content is
BEGIN

   for t in (select * from tbl)
loop
   row_cnt:=row_cnt+1;
   <data>
end loop;

end;

BEGIN
   row_cnt :=0;
   max_row :=1000;
   file_cnt :=1;

   l_file_hdl:= utl_file.fopen('/apps/data','ccs45_'||file_cnt||'.txt','W',32767);
   prt('<?xml version="1.0" encoding="UTF-8"?>');
   prt('<merchant_import xsi:noNamespaceSchemaLocation="http://xml.sample.com/import_v1.2.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">');
   content;
   prt( '</merchant_import>');

utl_file.fclose(l_file_hdl);
end;
Re: UTL_FILE extract to multiple files [message #193441 is a reply to message #192476] Sat, 16 September 2006 15:27 Go to previous message
kimant
Messages: 201
Registered: August 2006
Location: Denmark
Senior Member
Check out Naveens suggestion.
It is actually quite nice, and does what I outlined!

Br
Kim
Previous Topic: Parallel Hint - nodes usage
Next Topic: question about "set termout off" and "java stored procedure "
Goto Forum:
  


Current Time: Tue Dec 06 04:56:35 CST 2016

Total time taken to generate the page: 0.13556 seconds