From: Stephen Pennine Newsgroups: comp.databases.oracle.server,comp.databases.oracle Subject: Convert IBM Zoned-Decimal EBCDIC data to NUMBER format Date: Mon, 09 Oct 2000 20:20:49 -0400 Message-ID: <7gn4uso9lrda79lfb430kcrfbvnm6sm4tm@4ax.com> X-Newsreader: Forte Agent 1.8/32.548 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit NNTP-Posting-Host: news Lines: 25 X-Authenticated-User: spennine X-Comments: This message was posted through Newsfeeds.com X-Comments2: IMPORTANT: Newsfeeds.com does not condone, nor support, spam or any illegal or copyrighted postings. X-Comments3: IMPORTANT: Under NO circumstances will postings containing illegal or copyrighted material through this service be tolerated!! X-Report: Please report illegal or inappropriate use to You may also use our online abuse reporting from: http://www.newsfeeds.com/abuseform.htm X-Abuse-Info: Please be sure to forward a copy of ALL headers, INCLUDING the body (DO NOT SEND ATTACHMENTS) Organization: Newsfeeds.com http://www.newsfeeds.com 73,000+ UNCENSORED Newsgroups. We're importing IBM mainframe data to an 8i database via the UTL_FILE package. The data is produced on an IBM in COBOL, as zoned-decimal (COBOL PICture S9(10)V999). Because this format of data has letters in the last byte of the number, which denotes the numbers's sign, Oracle doesn't like it. The TO_NUMBER function works with display numbers (no sign), but not with the signed data. How do we communicate the fact that this data is 'zoned', not dislpay to the TO_NUMBER function? None of the NLS_PARAMS I've seen account for the 'zone' or 'overpunch' byte that represents both the least significant digit, and the sign of the number itself. Is there a direct way to translate this type of data into an understandable form for Oracle? I could write a procedure to parse the number (replacing the letter with the approprate number, and signing the entire number accordingly). But there must be a better way. -----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World! -----== Over 80,000 Newsgroups - 16 Different Servers! =-----