A flat ASCII file. That's all I want. Just a flat ASCII file..

Okay, I'm trying to get a flat data file out of Oracle. Just a flat ASCII file, with fields separated by pipes. I want to do this in both sqlplus and sqlrep. Can anyone help me out?

 A coworker went to Oracle's SQL class and they dug up the following options for her; but it's not perfect.

sqlplus -s supadm/a <<! > /dev/null
  set heading off
  set echo off
  set feedback off
  set space 0
  set linesize 500
  spool OUTPUT2
  column data_source fold_after
  select

	field1 	|| '|',
	field2 	|| '|',
	...etc...

I want to strip off extraneous spaces before and after the real field, so bereft of other ideas I run this sed script after the sqlplus:

sed -e 's/ *\|/|/g' -e 's/\| */|/g' OUTPUT2 > OUTPUT

And yet it's still not what I want. It gives me something like this:

field|field|field|field|field|field|field|field|field|field|field|field|field|fi eld| <- carriage return right here
(the line after this line appears to be 500 spaces.)

This sequence is repeated for each record. Various other combinations of those sqlplus options give various combinations of problems that I don't want.

What could I be doing wrong??

My next question might be a little harder; or depressingly easy. I'm trying to make a similar report in sqlrep; a simple (or so I thought) ASCII dump. But I get form feeds (^L's). I cannot get rid of the ^L's. Now, before you say "use sed", this one's not a throw-away report, it's for production... so I can't cheat.

I'm told the way to fix this is by altering the default Oracle printer definitions.

Okay, so I go into the printer definition. I muck with the "dflt" definition in printdef.dat, generate with printdef, and get it to work! Amazing! But, the next report, this one with lots of formatting, doesn't work (in fact it doesn't work real bad-- it generates the first page, nicely formatted, and then an apparently infinite number of blank lines). So I muck with the dflt definition a bit more, and it works again.. but I have broken my first report. I've learned my lesson, TWO printers is a good idea, but it's too late: I cannot get the unformatted report the way it was. I do not know how I got it that way in the first place. That is, I do know; but it no longer works.

Here is the dflt printer def out of the package:

DF|DFLT|Default logical printer:\

:ap:li#66:co#80

2 problems with this:

1. ^L's every 66 lines, and an extra one at the end, and
2. Lines longer than 80 columns are wrapped to two lines.

So I change it to: (note the printer is 180 colums wide now)

DF|DFLT|Default logical printer:\

:ap:li#66:co#180

1. Now there's one ^L at the end (why?),
2. and lines are not wrapped, at least not til 180 columns.

Not quite satisfied, I try: (fs= means blank the printer termination string, and 999 columns).

DF|DFLT|Default logical printer:\

:ap:fs=:li#66:co#999

No change; still a ^L at the end. Okay, time to really take a chance: (ff= means blank the "printed page eject" character)

DF|DFLT|Default logical printer:\

:ap:ff=:fs=:li#66:co#999

I get a bunch of blank lines at the end, filling out the report to the end of a 66-line page.

Boldly, I tempt fate: (1-line printer)

DF|DFLT|Default logical printer:\

:ap:ff=:fs=:li#1:co#999

And now I get a report filled to 70-line pages. The thing about this last one is, I could swear on all the shit I ever shat that this was the one that fixed it; this was the one that gave me all the data I wanted without any extra surprises.

Surely someone out there has come up with the solution to the problems above.. If so, I would be much obliged if you were to share it with me.

Here's something interesting: when I run a sql report, I get a parameter screen... I hit return enough times, it tells me it's "working..." and out pops the report. But, if I hit F5 (which the keys list tells me is Accept), I get the "working..." message, and a BLANK report of 66 lines. This seems to happen only when I hit F5 at the parameter screen. What's going on here?

-- 

                                        .
                            .signature at 100 meters