Oracle DATE type, RCP, and loss of time portion

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
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Oracle DATE type, RCP, and loss of time portion

Post by qt_ky »

I have recently inherited some Oracle RCP jobs and am doing some debugging.

Is it known behavior that when using RCP with the Oracle Connector to select a table with Oracle DATE types, which contain dates AND times, that you lose the time portion? The jobs go from the Connector to a Data Set. When I dump the Data Set to a text file, or just view the Data Set, only the date portion is there. We are losing time! :shock:

We do have the following setting set inside our dsenv file on the server:
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

Also when I view data from the Connector, it does not honor the specified format we have in the dsenv file. It only displays the date portion.

But if I go and view the same data via Oracle SQL Developer then I can see all the dates AND times.

Is there a setting to enable to allow us to retain the time portion? I had some trouble searching on a solution.

On a related note, is the DSXchange "Search for all terms" function no longer working? I searched on all terms "oracle date RCP" and got no results, yet google pointed me to multiple results. Example: viewtopic.php?p=401272
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

On the search issue, for some reason only the "exact match" option searches the entire database. Any other search is secretly time limited to the most recent 60 (90? something) days, from what I recall.
-craig

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