Home » Developer & Programmer » Warehouse Builder » Need urgent help with DATE Format issue with Oracle OWB 10gR2 (OWB 10GR2)
Need urgent help with DATE Format issue with Oracle OWB 10gR2 [message #288280] Sun, 16 December 2007 22:23 Go to next message
muralil
Messages: 2
Registered: December 2007
Junior Member
We have From_date, and To_Date to pass for a mapping (in fact any mapping).

these parameters works fine from passing from UNIX script to Oracle OWB 9.2

Same script to call OWB 10g R2 mapping with same parameters is not working.

From date or To Date is unable to pickup any date formats !!!!
But if we pass SYSDATE or SYSDATE -10 the mapping works

What could be the problem? and any help?


-Murali
919 961 1598

[Updated on: Sun, 16 December 2007 22:23]

Report message to a moderator

Re: Need urgent help with DATE Format issue with Oracle OWB 10gR2 [message #288281 is a reply to message #288280] Sun, 16 December 2007 22:27 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
Your nls_date_format parameters are probably different and it is attempting an implicit conversion that is failing. You should make sure that you pass dates, using the built-in to_date function to convert the strings to dates.
Re: Need urgent help with DATE Format issue with Oracle OWB 10gR2 [message #288294 is a reply to message #288281] Sun, 16 December 2007 23:18 Go to previous messageGo to next message
muralil
Messages: 2
Registered: December 2007
Junior Member
Thanks for a Quick Response,

Should we try for a session

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

or should we try at instance level?

thanks again.


-Murali
Re: Need urgent help with DATE Format issue with Oracle OWB 10gR2 [message #288299 is a reply to message #288294] Sun, 16 December 2007 23:42 Go to previous message
Barbara Boehmer
Messages: 7860
Registered: November 2002
Location: California, USA
Senior Member
muralil wrote on Sun, 16 December 2007 21:18

Thanks for a Quick Response,

Should we try for a session

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

or should we try at instance level?

thanks again.


-Murali



Ideally neither. Instead, you should pass your dates like:

TO_DATE ('16-DEC-2007', 'DD-MON-YYY')

so that it works no matter what the nls_date_format parameter is set to. You should not rely on implicit conversions. However, if you have a lot of code that is passing strings instead of dates, until you fix that code, figure out what the nls_date_foramt is on the one that works and set it to that on the one that doesn't. But, that may affect other code that relies on that date format. Ultimately, you should fix your code to pass dates, not strings. If you just need it to work for one run right now, then alter the session, but if it needs to run beyond that, then change it in your initialization parameters and bounce your database, so that it takes affect.

Previous Topic: OWB Validation Error
Next Topic: INS0029 Error OWB10g Runtime Repository Install Failed
Goto Forum:
  


Current Time: Fri Apr 18 16:54:23 CDT 2014

Total time taken to generate the page: 4.08296 seconds