ORAOCI8 Stage Error ORA-01438: value larger than specified.
Moderators: chulett, rschirm, roy
ORAOCI8 Stage Error ORA-01438: value larger than specified.
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
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
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,
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,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
Actually, it's Oracle that doesn't always point it out.talk2shaanc wrote:Unfortunately Gaurav, datastage doesnt point out the column for which its giving error.
![Evil or Very Mad :evil:](./images/smilies/icon_evil.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India
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.chulett wrote:Actually, it's Oracle that doesn't always point it out.Seems to be dependant on the Client version.
Shantanu Choudhary
Thanks
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
![Idea :idea:](./images/smilies/icon_idea.gif)
Set a breakpoint on the input link, and edit the breakpoint expression to something like
Code: Select all
Len(column_name) > 9
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.
![Confused :?](./images/smilies/icon_confused.gif)
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers