Creating a flat file record containing x'00' bytes

[CraigJConrad]

I am in need of creating a flat file with a "canned" header in it. So, the first SELECT statement I do is a SELECT from DUAL to construct this record. A large number of the bytes are x'00'. After creating this, it appears that these have been changed by something to be all spaces (x'20'). What is causing this, and what must I do differently?

-- Variable-Length Header Record for MF Cobol:
   SELECT chr(48), chr(0), chr(0), chr(124), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(62),
                chr(0), chr(1), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(1), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(125), chr(0), chr(0), chr(0), chr(0),
                chr(1), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0), chr(0),
                chr(0), chr(10)
   FROM DUAL
   ;

We experience something similar in placing a prefix on each record coming from SELECTS built from real data tables -- x'00' becomes space.

Any help appreciated! ... Craig

 

[hoinz]

Hi Craig,

which tool do you use for creating a flat file?
I guess it is the spool command of sqlplus;
however this is not good at handling nonprintable characters, and it wasn't meant for it.
Maybe someone else will come up with a solution,
but I think you will have to look for other ways to achieve your goal.

If the header is always the same, what about this:
Create a header file once and for all, using whatever tool you may have at hand. Then create sqlplus spool files whithout the header, and concatenate files afterwards.

regards

 

[CraigJConrad]

Hoinz,

Thanks for the response. Yes, I am using piping (I think the issue is same for spooling).

I had concluded that I could solve the header by exactly what you said -- create a "template" and append/concatenate. However, I also need to place a record length (binary) in front of each record. For this, I have no alternative, as most lengths will be small and, therefore, will begin with "00". The length is quite different when this changes to "20".

We tried a test in which we did a SELECT chr(00), chr(01), .. chr(255) FROM DUAL, piped this to a file and looked at the results in hex. Only the chr(00) was affected and changed to x'20'. So, it appears that my only problem is to be able to put a x'00' into the output without it being translated to space.

Is there a better tool to use for this flat file? Something that comes with Oracle?

Many, many thanks .... Craig

 

[hoinz]

Hi Craig,

did you consider Embedded SQL in some programming language like C or Cobol? There are Oracle precompilers for them. I know it could be done with Pro*C. And from your profile I gather that you are good at Cobol. You might want to try Pro*Cobol. However, if you never did it before, you may find a steep learning curve.

regards