Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id g9AKuGC21354 for ; Thu, 10 Oct 2002 15:56:16 -0500 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id g9AKtQD21216 for ; Thu, 10 Oct 2002 15:55:33 -0500 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id LAA51839; Thu, 10 Oct 2002 11:30:18 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 004E5F94; Thu, 10 Oct 2002 10:05:00 -0800 Message-ID: Date: Thu, 10 Oct 2002 10:05:00 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Tim Gorman" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Tim Gorman" Subject: Re: Packed decimal Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_034B_01C2704C.8B7B6360" ------=_NextPart_000_034B_01C2704C.8B7B6360 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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! 1. each 4-bit "nibble" in the packed decimal field contains a decimal digit in hex... 2. the decimal digits are ordered from right to left when scanning from the lowest order digit to the highest... 3. the "sign" nibble is the rightmost nibble in the packed decimal field... 4. if the "sign" nibble is 0xA, 0xC, or 0xE, then the packed decimal's value is positive... 5. if the "sign" nibble is 0xB, 0xD, or 0xF, then the packed decimal's value is negative... 6. 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 =3D 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 -----=20 From: Brooks, Russ=20 To: Multiple recipients of list ORACLE-L=20 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 ------=_NextPart_000_034B_01C2704C.8B7B6360 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
SQL*Loader understands how to translate = PACKED and=20 ZONED DECIMAL format;  pretty sure that PRO*Cobol does as well, = though I=20 don't do Cobol...
 
If you're interested in conversion = routines, I've a=20 "C" function named "ptof()" that converts packed-decimal to=20 floating-point;  wrote it 10 years ago and have just been hauling = it around=20 since.  However, the converse function ("ftop()") seems to have = disappeared=20 in the mists of time.  It wouldn't be too hard to reconstruct, but = the need=20 hasn't arisen...
 
The "ptof()" source code comes with a = pretty=20 explanation of packed-decimal format, reprinted here:
  This = routine converts=20 the packed decimal in the input argument buffer
 into a double floating-point=20 number.
 
  If these assumptions are = incorrect, then=20 this routine won't work!
 
        1.=20 each 4-bit "nibble" in the packed decimal field contains = a
           = decimal=20 digit in hex...
 
        2.=20 the decimal digits are ordered from right to left when
           = scanning=20 from the lowest order digit to the highest...
 
        3.=20 the "sign" nibble is the rightmost nibble in the packed
           = decimal=20 field...
 
        4.=20 if the "sign" nibble is 0xA, 0xC, or 0xE, then the packed
           = decimal's=20 value is positive...
 
        5.=20 if the "sign" nibble is 0xB, 0xD, or 0xF, then the packed
           = decimal's=20 value is negative...
 
        6.=20 the actual byte width of the field is:
 
          &nbs= p;    =20 if (decimal width is an "odd" number then
          &nbs= p;            = ;=20 (decimal width + 1) / 2)
          &nbs= p;    =20 else
          &nbs= p;            = ;=20 (decimal width / 2) + 1
 
        7.=20 the "sign" nibble is ALWAYS the rightmost nibble
 
  This is a diagram of a = packed decimal=20 field defined "DEC(9,5)" in
  Cobol:
 
    =20 0       = 1      =20 2       = 3      =20 4       5   byte = offset
    =20 |---+---|---+---|---+---|---+---|---+---|
     = |  =20 |   |   |   |   |  =20 |   |   |   |   |
 
       =20 0x12    0x34    0x56   =20 0x78    0x9B     hex value of each=20 byte
 
     0x1 0x2 = 0x3 0x4 0x5=20 0x6 0x7 0x8 0x9 0xB    hex value of each = nibble
          &nbs= p;            = ;            =       =20 ^
          &nbs= p;            = ;            =       =20 sign =3D 0xB means "negative"
 
 This packed decimal field = contains the=20 value "-1234.56789", because
 it's format specification = (i.e=20 "DEC(9,5)") specifies a total field
 width of nine digits, five of = which are=20 to the right of the decimal
 point, and the sign is "0xB", = which means=20 it's a negative number...
Just recently, I had the dubious = pleasure of=20 writing PACKED2NUMBER and NUMBER2PACKED functions in PL/SQL.  These = took=20 packed-decimal numbers as input (in RAW format) and returned a NUMBER = and=20 vice-versa...
 
I haven't posted them, but I'd be glad = to if=20 you need them...
----- Original Message -----
From:=20 Brooks,=20 Russ
Sent: Wednesday, October 09, = 2002 1:09=20 PM
Subject: Packed decimal

Hi,
  Is there=20 any way to identify values in a field, defined in Oracle as = number(11,2), with=20 a packed decimal format?  Or to update the field with packed = decimal=20 values?
 
Thanks,
Russ
------=_NextPart_000_034B_01C2704C.8B7B6360-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tim Gorman INET: Tim@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@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).