Date warning ,while loading into oracle table
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
Date warning ,while loading into oracle table
Hi All,
Iam getting following warning when iam loading into the Oracle table
BUSINESSATTCOMLOADTBLE0605..fmtrec: At row 15886, link "updtrec", while processing column "ACTIVATE_DT"
Value treated as NULL
Attempt to convert String value "2005-10-19 11:21:38" to Date type unsuccessful
but all the records got updated,
here is the transformation i applied on the date field
if Not(IsNull(insandupdtrec.Activate_Date)) Then TrimB(TrimF(insandupdtrec.Activate_Date))[1,19] Else @NULL
and the input date format in the file is as follows,
2005-10-19 11:21:38.0
Could some one please shed some light on this...
Thanks to all ,
Iam getting following warning when iam loading into the Oracle table
BUSINESSATTCOMLOADTBLE0605..fmtrec: At row 15886, link "updtrec", while processing column "ACTIVATE_DT"
Value treated as NULL
Attempt to convert String value "2005-10-19 11:21:38" to Date type unsuccessful
but all the records got updated,
here is the transformation i applied on the date field
if Not(IsNull(insandupdtrec.Activate_Date)) Then TrimB(TrimF(insandupdtrec.Activate_Date))[1,19] Else @NULL
and the input date format in the file is as follows,
2005-10-19 11:21:38.0
Could some one please shed some light on this...
Thanks to all ,
The substring of the first 19 characters should be fine, there's nothing "wrong" with the timestamp value in the "Attempt to convert" message. Kris asked the key questions... and the answers should be 'Yes', 'Timestamp' and 'Generated' in that order. ![Wink :wink:](./images/smilies/icon_wink.gif)
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
If you read the OCI stage pdf documentation, the difference will become clear. In a nutshell, the stage will generate either a TO_CHAR() or a TO_DATE() function into your sql automagically and the format of the mask will change depending on the datatype and the action.
I always stick with Timestamp when dealing with Oracle DATE fields so the time portion is under my control:
Change the datatype to a Timestamp in the target OCI stage and it sounds like you'll be fine. You'll see it is then expecting exactly the same format as you are supplying.
I always stick with Timestamp when dealing with Oracle DATE fields so the time portion is under my control:
Code: Select all
Timestamp: TO_DATE(YourField,'YYYY-MM-DD HH24:MI:SS')
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Re: Date warning ,while loading into oracle table
Hi,
Pl refer viewtopic.php?t=100893 link , this will through u some lights on your issue.
Pl refer viewtopic.php?t=100893 link , this will through u some lights on your issue.
-
- Participant
- Posts: 85
- Joined: Fri Nov 18, 2005 5:35 am
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
Is the generated query showing to_timestamp('2005-10-19 11:21:38.0','YYYY-MM-DD HH:MI:SS:FF') ? if not you can go head with user defined or else default format is to_timestamp('19-oct-05 11:21:38.0','DD-MON-YY HH:MI:SS:FF') so in transformer get date to the above default format and insert.
Date will not take fractional second precision as timestamp do...............
Thanks,
ANupam
Date will not take fractional second precision as timestamp do...............
Thanks,
ANupam
-
- Premium Member
- Posts: 614
- Joined: Fri Feb 06, 2004 3:59 pm
Thanks for all your responses which helped..
yes , all the warnings are gone when i changed the datatype to
timestamp in OCI stage, however i have doubt , as i was loading
about 15000 rows and as almost all the date fields are populated,
all times job should abort , as it will exceed 50 warnings, i ran twice , once
it was aborting as the warnings limit exceed and other time , it loaded all
of records with some warnings..
this part was not clear, it should fail even on second run...
why was this happening..?
Any ideas..
Thanks all
yes , all the warnings are gone when i changed the datatype to
timestamp in OCI stage, however i have doubt , as i was loading
about 15000 rows and as almost all the date fields are populated,
all times job should abort , as it will exceed 50 warnings, i ran twice , once
it was aborting as the warnings limit exceed and other time , it loaded all
of records with some warnings..
this part was not clear, it should fail even on second run...
why was this happening..?
Any ideas..
Thanks all
But Craig, why would the job finish with different number of warnings when run 'n' number of times. In that case, it might finish with few warnings once and it might abort when it hits the default number of warnings.
No matter how many times we run the job, if the datatype is set to DATE instead of TIMESTAMP it should abort everytime. Am I thinking in the correct direction?![Question :?:](./images/smilies/icon_question.gif)
![Confused :?](./images/smilies/icon_confused.gif)
No matter how many times we run the job, if the datatype is set to DATE instead of TIMESTAMP it should abort everytime. Am I thinking in the correct direction?
![Question :?:](./images/smilies/icon_question.gif)
Hard to answer without knowing the exact job design.
Does 'n number of times' mean each run was done with the same input data set, or with different data each time? Was the target reset between each run or allowed to accumlate changes?
Unless you duplicate the process exactly each time, more than likely you won't get exactly the same outcome each time. You would need to understand why the warnings you are seeing are being generated - or not being generated - from run to run.
![Confused :?](./images/smilies/icon_confused.gif)
Does 'n number of times' mean each run was done with the same input data set, or with different data each time? Was the target reset between each run or allowed to accumlate changes?
Unless you duplicate the process exactly each time, more than likely you won't get exactly the same outcome each time. You would need to understand why the warnings you are seeing are being generated - or not being generated - from run to run.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
I agree. Since the number of rows( loaded into target) in question was 15000 rows from OP, I was taking into consideration that each job run was run with the same input dataset. But you are right that the process might not have been duplicated exactly from run to run( the input dataset might be different for each job run) and hence different number of warnings for each job run.chulett wrote:Hard to answer without knowing the exact job design.![]()