Myujth

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.