Re: generating a text file of a table

In article <91comf$gtm$1_at_nnrp1.deja.com>,   tshbedford_at_my-deja.com wrote:
> Thanks Jay,
> I've got that working fine as command line entries but I can't get it
> to run as a script. Do you know the URL of an idiot's guide to
> writing and running scripts (I'm a total Oracle novice).
> Ultimately I want to be able to create csv files from a C++
> application.
> I'm surprised that an established product like Oracle hasn't got a
> simpler method of generating text files of data.
>
> Tim
>
> In article <3A3930A3.1AC74F61_at_pixar.com>,
> Jay Weiland <jay_at_pixar.com> wrote:
> >
> >
> > Hey Tim,
> >
> > If you just want text and fixed width will do it for you:
> >
> > SQL> spool c:\temp\table_text.txt
> > SQL> select * from your_table_name;
> > .
> > .
> > .
> > SQL> spool off
> >
> > And you will have a simple text file with the output. If you
 need CSV one solution would be change the select to:
> >
> > SQL> select field1 || ',' || feild2 || ',' || feild3 || ',' ||
 field4
 from your_table_name;
> >
> > Jay!!!
> >
> > tshbedford_at_my-deja.com wrote:
> >
> > > Oops, I thought my first message had failed to up load. Sorry for
 repeating
> > > myself.
> > >
> > > Tim
> > >
> > > In article <91ab3b$h16$1_at_nnrp1.deja.com>,
> > > tshbedford_at_my-deja.com wrote:
> > > > Can anyone tell me how to generate a simple text file (csv) of a
 table's
> > > > contents? thanks
> > > >
> > > > Tim
> > > >

 --HTML crap removed for brevity--
>

Here is something that might help. I had a better script but seem to have overlaid or otherwise misplaced it.

set echo off
rem
rem SQL*Plus script to create comma delimited output file from table rem
rem 20000614 Mark D Powell Automate commonly done task rem
set pagesize 0
set verify off
set feedback off
set linesize 130
accept owner prompt 'Enter table owner => ' accept tblname prompt 'Enter table name => ' spool csv2.sql

select 'select ' from sys.dual;
select decode(column_id,1,column_name,

             '||'',''||'||column_name)
from sys.dba_tab_columns
where table_name = upper('&&tblname')
and owner = upper('&&owner')
order by column_id;
select 'from &&owner..&&tblname;'
from sys.dual;
spool off
undefine owner
undefine tblname

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com
http://www.deja.com/