Problem with routine result and format insert

jseclen · Post by **jseclen** » Fri Feb 27, 2009 9:48 am

Hi Forum,

I have a problem when insert record into oracle database, My job consist in a oracle source, a transformer, and a oracle target

oracle --> transformer --> oracle.

the target has 2 field (field1 varchar2, field2 date), when import the metadata that fields appear in the table definition with this type

field1 varchar
field2 timestamp

in the target field1 derivation assing the field1 from the source, and the target field2 assign a routine that return the system date in the format 'YMD', the routine syntax is DateSystem('','YMD'). In the manager test returns --> 20090227 and its ok.

when run the job, in the director log appear the next warnings:

s1..Oracle_OCI_8: INSERT INTO USR_ADMIN.O_CALL_TD_PROBE(FECHA1, FECHA2) VALUES(:1, TO_TIMESTAMP(:2, 'YYYY-MM-DD HH24:MI:SS'))

s1..Transformer_9: The value of the row is: FECHA1 = XXXXXX FECHA2 = 20092702

s1..Transformer_9: ORA-01843: not a valid month

why in the log appear the field2 value with the format different to the routine result???

routine result = 20000227 (YYYYMMDD)
log = 20092702 (YYYYDDMM)

the format is different in both cases.

i'm working with DataStage Enterprise 7.5.3 in a Itanium box, the oracle client is 10g, and the database target is oracle 9i.

the insert is not the problem, because i exec the query in the unix prompt with sqlplus and working fine, the problem is in the processing when DS change the date format order before insert.

i appreciate your help.

chulett · Post by **chulett** » Fri Feb 27, 2009 10:23 am

If your target field is a DATE, you should have TO_DATE in your sql, not a TO_TIMESTAMP. And you must match the format mask being used so the routine must return a full YYYY-MM-DD HH24:MI:SS timestamp for what you posted to work.

jseclen · Post by **jseclen** » Fri Feb 27, 2009 10:30 am

Hi Craig,

I run the sql query, exactly like the generated into oracle stage:

INSERT INTO USR_ADMIN.O_CALL_TD_PROBE(FECHA1, FECHA2) VALUES('hola', TO_TIMESTAMP('20090227', 'YYYY-MM-DD HH24:MI:SS'))

and insert without problems, the problem is the routine result, because return the value with diferent order.

routine result in the manager = '20090227'
value in the sql insert in the job log

FECHA1 = ely spa FECHA2 = 2009-27-02 (do you see the date format is wrong?)

Thanks for your comments

chulett · Post by **chulett** » Fri Feb 27, 2009 10:43 am

I find it hard to believe this actually works: TO_TIMESTAMP('20090227', 'YYYY-MM-DD HH24:MI:SS') when the mask doesn't match the data format, unless TO_TIMESTAMP is *very* forgiving of user errors. I need to test it. What Oracle stage are you using, OCI? And your actual Oracle target datatype is what - DATE? Or an actual TIMESTAMP of some kind?

jseclen · Post by **jseclen** » Fri Feb 27, 2009 11:21 am

Hi Craig

That is the sql instructions, you must to believe because it wotks.

--------------------------------------------

SQL> INSERT INTO USR_ADMIN.O_CALL_TD_PROBE (FECHA1,FECHA2) VALUES
('test',TO_TIMESTAMP('20090227','YYYY-MM-DD HH24:MI:SS'));

1 row created.

SQL> commit;

Commit complete.

----------------------------------------------

ANybody has an idea why the result routine changed in the job execution???

chulett · Post by **chulett** » Fri Feb 27, 2009 11:26 am

Wow... tested it myself and it actually does work. For both TO_DATE and TO_TIMESTAMP. Boggles me mind.

OK, with that out of the way I guess we're back to your format difference. If I had to guess, I'd say the problem is the fact that you're using a 10g client to talk to a 9i instance. It will work fine the other way around, we use a 9i client to talk to both 9i and 10g instances but there's no guarantee of compatability going the way you're going and I've seen it cause weird problems.

Any chance you could switch to a 9i client instead?

jseclen · Post by **jseclen** » Fri Feb 27, 2009 4:56 pm

Hi Craig,

Thanks for your help, i was searching in the forum and found an old post about the date format with NLS. That post was similar with my case and check the procedure and found the answer...

I verify the NLS Configuration in the administrator, and the Local Setting for Server jobs was US-ENGLISH (use the MDY format), and change it for DEFAULT (YMD format).

I test my job and ! oh sorprise ! the date format returned from the routine was ok, and the insert worked fine.

Thanks for your help :D

chulett · Post by **chulett** » Fri Feb 27, 2009 5:21 pm

Cool. Not sure how much I actually helped, but glad you found the problem regardless.