Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: creating output file from sqlplus
Malcolm Dew-Jones wrote:
> DH (my_generic_email_at_att.net) wrote:
> : I know how to use the spool command in sql*plus to save the results to a
> : file. But myquery is returning about 63,000 user IDs. I need these in a
> : text output file that can be imported into a utility to correct a problem
> : we're having. Using the spool command includes line numbers and headers
> : that would have to be stripped out manually. Is there a way to create a
> : file with simply the user IDs?
>
> : (Hope this is the right group to post this - I couldn't find one for
> : sql*plus)
>
>
> read the sql*plus manual (google if necessary).
>
> Look up the SET command.
>
> Various settings can help, I don't know them all by memory, but something
> like this.
>
> SET PAGESIZE 0
> SET HEAD OFF
> SET TRIMSPOOL ON
> SET LINESIZE 1000
>
> Also, it isn't true that line numbers and headers that would have to be
> stripped out manually. Tools like perl, awk, grep, and col, can easily do
> that sort of thing. If you are dealing with about 63,000 user IDs then it
> would pay to learn those sorts of tools.
>
> On windows then perl is the easiest to get and use, or even VB in
> conjunction with excel or word. Load the spool file into excel or word,
> and write a VB macro to scan each line and keep the item you want.
>
But of course, if you were using Perl or Access VB, you wouldn't be messing around with post-processing a SQL*Plus spooled output file... :-)
-Mark Bole Received on Sat Sep 03 2005 - 21:08:13 CDT