RE: SQL Loader Help in 8.1.6

[Aponte, Tony]

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.