Spooling SQL via sqlplus to flat file for Mainframe

[stek]

Driving me bonkers!

Needs to be in format for Mainframe, fixed length fields, no spaces between, no newlines, no carriage returns, flat file, one long line.

Tried all the spool set commands under the sun, still get spaces and end of line, and don't want to resort to sed'ing or perl to sort it - surely sqlplus spooling can do it?

[mudskipper]

One line per record, or one line for everything?

Lots of SQLPlus options here sqlplus set command | SS64.com

Have you tried recsep and recsepchar ? It's not clear whether this suppresses the normal linebreak or is in addition.

Edit: SET RECSEP OFF might do the trick?

[Spacecadet]

Driving me bonkers!

Needs to be in format for Mainframe, fixed length fields, no spaces between, no newlines, no carriage returns, flat file, one long line.

Tried all the spool set commands under the sun, still get spaces and end of line, and don't want to resort to sed'ing or perl to sort it - surely sqlplus spooling can do it?

do it in the source SQL query

select
rpad(col1,length ' ') + rpad(col2, length,' ')

you can strip out any naughty characters at the same time

[stek]

Cheers fellas - it one one line with each record one the end of the previous, in vi Shift-A takes you to the end of the line and the file at the same time - wow!

I'll experiment later, got Tuxedo UBBCONFIG to deal with now. Gripping...

I got the output formatting from the Mainframe guy, he want's it to be exactly 372 bytes long! How's that gonna happen? He'll have to chunk it and/or pad it...

[doodab]

I got the output formatting from the Mainframe guy, he want's it to be exactly 372 bytes long! How's that gonna happen? He'll have to chunk it and/or pad it...

set linesize 372?

[stek]

set linesize 372?

That will just set the line width doods, the output is the output - it's a size and if it's less than 372 yeah can be padded out. But it depends on a lot such as block size of the underlying AIX fs the report resides before it ends up on Mainframe, it's just not possible.

I've been told mainframe JCL etc requires jobs to have a certain size, hence the issue...