Default Value of SYSDATE in a Date field throws error 2

[Shatneriffic]

Hello

I am inserting data into a table and have a ROW_ADD_TMS column that is set in the table to default at the sysdate. When I attempt to insert data, this field throws the following error: ORA-01840: input value not long enough for date format

When I try take the default value off of the column and try to insert sysdate from the query, I get the same result.

I've done this 100's of times with other insert statements, but never got this error. Any ideas why a DATE field will not accept a SYSDATE value?

Thanks!

 

[carp]

Very odd indeed! Could you post the insert statement for us to look at?

 

[Shatneriffic]

insert into PROD.TBL_PROD_ME (MNTH,HOURS_NOHOURS,NAME,TOTAL_STFD_TIME,COLLECTOR,COMMENT_ADDED_ID,CALLS,CONTACTS,PROM_TO_PAY,KEPT,SUPERVISOR)

SELECT MNTH,
HOURS_NOHOURS,
NAME,
sum(case
when TOTAL_STFD_TIME is null then 0
else to_number(TOTAL_STFD_TIME)
end) TOTAL_STFD_TIME,
COLLECTOR,
COMMENT_ADDED_ID,
sum(to_number(CALLS)) CALLS,
sum(to_number(CONTACTS)) CONTACTS,
sum(to_number(PROM_TO_PAY)) PROM_TO_PAY,
sum(case
when KEPT is null then 0
else to_number(KEPT)
end) KEPT,
SUPERVISOR

FROM PROD.TBL_PROD
group by MNTH,
HOURS_NOHOURS,
NAME,
COLLECTOR,
COMMENT_ADDED_ID,
SUPERVISOR

Note: I'm leaving the DATE field out so it updates by default upon insert. However, I get the same error when adding ROW_ADD_TMS to my fields in the insert line and hardoding a date or using sysdate in the query.

 

[SantaMufasa]

Shatner,

When Oracle throws this error, it typically results from character involvement (via a TO_DATE/TO_CHAR function) with a DATE expression.

So, could you also post a DESCRIBE of the portion of the table that shows the DATE...DEFAULT components?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Shatneriffic]

Column Name: ROW_ADD_TMS
ID: 12
Pk:
Null?: Y
Data Type: DATE
Default: sysdate
Histogram: No

 

[Dagon]

Are you absolutely sure it's coming from this field and not from something else in the statement ? You should be able to confirm this by taking off the default value and checking that the insert now runs correctly. Check also you don't have any triggers on the table.

For Oracle-related work, contact me through Linked-In.

 

[Beilstwh]

TRY THE FOLLOWING SELECT

SELECT DATA_DEFAULT, DATA_TYPE
FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME='TBL_PROD_ME'
AND OWNER = 'PROD'
AND COLUMN_NAME='ROW_ADD_TMS'

Bill
Lead Application Developer
New York State, USA

 

[carp]

Also, are we certain that there are no other columns with a DATE datatype? Yet another slender straw to grasp at, but when faced with the inexplicable, you must check the unlikely.

 

[Shatneriffic]

There are no other columns with a DATE datatype. I remove the ROW_ADD_TMS column and the insert works.

Weird, huh?

 

[carp]

Shatneriffic, this is way beyond weird! So to be sure of where we are, you are saying the following statement will throw the error:

insert into PROD.TBL_PROD_ME (MNTH,HOURS_NOHOURS,NAME,TOTAL_STFD_TIME,
COLLECTOR,COMMENT_ADDED_ID,CALLS,CONTACTS,
PROM_TO_PAY,KEPT,SUPERVISOR,ROW_ADD_TMS)
SELECT MNTH,
             HOURS_NOHOURS,
          NAME,
          sum(case
                 when TOTAL_STFD_TIME is null then 0
               else to_number(TOTAL_STFD_TIME)
               end) TOTAL_STFD_TIME,
          COLLECTOR,
          COMMENT_ADDED_ID,
          sum(to_number(CALLS)) CALLS,
          sum(to_number(CONTACTS)) CONTACTS,
          sum(to_number(PROM_TO_PAY)) PROM_TO_PAY,
          sum(case
                     when KEPT is null then 0
                   else to_number(KEPT)
                   end) KEPT,
          SUPERVISOR,
sysdate     
   FROM PROD.TBL_PROD
   group by MNTH,
                HOURS_NOHOURS,
                NAME,
            COLLECTOR,
            COMMENT_ADDED_ID,
            SUPERVISOR,
            sysdate;

If this is really the case and there's not a problem with misaligned source/target columns, the only other possibility I can think of is if the NLS_DATE parameters are mismatched between your client setup and your database setup. This is yet another very tenuous possibility; under the hood a date's a date's a date. But the only time I've seen this error message is when I've got the date format picture messed up.

 

[Shatneriffic]

Correct. The statement you posted will throw an error.

It will also throw an error if you decide to leave the sysdate out alltogether, and ignore the input to the ROW_ADD_TMS.

 

[carp]

Wow.

The error from the DEFAULT blows the NLS guess out of the water.
Last gasp guess here - is there a trigger on the table that might be faulty? If not, then it might be time to log an SR with Oracle Support.

And now I'm out of bullets.

If you do find an answer to this somewhere else, please be sure to post it here!

 

[carp]

Nope - thought of one other possibility. Is there, perhaps, a function-based index on the DATE column?

 

[Shatneriffic]

Unfortunately, in this case there are no triggers. Looks like it's time for an SR.

Thanks to you and everyone for taking time to look into this for me. At least you validated that I wasn't going crazy.

 

[carp]

And no FBIs? In that case, yeah - it's time to punt.

 

[SantaMufasa]

At least you validated that I wasn't going crazy.

Actually, we haven't dealt yet with whether or not you are going crazy...you can pursue that issue that this link, amongst others. <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Thargy]

Gents,

in order to rule out environment completely, can you post the create table statement for the entire table, and an insert statement that is failing.

I would like to try it in an empty schema where I can guarantee no unknown actions are occurring.

Regards

T