DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
thurmy34



Group memberships:
Premium Members

Joined: 31 Mar 2006
Posts: 194
Location: Paris
Points: 1484

Post Posted: Tue Dec 17, 2019 6:00 am Reply with quote    Back to top    

DataStage® Release: 11x
Job Type: Parallel
OS: Windows
Hi
When reading a oracle table via the connector with the RCP enable i have the following error

Code:
Can not convert to framework type <" (CC_PXBigBufferHandler::readField_TIMESTAMP_FS_STRING, file CC_PXBigBufferHandler.cpp, line 4˙830)

Note the strange Datatype.
Do you have un idea ?
Regards

_________________
Hope This Helps
Regards
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43053
Location: Denver, CO
Points: 222300

Post Posted: Wed Dec 18, 2019 4:52 am Reply with quote    Back to top    

Check for fixpacks / patches for your version that you haven't applied yet. Open a support case. Or both. Wink

_________________
-craig

"May the bridges I burn light my way forward"
Rate this response:  
Not yet rated
thurmy34



Group memberships:
Premium Members

Joined: 31 Mar 2006
Posts: 194
Location: Paris
Points: 1484

Post Posted: Wed Dec 18, 2019 5:20 am Reply with quote    Back to top    

In fact the problem comes from a fals date (00/01/3689) in a timestamp column.
To handle that i have to declare the column in varchar(10).
Thanks

_________________
Hope This Helps
Regards
Rate this response:  
Not yet rated
PaulVL



Group memberships:
Premium Members

Joined: 17 Dec 2010
Posts: 1286

Points: 8453

Post Posted: Wed Dec 18, 2019 7:54 am Reply with quote    Back to top    

Did you use the schema from the table definition as found on that Oracle box or did you hand code it? I doubt any properly defined date column on Oracle would reflect a 00 month/day value.
Rate this response:  
Not yet rated
thurmy34



Group memberships:
Premium Members

Joined: 31 Mar 2006
Posts: 194
Location: Paris
Points: 1484

Post Posted: Thu Dec 19, 2019 1:26 am Reply with quote    Back to top    

The following sql
Code:
select ID,TO_CHAR(MYDATE, 'DD/MM/YYYY hh24:mi:ss'),cast(MYDATE as timestamp) from MYTABLE where ID=1

gives this
Code:
1   00/01/3948 00:00:00   31/12/47 00:00:00,000000000


Like I said i'm using RCP so i don't declare any datatypes

_________________
Hope This Helps
Regards
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43053
Location: Denver, CO
Points: 222300

Post Posted: Tue Dec 24, 2019 5:50 am Reply with quote    Back to top    

So... how is your MYDATE column defined in Oracle? It doesn't seem to be a DATE otherwise your TO_CHAR would work a bit more better. And the TO_TIMESTAMP function might be a better choice than CAST for the second conversion. Hard to know from this end, not knowing the source datatype or what the contents look like before the conversion attempt.

Please clarify your "gives this" example - gives this where? That's inside the job or from a tool like sqlplus or Toad or... ?

_________________
-craig

"May the bridges I burn light my way forward"
Rate this response:  
Not yet rated
thurmy34



Group memberships:
Premium Members

Joined: 31 Mar 2006
Posts: 194
Location: Paris
Points: 1484

Post Posted: Wed Dec 25, 2019 11:41 pm Reply with quote    Back to top    

Hi
The mydate column is a date.
The select was launched with sqldeveloper.
Thanks

_________________
Hope This Helps
Regards
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43053
Location: Denver, CO
Points: 222300

Post Posted: Thu Dec 26, 2019 10:03 am Reply with quote    Back to top    

Sorry to keep harping on this but when you say it is a date do you mean DATE as in the datatype? Because your extracted date isn't valid and trying to convert that to a DATE fails as expected:

Code:
select to_date('00/01/3948', 'mm/dd/yyyy') from dual;
ORA-01843: not a valid month

Meaning it should not be possible to store the value you extracted in a DATE field. What do you see when you simply SELECT MYDATE without the TO_CHAR function? Would still suggest you open a support case if you haven't done so already, to see if this is some kind of odd Connector/RCP bug.

_________________
-craig

"May the bridges I burn light my way forward"
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 43053
Location: Denver, CO
Points: 222300

Post Posted: Thu Dec 26, 2019 10:04 am Reply with quote    Back to top    

Sorry to keep harping on this but when you say it is a date do you mean DATE as in the datatype? Because your extracted date isn't valid and trying to convert that to a DATE fails as expected:

Code:
select to_date('00/01/3948', 'mm/dd/yyyy') from dual;
ORA-01843: not a valid month

Meaning it should not be possible to store the value you extracted in a DATE field. What do you see when you simply SELECT MYDATE without the TO_CHAR function? Would still suggest you open a support case if you haven't done so already, to see if this is some kind of odd Connector/RCP bug.

_________________
-craig

"May the bridges I burn light my way forward"
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours