ORAOCI8 Stage Error ORA-01438: value larger than specified.

[gauravb]

Hi Group ,

I am trying to load a large data to Oracle using OCI stage .
while loading i get this error :

MasterSnap..ORAOCI_LOAD.IDENT1: ORA-01438: value larger than specified precision allows for this column

While i know that this error is due to truncation of data ( that is i am trying to insert more data than this column can take )

How can i know which is the column needs to be changed ?
i can try to insert one row but the there are 300 columns so even trying to make a insert statement will take 2 hrs .

Also is there a way which i can re-direct that rejected row in another seq file.

Thanks in Advance

MasterSnap..ORAOCI_LOAD.IDENT1: ORA-01438: value larger than specified precision allows for this column

MasterSnap..ORAOCI_LOAD.IDENT1: The value of the row is: ID = 11177 CONTRACT = 111-2006096-999 STATE_CODE = 05 CNTY_CODE = 071 CITY_CODE = 2770 INSPECTOR = NULL INSPEC_DATE = NULL ASSET_VENDOR = NULL A_GUAR_RESID = NULL A_OP_LEASE_VALUE = NULL PROP_STATUS = 90 ASSET_MAKE = NULL AS_TRUE_VALUE = NULL FEATURE_RECORDS = NULL A_SIC_CODE = 8011 A_COLLATERAL_CD = NULL A_COLLATERAL_CONTRACTS = NULL A_DELIVERY_DATE = NULL EQUIP_CODE = 100200 EQUIP_COST = 401261 EQUIP_COST_PCT = NULL EQUIP_DESC = Chairs A_BRANCH = AA A_REGION = 001

[roy]

Hi,
Any solution will make you test it yourself.
My sugestion:
use DS to generate a file with a valid insert statement and try running it in sqlplus it usually points you to the right column if you can't get it using DS.
IHTH,

[ketfos]

Hi,
Before you do anythng else, I would strongly suggest you to look into the order in which you naming your columns in insert statements and the order the data ins getting inserted into them.

Ketfos

[talk2shaanc]

I agree with you Ketfos.
Unfortunately Gaurav, datastage doesnt point out the column for which its giving error.
Best way is use sqlldr, if you have many columns else you can also use normal insert script.

[Sainath.Srinivasan]

Check the values in the numeric columns.

[chulett]

Unfortunately Gaurav, datastage doesnt point out the column for which its giving error.

Actually, it's Oracle that doesn't always point it out. Seems to be dependant on the Client version.

Since the message is about precision, then as noted it only applies to the numeric fields. You get to check each one, one by one, to see which one has more digits in it than can fit in your column definition. For example, from your 'value of the row is' log entry:

EQUIP_CODE = 100200 where EQUIP_CODE is defined as a NUMBER(5) would cause that error.

[talk2shaanc]

Actually, it's Oracle that doesn't always point it out. Seems to be dependant on the Client version.

Well I have wrked on Oracle9i, and i remember in that if i use insert script, and if any of the numeric column has a value greater than its size. The cursor goes to that position and you get an error message below in the message bar in Toad.

[gauravb]

Yep ,
I found the error , after 4 hrs . what i did was just tried with 50 row , then again with 50 . untill i got the column :(.

i can also make use of the rejected flag , what i intend to do is re- direct the rejected records to seq file and make inserts for them only.

[ray.wurlod]

You could have used the Debugger here.
Set a breakpoint on the input link, and edit the breakpoint expression to something like

Code: Select all

Len(column_name) > 9 

[chulett]

You already had your rejected row in the log. One of the values on that row should have been the culprit.

Something to keep in mind - setting the Array Size to something larger than 1 can make it report the wrong row (or rows) in situations like this. Setting it back to 1 and running the job one more time will get you the 'real' row.