Hi,
I am having problem while loading DATE value in to oracle.
Input value is : 16-NOV-05,
Query in Oracle stage : INSERT INTO TEMP (Load_Date) VALUES (:1)
ERROR: ORA-01862: the numeric value does not match the length of the format item.
please let me know the solution ASAP
Error loading DATE into ORACLE
Moderators: chulett, rschirm, roy
RaviM,
this is a volunteer forum; the terms "urgent" and "ASAP" have the effect that many of the real experts won't even bother to respond, or will wait a day or three. If you have an urgent problem then go to your support provider - you are paying them for the privilege of getting ASAP responses.
If you were to use SQLPLUS or TOAD or whatever Oracle tool you prefer and did a "INSERT INTO TEMP (Load_Date) VALUES ('16-NOV-05');" you would most likely get the same error - it doesn't like your date format.
This Oracle error 01862 is a common one; it is solved by CASTing or otherwise modifying your data to match what Oracle expects.
What is your default date format in Oracle?
this is a volunteer forum; the terms "urgent" and "ASAP" have the effect that many of the real experts won't even bother to respond, or will wait a day or three. If you have an urgent problem then go to your support provider - you are paying them for the privilege of getting ASAP responses.
If you were to use SQLPLUS or TOAD or whatever Oracle tool you prefer and did a "INSERT INTO TEMP (Load_Date) VALUES ('16-NOV-05');" you would most likely get the same error - it doesn't like your date format.
This Oracle error 01862 is a common one; it is solved by CASTing or otherwise modifying your data to match what Oracle expects.
What is your default date format in Oracle?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
In the time it took to type in your ASAP question, you could have used the Search facility and found the answer yourself.
Your lack of knowledge of Oracle date handling will be greatly reduced if you spend a few minutes researching and learning rather than posting ASAP requests when single issues come up. Since there's bound to be more discussion of other common date issues, please take some time and read up on that and forgo any more ASAP posts, as you'll already have the answers.
Your lack of knowledge of Oracle date handling will be greatly reduced if you spend a few minutes researching and learning rather than posting ASAP requests when single issues come up. Since there's bound to be more discussion of other common date issues, please take some time and read up on that and forgo any more ASAP posts, as you'll already have the answers.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The solution is to get it right.
It's possibly not in your SQL but in the other columns in the table not mentioned in the INSERT statement. One of them is numeric, and its default value (whatever that may be) is not a valid numeric value.
That's all the error message has told you.
But what may also be the case is that the format of the date you supplied does not conform to the default Oracle date picture for the instance. You may need a TO_DATE function in your INSERT statement.
It's possibly not in your SQL but in the other columns in the table not mentioned in the INSERT statement. One of them is numeric, and its default value (whatever that may be) is not a valid numeric value.
That's all the error message has told you.
But what may also be the case is that the format of the date you supplied does not conform to the default Oracle date picture for the instance. You may need a TO_DATE function in your INSERT statement.
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.
Search the forum for tons of comments on handling Oracle DATE fields in DataStage. My personal standard is to always declare them as a Timestamp data type. DataStage will always deliver them in a fixed format and expect them in that same fixed format, one that will work regardless of the NLS_DATE format of the database. The OCI stages will supply the needed TO_CHAR or TO_DATE statements to match that format. You will also have direct control over the 'time' portion of each DATE field.
After that it's just a standard set of date handling routines that all developers leverage. A consistant approach yields consistant results.
After that it's just a standard set of date handling routines that all developers leverage. A consistant approach yields consistant results.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers