Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: Cannot insert data into a table

    Hi

    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).

    When I
    Code:
    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:

    Code:
    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'.

    Any ideas?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Sorry, I meant to say, only one PK constraint which is the 'datetime' column.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by shajju View Post
    This is confusing me as the table does not contain any entries for 9 SEP 2012
    Apparently there is already such a row, otherwise the error wouldn't occur.

    the only constraint defined on that table is 'DATETIME'.
    What exactly does that mean? There is no DATETIME datatype in Oracle, and there isn't such thing as a constraint "type" of that name.

    Please show us the full create table statement including all constraint definitions as SQL
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •