Folks
In this following script, data is extracted from Oracle Database in .csv file using pipeline functions. Currently, there is a single file getting created for P_SOURCE which is being passed as a parameter. Could anyone please advise on how can I break this script in such a way that for example if table ABC containing 1000 rows, is being extracted which is passed through P_SOURCE then instead of single file containing 1000 rows, there are lets say 5 files getting created 200 rows each.
PROCEDURE legacy_unload(
p_source IN SYS_REFCURSOR,
p_filename IN VARCHAR2,
p_directory IN VARCHAR2,
p_limit_size IN PLS_INTEGER DEFAULT unload_pkg.c_default_limit
) IS
TYPE row_aat IS TABLE OF VARCHAR2(32767)
INDEX BY PLS_INTEGER;
aa_rows row_aat;
v_name VARCHAR2(128) := p_filename || '.txt';
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN( p_directory, v_name, 'w', c_maxline );
LOOP
FETCH p_source BULK COLLECT INTO aa_rows LIMIT p_limit_size;
EXIT WHEN aa_rows.COUNT = 0;
FOR i IN 1 .. aa_rows.COUNT LOOP
UTL_FILE.PUT_LINE(v_file, aa_rows(i));
END LOOP;
END LOOP;
CLOSE p_source;
UTL_FILE.FCLOSE(v_file);
END legacy_unload;
Thanks