Re: Exporting to a .csv file
From: <milam_at_nospam.com>
Date: Tue, 10 Apr 2001 14:01:47 GMT
Message-ID: <fnEA6.1456$%L5.24508_at_insync>
l_column_rec dbms_sql.desc_rec;
begin
end loop;
Date: Tue, 10 Apr 2001 14:01:47 GMT
Message-ID: <fnEA6.1456$%L5.24508_at_insync>
[Quoted] : 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 Tue Apr 10 2001 - 16:01:47 CEST