Oracle-L: Re: Packed decimal

From: Tim Gorman <Tim_at_SageLogix.com>
Date: Thu, 10 Oct 2002 10:05:00 -0800
Message-ID: <F001.004E5F94.20021010100500@fatcity.com>

SQL*Loader understands how to translate PACKED and ZONED DECIMAL format; pretty sure that PRO*Cobol does as well, though I don't do Cobol...

If you're interested in conversion routines, I've a "C" function named "ptof()" that converts packed-decimal to floating-point; wrote it 10 years ago and have just been hauling it around since. However, the converse function ("ftop()") seems to have disappeared in the mists of time. It wouldn't be too hard to reconstruct, but the need hasn't arisen...

The "ptof()" source code comes with a pretty explanation of packed-decimal format, reprinted here:

This routine converts the packed decimal in the input argument buffer into a double floating-point number.

If these assumptions are incorrect, then this routine won't work!

each 4-bit "nibble" in the packed decimal field contains a decimal digit in hex...
the decimal digits are ordered from right to left when scanning from the lowest order digit to the highest...
the "sign" nibble is the rightmost nibble in the packed decimal field...
if the "sign" nibble is 0xA, 0xC, or 0xE, then the packed decimal's value is positive...
if the "sign" nibble is 0xB, 0xD, or 0xF, then the packed decimal's value is negative...
the actual byte width of the field is:

                  if (decimal width is an "odd" number then
                          (decimal width + 1) / 2)
                  else
                          (decimal width / 2) + 1

          7. the "sign" nibble is ALWAYS the rightmost nibble

This is a diagram of a packed decimal field defined "DEC(9,5)" in Cobol:

       0       1       2       3       4       5   byte offset
       |---+---|---+---|---+---|---+---|---+---|
       |   |   |   |   |   |   |   |   |   |   |

          0x12    0x34    0x56    0x78    0x9B     hex value of each byte

       0x1 0x2 0x3 0x4 0x5 0x6 0x7 0x8 0x9 0xB    hex value of each nibble
                                            ^
                                            sign = 0xB means "negative"

This packed decimal field contains the value "-1234.56789", because it's format specification (i.e "DEC(9,5)") specifies a total field width of nine digits, five of which are to the right of the decimal point, and the sign is "0xB", which means it's a negative number... Just recently, I had the dubious pleasure of writing PACKED2NUMBER and NUMBER2PACKED functions in PL/SQL. These took packed-decimal numbers as input (in RAW format) and returned a NUMBER and vice-versa...

I haven't posted them, but I'd be glad to if you need them...

Original Message ----- From: Brooks, Russ To: Multiple recipients of list ORACLE-L Sent: Wednesday, October 09, 2002 1:09 PM Subject: Packed decimal

Hi,
Is there any way to identify values in a field, defined in Oracle as number(11,2), with a packed decimal format? Or to update the field with packed decimal values?

Thanks,
Russ

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Tim Gorman
INET: Tim_at_SageLogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Oct 10 2002 - 13:05:00 CDT