Connector Oracle Error

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
thurmy34
Premium Member
Premium Member
Posts: 198
Joined: Fri Mar 31, 2006 8:27 am
Location: Paris

Post by thurmy34 »

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
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

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.
thurmy34
Premium Member
Premium Member
Posts: 198
Joined: Fri Mar 31, 2006 8:27 am
Location: Paris

Post by thurmy34 »

The following sql

Code: Select all

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

Code: Select all

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
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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

"You can never have too many knives" -- Logan Nine Fingers
thurmy34
Premium Member
Premium Member
Posts: 198
Joined: Fri Mar 31, 2006 8:27 am
Location: Paris

Post by thurmy34 »

Hi
The mydate column is a date.
The select was launched with sqldeveloper.
Thanks
Hope This Helps
Regards
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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 all

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

"You can never have too many knives" -- Logan Nine Fingers
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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 all

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

"You can never have too many knives" -- Logan Nine Fingers
Post Reply