RE: SQL Loader Help in 8.1.6

From: Wendy Y <lannyue_at_yahoo.com>
Date: Sun, 25 Mar 2001 11:06:44 -0800
Message-ID: <F001.002D763A.20010325101040@fatcity.com>

  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" <AponteT_at_hsn.net> 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:lannyue_at_yahoo.com]Sent: Friday, March 23, 2001 2:47 PMTo: Multiple recipients of list ORACLE-LSubject: 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 DATAREPLACEINTO 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. Received on Sun Mar 25 2001 - 13:06:44 CST