I have a wierd scenario here. I've got 2 DBs (9i and 10g), the 10g one being created as a clone of the 9i one. Now there's a table in each of these dbs which has only one constraint (i.e., datetime).
select * from schema.table
, I get all the records apart from 9 SEP 2012. So there records for 8 SEP 2012 and 10 SEP 2012 but not 9 SEP 2012.
Records are inserted into this table by a loader application which runs every 6 hrs automatically and inserts 2 records for each day.
I ran the SELECT part of the SQL statement configured in the application for the table in question and it returns data for 9 SEP 2012 but when I run the full SQL including the INSERT part of the SQL statement, I get:
ORA-00001: unique constraint violated.
This is confusing me as the table does not contain any entries for 9 SEP 2012 and the only constraint defined on that table is 'DATETIME'.
Perhaps you should post CREATE TABLE statement (which includes the constraint you are talking about), as well as INSERT INTO several sample records.
Saying that there's "only one constraint (i.e. DATETIME)" means close to nothing. What is DATETIME? How can DATETIME be a constraint? I know about check constraints, foreign key constraints and such, but "datetime constraint" is unknown to me.
This still isn't helping much; "only one PK constraint" is self-understanding as there can be only one primary key constraint per table anyway. It contains a "datetime" column; OK.
You said that "loader application inserts 2 records for each day". What "datetime" values does it insert? If these two records have exactly the same timestamp, then the uniqueness is violated (obviously).
Therefore, how about CREATE TABLE and those INSERT INTO sample records I asked previously? It wouldn't harm if you posted your SQL*Plus session so that we could see what you did and how Oracle responded.