We have an issue when we perform an insert like this
INSERT INTO STS_RESP_LOG(STS_REQ_LOG_SYSTEM_ID,HSTRY_FLG, SNGL_STS, FRQNCY, CRT_DATE )
VALUES ( ?, ?, ?, ?, sysdate);
? is the corresponding bind variable.
We also have a nullable/numeric column called msg_id that is not listed on the insert query, therefore it should be inserted with null values.
We have a few cases (about 10 cases out of 10 million records) where the record has a 0 instead of a null and we wonder if someone has encountered a glitch on Oracle when it is inserting zero's instead of nulls.
Thanks
I've never heard of such an instance where we insert a ZERO (or any other non-null value) into a column that is not specified in the SQL statement.
Things to look for:
- an erroneous triggers on the table (don't forget, triggers can be in a different schema to the table)
- erroneous DEFAULT clause
If all fails, you could explicity ADD a trigger to the table temporarily at AFTER ROW level, with something like:
if :new.msg_id is not null then
raise_application_error(-20000,'WE FOUND ONE!');
end if;
and use things like UTL_CALL_STACK and the like to capture everything to see where that particular insert was different to the others.