Wsrtjtyk

I am using the procedure below as mentioned in this to dump data into csv.
This writes data into a single csv file test.csv. In case table is very big, I don't want to write into one csv file. I want to specify a threshold of 10k into it with a file name as

test_sequenceNumber _timeOfOpeningInYYYYMMDDHHMMSSsss.csv

so for example if row count of a table is 15k then I should have 2 files:

test_1_20181112012830000.csv 

test_2_20181112012835005.csv

How can we I achieve this by modifying the below SP?

create or replace procedure dump_table_to_csv

    ( p_tname in varchar2

    , p_dir in varchar2

    )

as

    l_output utl_file.file_type;

    l_theCursor integer default dbms_sql.open_cursor;

    l_columnValue varchar2(4000);

    l_status integer;

    l_query varchar2(1000)

        default 'select * from ' || p_tname;

    l_colCnt number := 0;

    l_separator varchar2(1);

    l_descTbl dbms_sql.desc_tab;

    p_filename varchar2(100) := 'test.csv' ;

begin

    l_output := utl_file.fopen( p_dir, p_filename, 'w' , 32760); 

    execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';



    dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );

    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );



    for i in 1 .. l_colCnt loop

        utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );

        dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );

        l_separator := ',';

    end loop;



    utl_file.new_line( l_output );



    l_status := dbms_sql.execute(l_theCursor);



    while (dbms_sql.fetch_rows(l_theCursor) > 0 ) loop

        l_separator := '';



        for i in 1 .. l_colCnt loop

            dbms_sql.column_value( l_theCursor, i, l_columnValue );

            utl_file.put( l_output, l_separator || l_columnValue );

            l_separator := ',';

        end loop;



        utl_file.new_line(l_output );

    end loop;



    dbms_sql.close_cursor(l_theCursor);

    utl_file.fclose(l_output );



    execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';

exception

    when others then

        utl_file.fclose( l_output );

        execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';

        raise;

end;

EDIT1:
I was trying of applying a logic of mod(num_rows_written,10000) == 0 it's time to close old file and start a new one but just stuck at a point that, what should be top most loop to continue the same operation on new file ?

Well, it's a fairly straightforward task. All you need to do is close the old file and open a new one. There's no need for outer loop, all you need is to put a condition and retrace the steps for preparing the csv.

create or replace procedure dump_table_to_csv

    ( p_tname in varchar2

    , p_dir in varchar2

    )

as

    l_output utl_file.file_type;

    l_theCursor integer default dbms_sql.open_cursor;

    l_columnValue varchar2(4000);

    l_status integer;

    l_query varchar2(1000)

        default 'select * from ' || p_tname;

    l_colCnt number := 0;

    l_separator varchar2(1);

    l_descTbl dbms_sql.desc_tab;

    l_file_seq number := 1;

    l_rows_written number := 0;

    l_nls_format varchar2(200);

begin

    l_output := utl_file.fopen( p_dir, 'test_'||l_file_seq||'_'||to_char(systimestamp, 'YYYYMMDDHH24MISSFF3'), 'w' , 32760); 

    l_file_seq := l_file_seq + 1;

    SELECT value into l_nls_format

      FROM   nls_session_parameters

     WHERE  parameter = 'NLS_DATE_FORMAT';

    execute immediate 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';



    dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );

    dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );



    for i in 1 .. l_colCnt loop

        utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );

        dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );

        l_separator := ',';

    end loop;



    utl_file.new_line( l_output );



    l_status := dbms_sql.execute(l_theCursor);



    while (dbms_sql.fetch_rows(l_theCursor) > 0 ) loop



        if l_rows_written >= 10000 then

          utl_file.fclose( l_output );

          l_output := utl_file.fopen( p_dir, 'test_'||l_file_seq||'_'||to_char(systimestamp, 'YYYYMMDDHH24MISSFF3'), 'w' , 32760);

          for i in 1 .. l_colCnt loop

              utl_file.put( l_output, l_separator || '"' || l_descTbl(i).col_name || '"' );

              l_separator := ',';

          end loop;

          utl_file.new_line( l_output );



          l_file_seq := l_file_seq + 1;

          l_rows_written := 0;

        end if;



        l_separator := '';



        for i in 1 .. l_colCnt loop

            dbms_sql.column_value( l_theCursor, i, l_columnValue );

            utl_file.put( l_output, l_separator || l_columnValue );

            l_separator := ',';

        end loop;



        utl_file.new_line(l_output );

        l_rows_written := l_rows_written + 1;

    end loop;



    dbms_sql.close_cursor(l_theCursor);

    utl_file.fclose(l_output );



    execute immediate 'alter session set nls_date_format='''||l_nls_format||''' ';

exception

    when others then

        utl_file.fclose( l_output );

        execute immediate 'alter session set nls_date_format='''||l_nls_format||''' ';

        raise;

end;

It might be a good idea to offload the code for preparing CSV to a separate (sub)procedure to avoid code duplication. Also beware that original procedure has a nasty side effect of overriding your nls_date_format to dd-MON-yy without any regards to how it was set before execution; I fixed that.