Re: Exporting to a .csv file

If you have Oracle 8 Personal Edition, you can use the Oracle 8 Navigator to export out individual table in CSV format.

<milam_at_nospam.com> wrote in message news:fnEA6.1456$%L5.24508_at_insync...
> : Hello everyone,
 

> : Can anyone tell me how to Export my database to a comma separated value
> : file. I have done some reading on the net about this but everywhere I
 go
> : they say you need to spool from within SQL*Plus with a custom script,
 but
> : there are no examples of this custom script. Can anyone help me?
 

> : Thanks,
> : Jeff
>
> Here is a PL/SQL function found on the net and modified to be a little
 more
> sophisticated. It uses the UTL_FILE package, so you must look at that to
> see what directories you can write into. We use this function quite a lot
> where I work. Enjoy - Stan Milam.
>
>
> create or replace function
> write_sql_to_csv( p_query in varchar2,
> p_dir in varchar2,
> p_filename in varchar2,
> p_separator in varchar2 default ',',
> p_col_flag in boolean default true )
>
> /**FILE****************************************************************/
> /* File Id: writecsv.sql. */
> /* Author: Stan Milam. */
> /* Date Written: 31-Jan-2001. */
> /* Description: */
> /* Function to write the columns of a query to a comma seperated */
> /* file. */
> /* */
> /****************************************************************FILE**/
>
> /**********************************************************************/
> /* Name: */
> /* write_sql_to_csv(). */
> /* */
> /* Description: */
> /* Function which uses dynamic SQL and the UTL_FILE package to */
> /* dump the contents of a query to a comma seperated file. */
> /* */
> /* Arguments: */
> /* p_query varchar2 - A string containing the query to be */
> /* executed. */
> /* p_dir varchar2 - The directory where the file will be */
> /* created. */
> /* p_filename varchar2 - The filename used to store the file. */
> /* p_seperator varchar2 - The character to seperate the fields. */
> /* a comma ',' is used by default. */
> /* p_col_flag boolean - Default is true in which case column */
> /* headers will be created in the file. */
> /* */
> /* Return Value: */
> /* The number of rows written to the CSV file. */
> /* */
> /**********************************************************************/
>
> return number
> is
> l_status integer;
> l_col_count number default 0;
> l_row_count number default 0;
> l_output utl_file.file_type;
> l_separator varchar2(10) default '';
> l_wrkbuf varchar2(4000) default '';
> l_cursor integer default dbms_sql.open_cursor;
>
> /******************************************************************/
> /* Special types in the DBMS_SQL package for column information. */
> /******************************************************************/
>
> l_columns dbms_sql.desc_tab;
> l_column_rec dbms_sql.desc_rec;
> begin
>
> /******************************************************************/
> /* Open the output file and parse the sql statement. */
> /******************************************************************/
>
> l_output := utl_file.fopen( p_dir, p_filename, 'w' );
> dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
>
> /******************************************************************/
> /* Call DBMS_SQL.DESCRIBE_COLUMNS to get the column count of the */
> /* query, and the column names if we need them later on. */
> /******************************************************************/
>
> dbms_sql.describe_columns( l_cursor, l_col_count, l_columns );
>
> /******************************************************************/
> /* Get the column names to use for headers. */
> /******************************************************************/
>
> if p_col_flag = true
> then
>
> /**************************************************************/
> /* For each column we must process the following. */
> /**************************************************************/
>
> for i_sub in 1 .. l_col_count
> loop
>
> /**********************************************************/
> /* Get the column record and add column name to the */
> /* string */
> /**********************************************************/
>
> l_column_rec := l_columns( i_sub );
> l_wrkbuf := l_wrkbuf || l_separator || l_column_rec.col_name;
> l_separator := p_separator;
>
> end loop;
>
> /**************************************************************/
> /* Write out the column headers and adjust the row count. */
> /**************************************************************/
>
> l_row_count := 1;
> utl_file.put_line( l_output, l_wrkbuf );
>
> /**************************************************************/
> /* Reset these variables. */
> /**************************************************************/
>
> l_wrkbuf := '';
> l_separator := '';
>
> end if;
>
> /******************************************************************/
> /* Define all the columns as varchar with a buffer size of 4000 */
> /******************************************************************/
>
> for x_sub in 1 .. l_col_count
> loop
> begin
> dbms_sql.define_column( l_cursor, x_sub, l_wrkbuf, 4000 );
> exception
> when others then
> if ( sqlcode = -1007 )
> then
> exit;
> else
> raise;
> end if;
> end;
> end loop;
>
> /******************************************************************/
> /* Execute the cursor. */
> /******************************************************************/
>
> l_status := dbms_sql.execute( l_cursor );
>
> /******************************************************************/
> /* Loop through the rows and exit when there are no more. */
> /******************************************************************/
>
> while dbms_sql.fetch_rows( l_cursor ) > 0
> loop
>
> /**************************************************************/
> /* For each column in the row, get its value and write it */
> /* to the file. */
> /**************************************************************/
>
> for i_sub in 1 .. l_col_count
> loop
> dbms_sql.column_value( l_cursor, i_sub, l_wrkbuf );
>
> /**********************************************************/
> /* If the column value includes the separator value we */
> /* need to put quote marks around the value. */
> /**********************************************************/
>
> if instr( l_wrkbuf, p_separator ) > 0
> then
> l_wrkbuf := '"' || l_wrkbuf || '"';
> end if;
>
> utl_file.put( l_output, l_separator || l_wrkbuf );
> l_separator := p_separator;
> end loop;
>
> /**************************************************************/
> /* Output a new line in preparation of a new row. Also */
> /* increment the count of rows we have processed. */
> /**************************************************************/
>
> l_separator := '';
> utl_file.new_line( l_output );
> l_row_count := l_row_count + 1;
>
> end loop;
>
> /******************************************************************/
> /* Close the file and cursor and return the count of rows. */
> /******************************************************************/
>
> utl_file.fclose( l_output );
> dbms_sql.close_cursor(l_cursor);
> return l_row_count;
>
> exception
> when others then
>
> dbms_output.put_line( 'WRITECSV SQLERRM: ' || sqlerrm );
> dbms_output.put_line( 'WRITECSV SQL: ' || p_query );
> dbms_output.put_line( 'WRITECSV Dir: ' || p_dir );
> dbms_output.put_line( 'WRITECSV File: ' || p_filename );
>
> /**************************************************************/
> /* Make sure the file is closed. */
> /**************************************************************/
>
> if utl_file.is_open( l_output )
> then
> utl_file.fclose( l_output );
> end if;
>
> /**************************************************************/
> /* Make sure the cursor is closed. */
> /**************************************************************/
>
> if dbms_sql.is_open( l_cursor )
> then
> dbms_sql.close_cursor( l_cursor );
> end if;
>
> /**************************************************************/
> /* Return -1 to indicate an error. */
> /**************************************************************/
>
> return -1; -- Return an error code.
>
> end write_sql_to_csv;
> /
>
>
> --
> Reply To: milam "at" metronet "dot" com
> =============================================================
> Charter Member of The Society for Mediocre Guitar Playing on
> Expensive Instruments, Ltd.
> =============================================================
Received on Fri Apr 20 2001 - 15:55:44 CEST