Thanks, I got that work out now.
It's an option (direct=true) in the control file in 8.1.6 was missing. But I still don't understand that 8.0.5 will be able to compile without this option. I'm running 8.0.5 on my local machine, but 8.1.6 is remote on a UNIX box. Will these different environment matters?
Wendy
"Aponte, Tony" <[EMAIL PROTECTED]> wrote:
Is your mainframe using EBCDIC? If so there is a translation table that explains what the "}" translates into. The sign of the field is trailing (E.I.. at the end of the field for non-Cobolers)
These indicate that the sign of the number is negative.
}=0
R=9
Q=8
P=7
O=6
N=5
M=4
L=-
K=-
J=1
These are for positive numbers:
{=0
A=1
B=2
C=3
D=4
E=5
F=6
G=7
H=8
I=9
The example you mentioned (000000}) is really all zeroes. A value of 1023A really equals 10231. An example of a negative number would be 112233R which equals -1122339. The Utilities manual might have a sample of how to convert external numbers with trailing signs. You could also code a function to do translation and return the resulting number. Then you can use it in the SqlLdr control file.
Tony Aponte
-----Original Message-----
From: Wendy Y [mailto:[EMAIL PROTECTED]]
Sent: Friday, March 23, 2001 2:47 PM
To: Multiple recipients of list ORACLE-L
Subject: SQL Loader Help in 8.1.6
Dear Experts:
This does not make any sense to me, can you help me out?
I have a control file to interpret the mainframe data and then write to my table in Oracle Database.
The mainframe data looks like "000000000}", The table is designed as Number(10,2), The control file use zoned(10,2).
All these combination will work fine on Oracle 7.3/8.0.5, but will fail on 8.1.6. (the message said: rejected on XXX field)
If I modify table as Varchar2(10), it will work, but the data format is not the way I want. the format should be "0.00" when it's 0, instead of "00000". Also "+" or "-" should be able to display. How can I make this work in 8.1.6?
Here are part of the control file:
LOAD DATA
REPLACE
INTO TABLE C_FINANCIAL
(CONSOLI_ID POSITION(001:003) CHAR, ....
TAX_CREDIT_AMT POSITION(177:186) zoned(10, 2),
QTY_ORIGINAL POSITION(187:196) zoned(10),... )
Thanks
Wendy
Do You Yahoo!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail.
Do You Yahoo!?
Yahoo! Mail Personal Address - Get email at your own domain with Yahoo! Mail.
