Generate Multiple XML files from SQL script

[cwsstins]

I've got a SQL script that uses UTL_FILE to generate a text file in XML format. Script works great, thanks to some help from Santa Mufasa (

http://www.tek-tips.com/viewthread.cfm?qid=1272379&page=1).

Recently discovered that, due to external system limitations, a single text file cannot have more than ~20k records. My script will sometimes create a file containing more than 20k records.

Is there a way to put in logic to create .txt1 and then at record number 20,001, start putting the data into .txt2 and so on?

 

[cwsstins]

I've made a little progress on this, but I'm wondering if anyone can provide insight on what else I need. I've put in components (in bold) to count the number of records and to increment the filename.

However, I think I need another loop to indicate that the filename should increment when the record count gets to x. Any assistance greatly appreciated:

Declare
    action VARCHAR2(1);
[b]     cnt number;
    incr number;
    file_incr varchar2(2);[/b]
    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;
BEGIN
[b]     cnt :=0;
    incr :=0;
    file_incr :=incr+1;[/b]
    l_file_hdl:= utl_file.fopen('/apps/heatstg/htcs/data','ccs45_'[b]||file_incr||[/b]'.txt','W',32767);
    prt('<?xml version="1.0" encoding="UTF-8"?>');
    prt('<merchant_import xsi:noNamespaceSchemaLocation="[URL unfurl="true"]http://xml.test.com/import_v1.2.xsd"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance">');[/URL]
    for t in (select statement) 
loop
    [b]cnt:=cnt+1;[/b]

 

[cwsstins]

Right now, this will loop through, incrementing the filename but it's writing all the data to each file and the loop doesn't exit.

I know I need a test where row_cnt > max_row, but where do I put it? And how do I present the exit?

BEGIN

    file_cnt :=0;
    row_cnt :=0;
    max_row :=500;

   LOOP

    file_cnt :=file_cnt+1;

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

instance">');
    content;
    prt( '</merchant_import>');
    utl_file.fclose(l_file_hdl);


END LOOP;