Re: SQL EXPORT STATEMENT

From: Steve M <steve.mcdaniels_at_vuinteractive.com>
Date: Mon, 11 Nov 2002 15:13:54 -0800
Message-ID: <aqpdjb$5il$1_at_spiney.sierra.com>

This is what I use. (It is not my original work. Thanks to originator)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;"Andre Boeder" <me_at_aboeder.com> wrote in message news:c2771de5.0211051015.3e5b24ca_at_posting.google.com...
> hello!
>
> i need to export a result of a view into a csv file. is there a way to
> do it with a sql statement ?
>
> thx
>
> andr
 -- Received on Tue Nov 12 2002 - 00:13:54 CET