Oracle error 01841

vijay.barani · Post by **vijay.barani** » Tue Sep 14, 2010 10:45 pm

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..

ray.wurlod · Post by **ray.wurlod** » Wed Sep 15, 2010 12:22 am

Use the interactive Debugger (in the Designer client) to see what is happening with the data on each link.

vijay.barani · Post by **vijay.barani** » Wed Sep 15, 2010 1:21 am

ray.wurlod wrote:Use the interactive Debugger (in the Designer client) to see what is happening with the data on each link. ...

Hi Ray,
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..

ray.wurlod · Post by **ray.wurlod** » Wed Sep 15, 2010 4:25 am

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?

vijay.barani · Post by **vijay.barani** » Wed Sep 15, 2010 6:12 am

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? ...

Sorry Ray, anyway this is the error i am encountering :
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.

chulett · Post by **chulett** » Wed Sep 15, 2010 6:45 am

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.

vijay.barani · Post by **vijay.barani** » Wed Sep 15, 2010 6:56 am

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.

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 · Post by **chulett** » Wed Sep 15, 2010 7:33 am

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.