Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: novice: extract comma delimited data
Try setting the parameter 'utl_file_dir' and write a stored procedure that calls
the built-in package 'dbms_output.put_line'. This will write a file to the
directory noted by the utl_file_dir. This way for big jobs the server does all
the work not SQLPlus and as far as I know but dont quote me there will be no
length limit.
Cheers
Gary Perkins
J. G. Dutcher wrote:
> These are good approaches, but what do you do when the results are wider
> than sqlplus or sql allows? I need to download a file about 1200
> characters per line.
> Any help is appreciated.
>
> TIA
>
> In article <SO9V2.5499$%L2.240143_at_news6.ispnews.com>, "Chris Nelson"
> <wrhs71_at_hotmail.com> wrote:
>
> > [posted and emailed]
> >
> > Hi, Furkan.
> >
> > I do this all the time, and I like SQL much better than the gui. That
> > export utility is not all that trustworthy sometimes.
> >
> > Write this up as a file, like EXPORT.SQL, for instance:
> >
> > SPOOL Drive:\Path\Filename.TXT
> >
> > SELECT a.column_1 || ', ' || a.column_2 || ', ' || b.column_3 || ', '
> > FROM table_1 a
> > , table_2 b
> > WHERE conditions...
> > ORDER BY column_1...
> > /
> >
> > SPOOL OFF
> >
> > I use the final (extra) comma to delimit the last column, because I find
> > that Excel or Oracle adds extra spaces to the last value if I don't add it.
> > There might be a neater way, like RTRIM( column_3) or something, but this
> > works fine for me.
> >
> > Chris
> >
> >
> > Furkan Khan wrote in message <3724F703.B40C1E9B_at_home.com>...
> > >Folks, I am a novice at SQL, its been quite some time since I used
> > >it. Can someone recommend me the best way to dump out data
> > >from an oracle table into comma delimited form.
> > >
> > >I know the the Oracle Navigator allows it via the gui, but I want
> > >a sql script or some other scripting utility.
> > >
> > >Thanks in advance. You can mail me directly also.
> > >
> > >Regards.
> > >Furkan Khan
> > >
>
> TIA
Received on Fri Apr 30 1999 - 08:47:21 CDT