Re: SQL*PLUS spooling and CR/LF

abrusko wrote:

> Ken,
>
> I am creating this comma-delimited file to send to an off-
> site service that has told me they require a CR/LF at the
> end of each record.
>
> I don't know what is requiring them to do this, but they
> are requesting the data this way.
>
> Unfortunately I am restricted by the tools that I have
> available and by various other department standards.
>
> I have SQL*PLUS to do my SQL statements, which I am using
> to produce the comma-delimited file that I will send off-
> site. This will be a very simple and effective means of
> producing this file if I can just assure that there is a
> CR/LF at the end of each record. My record will be of
> fixed length (416 characters long) and is I am using 416 on
> the "Set linesize" statement. Shouldn't I be able to view
> this file and shouldn't I be able to "detect" the CR/LF by
> putting the cursor at the end of the line an hitting the
> "arrow" key and being "bounced" down to the next line?
>
> So I'm back to the original question of: How can I ensure
> that there is a CR/LF at the end of each physical line in
> my comma-delimited file?
>
> P.S. Thanks alot for your reply and info!
>
> * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
> The fastest and easiest way to search and participate in Usenet - Free!

I didn't read as thoroughly as I should have before my last response.

If you are going for a fixed record length, then just set that with "SET LINESIZE". However, this doesn't tend to make much sense if you are writing comma-delimted files.

As I pointed out, the records already have a cr/lf sequence. However, since your physical record length will probably be greater than your logical record length then each logical record will be followed by enough blanks (spaces) to pad it out to the physical record length.

As for bringing up the file in a text editor, that is not a good test of anything since a text editor does not handle files on a binary basis, so you really can't detect much of anything that way.

One question you do need to think about is whether the 416 characters for the physical record length includes the carriage return and the line feed. If so, then you need to use "SET LINESIZE 414" and that will then generate physical records that contain exactly 416 bytes.

Is the machine at the other end a mainframe? If so, can you find out how what they are doing with the file once it arrives? Do they perform an IEBGENER? If so, what is the DCB information that they set up. Are they using IDCAMS? If so, how do they have that set up.

You see, the problem with all of this is that you have to solve the problem from the perspective of all of the elements that are involved. There is no real "generic" solution because there are so many factors. I don't know how many times I've dealt with situations like this in my career but there have been few cases where I can just mindlessly apply a solution that solves the problem in one instance to another instance that may have a different transmission method, a different type of machine at the other end, etc. etc.

Ken Received on Tue Aug 24 1999 - 12:05:26 CDT