Re: Write numbers in binary form with UTL_FILE?

Hi Stephen,

I prepared the following study in an 8.1.6.3.1 instance on NT 4.0 SP 6a:

• Define variable charset for discovering the currently used charset. column charset new_value charset

select value as charset
from v$nls_parameters
where parameter = 'NLS_CHARACTERSET'
/

select c1 -- our original string
 , c6 || c7 as res -- our result, length field binary plus EBCDIC string  , dump (c6 || c7) -- our result as represented within Oracle from (
select c1 -- our original string
 , chr (to_number (substr (c5, 1, 2), 'XX')) -- first byte of binary length field
 || chr (to_number (substr (c5, 3, 2), 'XX')) -- second byte of binary length  field
 || chr (to_number (substr (c5, 5, 1) || 'C', 'XX')) as c6 -- third byte of binary length field, contains BCD-append 'C'  , c7 -- our string in EBCDIC
from (
select c1 -- original string to be represented  , c5 -- length in format 09999 as char
 , c7 -- EBCDIC string
from (
select object_name as c1 -- our original string  , to_char (length (object_name), 'FM09999') as c5 -- our length field  , convert (object_name, '&&charset', 'WE8EBCDIC37') as c7 -- our string in EBCDIC
from (
-- We take the object_name twice with a blank to view longer strings. select object_name || ' ' || object_name as object_name from user_objects

)
)
)
)

The idea is as follows:

1. Discover your currently used character set.
2. Convert your data by the CONVERT function to another character set.
3. Calculate the length and represent it as BCD number with five digits, the last digit occupied by the postive sign 'C'. We assume that the length field is always five digits.
4. Represent that BCD length by three characters.
5. Concatenate the length field with your EBCDIC string.

How do you represent line breaks? In many EBCDIC systems this is not done by CR or LF or a combination of both but by a length field of the record.

Martin

Stephen Doom wrote:
>
> Anyone with experience writing binary data, to flat text Operating
> System files?
>
> We're familiar with UTL_FILE, but it seems to convert everything to
> character before writing out.
>
> We have a need on an EBCDIC platform for an extract from Oracle. That
> file needs to have binary data (that's COMP or COMP-3 in COBOL) within
> it. The binary number we need (which will always positive), will
> contain the LENGTH of a corresponding VARCHAR2 column.
>
> The IBM needs it this way so it can load VARCHAR2 data from Oracle,
> into VARCHAR2 data in DB/2. I guess DB/2 isn't smart enough to parse
> the string and figure it out on it's own.
>
> And we don't have any tools to get them to talk to one another.
>
> So short of a solution within DB/2 (to eliminate that requirement) or
> Oracle, we'll need to resort to COBOL to re-create the file on the IBM
> side, using a CHAR field in the Oracle extract containing the length,
> which we'll then convert to COMP or COMP-3 on the IBM before the load
> to DB/2.
>
> Kindly reply, if you would, to pennine.s_at_bcbsri.org, as I don't get
> out to the group that often.
>
> Posted Via Usenet.com Premium Usenet Newsgroup Services
> ----------------------------------------------------------
> ** SPEED ** RETENTION ** COMPLETION ** ANONYMITY **
> ----------------------------------------------------------
> http://www.usenet.com
Received on Sat Mar 24 2001 - 01:19:53 CST