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.
Problem with routine result and format insert
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 133
- Joined: Wed Mar 05, 2003 4:19 pm
- Location: Lima - Peru. Sudamerica
- Contact:
Problem with routine result and format insert
Saludos,
Miguel Seclén
Lima - Peru
Miguel Seclén
Lima - Peru
-
- Participant
- Posts: 133
- Joined: Wed Mar 05, 2003 4:19 pm
- Location: Lima - Peru. Sudamerica
- Contact:
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
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
Saludos,
Miguel Seclén
Lima - Peru
Miguel Seclén
Lima - Peru
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 133
- Joined: Wed Mar 05, 2003 4:19 pm
- Location: Lima - Peru. Sudamerica
- Contact:
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???
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???
Saludos,
Miguel Seclén
Lima - Peru
Miguel Seclén
Lima - Peru
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 133
- Joined: Wed Mar 05, 2003 4:19 pm
- Location: Lima - Peru. Sudamerica
- Contact:
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
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
Saludos,
Miguel Seclén
Lima - Peru
Miguel Seclén
Lima - Peru