RE: Packed decimal
Russ - What do you have in mind? Oracle stores numbers in its own format in its own datafiles. Are you looking to hack the Oracle datafiles? Or do you have an input file in packed decimal format that your are looking at loading into Oracle? If you could tell us the objective you are seeking to achieve, perhaps we could help you. Or maybe not. :-) My schedule doesn't allow me much spare time to hack Oracle datafiles. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, October 09, 2002 2:09 PM To: Multiple recipients of list ORACLE-L 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: DENNIS WILLIAMS INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Packed decimal
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 = 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 toif
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
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
RE: Packed decimal
Oracle uses a proprietary field format for numbers. I dont think it is packed decimal, however, I believe most of the Cobol or PL/I programs I wrote against Oracle RDBMS on the mainframe used Packed Decimal to represent number fields. You can use the SIGN function to determine the sign of the value, if thats what you are looking for. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web: www.compuware.com -Original Message- From: Brooks, Russ [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 09, 2002 3:09 PM To: Multiple recipients of list ORACLE-L 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 The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it.
