Generate a CSV file from ORACLE Database

The below procedure will help you to create a CSV (comma separated value) from database only passing the below parameters:

P_QUERY - This parameter will be your required SQL query

P_DIR - This parameter is the database directory, where the CSV file will be generated. You can move the file into client directory by programmatically.

P_FILENAME - This parameter is used to the generate CSV file with a specific filename. So that user can identify the required file.  

CREATE OR REPLACE Procedure Get_Csv( p_query 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(2000);

l_status integer;

l_colCnt number default 0;

l_separator char(1);

l_cnt number default 0;

Begin

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

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

    For i in 1 .. 255 Loop

        Begin

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

            l_colCnt := i;

            Exception

            when others then

                if ( sqlcode = -1007 ) then exit;

                  else

                  raise;

                end if;

        End;

    End Loop;

dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

l_status := dbms_sql.execute(l_theCursor);

  Loop

  Exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );

    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_cnt := l_cnt+1;

  End loop;

dbms_sql.close_cursor(l_theCursor);

utl_file.fclose( l_output );

End Get_Csv;

/

Compile the above procedure & execute as below:

Begin

  Get_Csv(p_query    => 'Write a SQL QUERY',

          p_dir      => 'Write a database directory name',

          p_filename => 'FileName');

End;