CREATE OR REPLACE PROCEDURE dump_tbl2csv

  ( p_tname    IN VARCHAR2,

    p_dir      IN VARCHAR2,

    p_filename IN VARCHAR2 )

IS

    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;

BEGIN

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

    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

      EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format=''dd-MON-yy'' ';

      RAISE;

END;

/

--Sample calling format:

rob@XE11g2> EXEC dump_tbl2csv( 'employees', 'D:\R_workdir', 'employees.csv' );