Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    10

    Unanswered: Loop to write data to sequential files

    I've got a script that extracts data from Oracle db and writes it to a file. I want to loop it so that if the row count exceeds 500, the data will begin writing to a new file. So records 1-500 write to file1.txt, 501-1000 write to file2.txt and so on. Right now, I have it looping, but it writes all the data to each file and the loop does not exit.

    I know I need to test for row_cnt > max_row, but I'm having trouble on figuring out where to put it...and also how to exit the loop.

    Code:
    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="http://xml.sample.com/import_v1.2.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">');
        content;     -- this procedures increments the row_cnt (row_cnt+1)
        prt( '</merchant_import>');
        utl_file.fclose(l_file_hdl);
        row_cnt :=0;
    
    END LOOP;
    
    end;
    Last edited by dstinsman; 09-14-06 at 11:52.

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    don't konw how comparing row_cnt to anything will help, since you reset row_cnt to zero within the loop, but the format would be:

    if row_cnt > max_row then exit; end if;

    but you still need to work on your logic skills to avoid the inifinte loop.
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Jul 2004
    Posts
    10
    I'm open to suggestions...how can I make record # 501 write to file2 if I don't compare row_cnt to max_row?

    I don't want it to exit if if row_cnt > max_row...if if row_cnt > max_row, then the next record should start the next file.

  4. #4
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Ugh, this is ugly.

    file_cnt should be 1 + trunc( file_cnt / 500 - 0.0001 )
    get rid of row_cnt inside the loop (since you said you're already increasing it with content)

    Obviously, it is not exiting since you dont have any exit condition inside the loop. include one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •