i write the sql in the oci stage
SELECT to_date('#etldate#','yyyy/mm/dd') AS etldate
FROM T_WORKDATE T_WORKDATE
Etldate is defined in the parameters of the Job Properties
when i view data , the error is DSBrowse..ORAOCI9_0:OCI DATE conversion failed.
how can i change a string to the date type in the oci stage?
oracle date problem
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Is the job parameter in YYYY/MM/DD format?
Is the parameter type Date? In this case, beware that DataStage may convert the date to internal format. Check by outputting the value into a text file. If it does, you'll need an Oconv() function in your derivation.
The Oracle TO_DATE function is perfectly OK, provided what you feed it exactly matches the date picture in the second argument.
Is the parameter type Date? In this case, beware that DataStage may convert the date to internal format. Check by outputting the value into a text file. If it does, you'll need an Oconv() function in your derivation.
The Oracle TO_DATE function is perfectly OK, provided what you feed it exactly matches the date picture in the second argument.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
To expand on what Craig said, "YYYY-MM-DD" is not the same as the "YYYY/MM/DD" you specified in your date picture. Change the date picture argument of TO_DATE such that it uses "-" as the delimiter character.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
i change the parameters type to the date,the problem is solved,
but when i insert the oracle table , it
ntitled1..Transformer_2: The value of the row is: etldate = 31-十二月(or DEc)-2004
Untitled1..Transformer_2: ORA-01843: not a valid month
Untitled1..Transformer_2: DBMS.CODE=ORA-01843
how can i do, change datastage or oracle?
but when i insert the oracle table , it
ntitled1..Transformer_2: The value of the row is: etldate = 31-十二月(or DEc)-2004
Untitled1..Transformer_2: ORA-01843: not a valid month
Untitled1..Transformer_2: DBMS.CODE=ORA-01843
how can i do, change datastage or oracle?
How is the field defined in the Oracle table? How is it defined in the DataStage job? What stage are you using to access Oracle? If applicable, are you using Custom or Generated SQL to insert into Oracle? These can all affect the answer to your question.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Assuming that you are using one of the OCI stages to write to your target, you need to know that the OCI stages need Date or Timestamp data in a specific format. You can read the Oracle OCI Technical Bulletin that ships with the product for the gory details, but dates should be in this format:
For a Timestamp field in DataStage, it is expecting the format to be:
Code: Select all
YYYY-MM-DD
Code: Select all
YYYY-MM-DD HH24:MI:SS
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers