Hi,
I am facing problem while loading data into the target database,which is oracle.THe stage i used is Oracle OCI.may i know th issue why i am unable to load the timestamp into the target.I suspect the date coming from source is being converted into internal format.
But now i need to load the date column into the target.
Could naybody advice on this,plzz..
Oracle error 01841
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 78
- Joined: Wed Jun 04, 2008 2:59 am
Oracle error 01841
Warm Regards,
Vijay
Vijay
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 78
- Joined: Wed Jun 04, 2008 2:59 am
Hi Ray,ray.wurlod wrote:Use the interactive Debugger (in the Designer client) to see what is happening with the data on each link. ...
I am getting the ora 01841 error mentioning the date is not the valid one.Do we need to load the date column into target only after parsing into char or is there any possibility for us to load with datatype as date/timestamp..
Warm Regards,
Vijay
Vijay
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 78
- Joined: Wed Jun 04, 2008 2:59 am
Sorry Ray, anyway this is the error i am encountering :ray.wurlod wrote:That's the first time you've mentioned that 01841 is an Oracle error code. What does the oerr command tell you about this code? ...
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
The source data has many records which consists of the date 01-01-2000 00:00:00.I suspect this is creating the trouble.
Warm Regards,
Vijay
Vijay
Tell us exactly how you are handling that value: data type in source and target, transformations, IConv/OConv, source and target sql, etc etc. We would need an appropriate level of details from you in order to be able to help in anything more than a very generic manner.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 78
- Joined: Wed Jun 04, 2008 2:59 am
I am fetching data(timstamp datatype for the column required) from source "SQL server" .the data coming from ODBC is directly loaded into transformer and then into Oracle stage.while loading into oracle the datatype is again timestamp.i am neither using any converstions in transformer nor any typecasting is done.chulett wrote:Tell us exactly how you are handling that value: data type in source and target, transformations, IConv/OConv, source and target sql, etc etc. We would need an appropriate level of details from you in order to be able to help in anything more than a very generic manner.
Warm Regards,
Vijay
Vijay
Hence the problem. If you really want to build 'environment neutral' solutions for date/time handling, you'll bring them in as strings from your source, ensure that string source is in a valid format for the target and then use TO_DATE() or TO_TIMESTAMP() in your target sql with a matching format mask.
I use ISO Timestamps for DATE or TIMESTAMP fields, so make sure it gets into YYYY-MM-DD HH24:MI:SS format before passing it over to Oracle with one of the functions I mentioned earlier.
This takes all consideration of the NLS_DATE format of the source or target out of the equation.
I use ISO Timestamps for DATE or TIMESTAMP fields, so make sure it gets into YYYY-MM-DD HH24:MI:SS format before passing it over to Oracle with one of the functions I mentioned earlier.
This takes all consideration of the NLS_DATE format of the source or target out of the equation.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers