Re: CSV File Headings....
From: Stan Milam <stmilam_at_swbell.net>
Date: Wed, 21 Jun 2006 03:22:42 GMT
Message-ID: <6Y2mg.26336$VE1.5728_at_newssvr14.news.prodigy.com>
>>Chaz n Dave,
>>
>>Am using reports to generate a CSV file. This is working fine but the
>>users want column headings too.
>>
>>Am trying to output headings but they just get prefixed to the
>>beginning of each row output.
>>
>>Any ideas on how i can achieve this.
>>
>>TIA
>>
Forget reports, use plain ole PL/SQL!
begin
Date: Wed, 21 Jun 2006 03:22:42 GMT
Message-ID: <6Y2mg.26336$VE1.5728_at_newssvr14.news.prodigy.com>
PAUL MADDSION wrote:
> Try using a place holder, and in the before report trigger create the > heading something like this > :rep_header := 'column A, column B, column C'; > Then in the report layout create a field that references the place holder > > It's a while since I did this but I hope it points you in the right > direction. > "Miggins" <mtproc_at_yahoo.co.uk> wrote in message > news:1144664458.487042.31580_at_i39g2000cwa.googlegroups.com... >
>>Chaz n Dave,
>>
>>Am using reports to generate a CSV file. This is working fine but the
>>users want column headings too.
>>
>>Am trying to output headings but they just get prefixed to the
>>beginning of each row output.
>>
>>Any ideas on how i can achieve this.
>>
>>TIA
>>
> > >
Forget reports, use plain ole PL/SQL!
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 || _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;
/
-- Regards, Stan Milam ============================================================= Charter Member of The Society for Mediocre Guitar Playing on Expensive Instruments, Ltd. =============================================================Received on Wed Jun 21 2006 - 05:22:42 CEST