Error loading DATE into ORACLE

[RaviM]

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

[ArndW]

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?

[kcbland]

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.

[ketfos]

Hi,

I am sure if the word ASAP were not mentioned in your post,

you could have got well defined answers to your post from different members.

Ketfos

[ray.wurlod]

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.

[chulett]

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.