Re: Spooling without the whitespace

From: Steve M <steve.mcdaniels_at_vuinteractive.com>
Date: Mon, 26 Aug 2002 14:04:44 -0700
Message-ID: <ake55b$9pf$1@spiney.sierra.com>

try this. (credit to original author....this is not my handiwork)

create or replace function dump_csv

  (p_query varchar2,
   p_separator varchar2 default ',',
   p_dir varchar2,
   p_filename varchar2)               return number is
--
-- example usage:
--
-- SQL> variable number_of_lines number;

-- SQL> begin
-- :number_of_lines :=

        dump_csv('select * from my_table', ',', '/fs1/data1', 'my_table.txt');

-- end;
-- SQL> /
--
  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 varchar2(10) default '';
  l_cnt number default 0;
begin
  l_output := utl_file.fopen( p_dir, p_filename, '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 := p_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);
  return l_cnt;
end dump_csv;

"BVince2172" <bvince2172_at_aol.comPRO> wrote in message news:20020825085616.29713.00002982_at_mb-mo.aol.com...
> I'm trying to spool to a csv file, which I have managed to do, but I can't
work
> out how to remove the extra white space in the columns. ie, my output
looks
> like the following:
>
> 101,12-Dec-99, 190101,01-Jan-00, 29292,'monkey' , 90
>
> Should I be SETting something or setting something in the COLUMN
definition, or
> something else altogether??
>
> Thanks,
> Ben
>
Received on Mon Aug 26 2002 - 16:04:44 CDT