Re: CSV File Headings....
- From: Stan Milam <stmilam@xxxxxxxxxx>
- Date: Wed, 21 Jun 2006 03:22:42 GMT
PAUL MADDSION wrote:
Try using a place holder, and in the before report trigger create the heading something like thisForget reports, use plain ole PL/SQL!
: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@xxxxxxxxxxx> wrote in message news:1144664458.487042.31580@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
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.
=============================================================
.
- Prev by Date: Re: I can't find a SETUP.EXE in the SQL Plus Client ??
- Next by Date: Re: I can't find a SETUP.EXE in the SQL Plus Client ??
- Previous by thread: utl_file
- Next by thread: Q: Designer, forms testing, automation
- Index(es):
Relevant Pages
|
