Re: SQL*Plus question. Trimming column lengths.

Ganesh Raja wrote:
>
> You Can Use this procedure to do that ....
>
> Coutesy http://asktom.oracle.com
>
> create or replace function dump_csv( p_query in varchar2,
> p_separator in varchar2
> default ',',
> p_dir in varchar2 ,
> p_filename in varchar2 )
> return number
> is
> 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;
> /
>
> HTH
> On Tue, 12 Feb 2002 16:25:37 +1100, "Ray Teale" <ray_at_BLAHholly.com.au> wrote:
>
> >Hi
> >
> >SQL*Plus 8.1.7
> >
> >I'm creating a comma delimited report :
> >
> >set linesize 100
> >set pages 0
> >
> >select value1||','||value2||','||value3
> >from example_table
> >/
> >
> >... does anybody know how I can prevent SQL*Plus from padding my records
> >with spaces and always having the carriage return at position 100.
> >I'd like the records to be variable length if poss.
> >
> >Thanks
> >
> >Ray Teale
> >
> >
>
> [Additions and Corrections Always Welcome.]
> Best Regards,
> Ganesh R

Or maybe just make teh line much longer and use: set trimspool on

Geoff
University of Brighton Received on Tue Feb 12 2002 - 10:20:35 CST