1

I have a procedure which creates a file with as many records returned from a cursor as possible. Due to downstream file size issues, I want to limit each file to ~500k(n) records (value to derive from a parameter table). If there are more than 500k(n) records in the cursor, a new file with a newer timestamp should be created. The procedure uses the following steps.

set v_date = to_char(SYSDATE, 'YYYYMMDDHH24MISS');
set new_file_name = 'new_file_'|| v_date
  open record cursor
  loop
    fetch bulk collect with 1000 record limit
    for loop
      write records to file
    end loop
    clear cache
  close cursor loop

Before down-voting, please leave a comment if you need more information. I am looking for the easiest solution as well as the most efficient solution.

APC
  • 144,005
  • 19
  • 170
  • 281
user3224907
  • 758
  • 2
  • 15
  • 40
  • You can use if else in your loop with count variable which increases with each iteration and if it goes beyond the limit, create new file in if else block and reset the count variable – Popeye Dec 19 '19 at 03:23

1 Answers1

3

You need three loops. The outer loop manages each file, the middle loop fetches the records, the inner loop writes the records. So, adapting your pseudo-code....

open record cursor    
loop

    fetch bulk collect with 1000 record limit
    exit when 0

    record_count := 0;
    utl_file.fopen(dir, 'new_file_'||to_char(SYSDATE, 'YYYYMMDDHH24MISS'), 'W');

    loop

        for loop
             write records to file
        end loop

        record_count := record_count + 1000;
        if record_count = 50000 then exit;

        fetch bulk collect with 1000 record limit
        exit when 0

    end loop

    close file

end loop;
close record cursor;
APC
  • 144,005
  • 19
  • 170
  • 281