Date conversion when using Oracle Enterprise stage as Target
Moderators: chulett, rschirm, roy
Date conversion when using Oracle Enterprise stage as Target
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.
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.
Kiran Vaduguri
As soon as the fear approaches near, attack and destroy it.
As soon as the fear approaches near, attack and destroy it.
-
- Premium Member
- Posts: 1735
- Joined: Thu Mar 01, 2007 5:44 am
- Location: Troy, MI
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.
Kiran Vaduguri
As soon as the fear approaches near, attack and destroy it.
As soon as the fear approaches near, attack and destroy it.
I implemented below conversion: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. ...
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
Kiran Vaduguri
As soon as the fear approaches near, attack and destroy it.
As soon as the fear approaches near, attack and destroy it.
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
Thanks for response.
Kiran Vaduguri
As soon as the fear approaches near, attack and destroy it.
As soon as the fear approaches near, attack and destroy it.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers