Date conversion when using Oracle Enterprise stage as Target

kiran259 · Post by **kiran259** » Fri Mar 26, 2010 3:57 am

My target is Oracle 10g database.Here is the code snippet.

Dataset----->T/F------->OracleEnterpriseStage

I have a varchar field from input dataset which is to be loaded as Date in Oracle target.
1)I kept StringToDate(<lnkname>,"%yyyy-%mm-%dd") in transformer---All records are rejected silently with job finish status
2)I inserted user-defined query with TO_DATE(orchestrate.<lnkname>,'YYYY-MM-DD').-----Job aborted with error 'ORA-1858: a non-numeric character was found where a numeric was expected'----
3)I removed date conversion and made query as Auto-generated----Records rejected

I tried with ODBC connector but no luck

Remaining tables which doesn't have any date fields in the tables are loading perfectly.
Are there any limitations for OracleEnt stage?Which is the better stage(ODBC Connector or Oracle EE) for loading the date fields without any contention?
I have gone through some posts relavant to the problem but I couldn't find solution.
Please advice.

chulett · Post by **chulett** » Fri Mar 26, 2010 6:35 am

#2 would be fine if your source column is a string in the format noted - YYYY-MM-DD.

priyadarshikunal · Post by **priyadarshikunal** » Fri Mar 26, 2010 7:00 am

What if you change the method from upsert to load? does it loads data correctly?

can you please post a sample of rejected record?

kiran259 · Post by **kiran259** » Fri Mar 26, 2010 7:16 am

Yes.The source column is in the same format defined 'YYYY-MM-DD'. One more point is ,when I import Date field from Oracle DB,it reports in datastage as Timestamp.But I assume that doesn't make much difference as it is oracle.

chulett · Post by **chulett** » Fri Mar 26, 2010 7:18 am

It does, actually, with a Timestamp you have to include the time portion even if it is all zeroes. I always treat Oracle DATE fields as Timestamps in job and ensure a proper time portion.

kiran259 · Post by **kiran259** » Sat Mar 27, 2010 12:10 am

chulett wrote:It does, actually, with a Timestamp you have to include the time portion even if it is all zeroes. I always treat Oracle DATE fields as Timestamps in job and ensure a proper time portion. ...

I implemented below conversion:
StringToDate(lnkname.column,'YYYY-MM-DD'):' ':'00':':':'00':':':'00'

Out of 4477 rows,one row loaded into oracle table.Rest of the rows rejected with Sqlcode:1403(update not found).

Thanks

kiran259 · Post by **kiran259** » Sat Mar 27, 2010 12:14 am

[quote="priyadarshikunal"]What if you change the method from upsert to load? does it loads data correctly?

I don't have DBA privileges to do direct load.

chulett · Post by **chulett** » Sat Mar 27, 2010 7:50 am

Two problems with your "conversion". First, no "toDate", leave it as a string to play this game. That or use a StringToTimestamp() with the proper mask after adding the zeroes. Second, just cat on the " 00:00:00" in once piece, no need for everything to be separate like that.

And your conversion choice will change what your SQL and data types need to be. For more cogent help, let us know what settings you are using in the OE stage, the column data types and what the SQL looks like. It all matters.

kiran259 · Post by **kiran259** » Sun Mar 28, 2010 7:39 am

Success!!I played it as string and followed #2 in my post.Now all the rows loading into the database.But I wonder when I put the data type field as per the target table in OE stage,why don't the DS load it?Can you justify?

Thanks for response.

chulett · Post by **chulett** » Sun Mar 28, 2010 8:18 am

A Date in DataStage isn't the same as a DATE in Oracle. Both are internal formats specific to their source. Hence the need for passing the value in as a string from an external source and using Oracle's TO_DATE() function to do the conversion during the load process.