Home » SQL & PL/SQL » SQL & PL/SQL » How to solve Insertion Problem while using sequence.?
How to solve Insertion Problem while using sequence.? [message #213058] Tue, 09 January 2007 04:45 Go to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Friends,

I am having a insert_procedure that inserts values into table which uses sequence to do the same.
I have coded that, and it is working fine, Everytime it inserts the record into table by generating new sequence no.

when i write other call_procedure to call insert_procedure 'n' times, call_procedure is throwing error, as given below..

Error -1: ORA-00001: unique constraint (MPSS.Q...IDX) violated

How to solve this problem?

Here goes the code..

insert_procedure
PROCEDURE q
...   
    END q;



call_procedure

PROCEDURE O...
...
END o...;

[Updated on: Sat, 02 June 2007 03:57] by Moderator

Report message to a moderator

Re: How to solve Insertion Problem while using sequence.? [message #213064 is a reply to message #213058] Tue, 09 January 2007 05:06 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What does constraint "Q...IDX" do? It is unique, but on which columns? Perhaps it doesn't have anything to do with a sequence, but combination of other columns you are inserting "n" times using the "call_procedure"?

[Updated on: Fri, 01 June 2007 18:41] by Moderator

Report message to a moderator

Re: How to solve Insertion Problem while using sequence.? [message #213075 is a reply to message #213064] Tue, 09 January 2007 06:21 Go to previous messageGo to next message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Hi,

Thanks for your reply.
Its a index for (next_attempt) date field in the table.

Actualy i am getting problem with that index only.

Here goes my table structure - q...

NAME Null? Type
------------------------------- --------- -----
...
NEXT_ATTEMPT DATE
...

INDEX

CREATE UNIQUE INDEX q...idx ON q...
(
next_attempt ASC
)
...
/

[Updated on: Sat, 02 June 2007 03:58] by Moderator

Report message to a moderator

Re: How to solve Insertion Problem while using sequence.? [message #213080 is a reply to message #213075] Tue, 09 January 2007 07:08 Go to previous messageGo to next message
Littlefoot
Messages: 20895
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Just like I thought: it has nothing to do with a sequence.

In CALL_PROCEDURE, v_next_attempt = NULL.

In INSERT_PROCEDURE, you are inserting SYSDATE into the 'next_attempt' column because v_next_attempt IS NULL.

Now, as SYSDATE contains date AND time, manual inserting did not cause an error because you didn't type that fast so that both records were inserted at the same second. However, FOR loop does this much faster and I believe this is the reason for an error to appear - two records are to be inserted at the same moment.

What to do? Making a loop slower isn't a solution. It seems that you'll have to add another column to the unique index (a sequence could be a good choice), or - if it isn't important - recreate the index so that it isn't unique any more.

[EDIT]

On a second thought, is there a database trigger which truncates value entered into the 'next_attempt' column? If so, it might be a reason for duplicate index values (as TRUNC removes TIME component from a date value).

[Updated on: Tue, 09 January 2007 10:44]

Report message to a moderator

icon14.gif  Re: How to solve Insertion Problem while using sequence.? [message #213238 is a reply to message #213080] Tue, 09 January 2007 23:50 Go to previous message
fortunethiyagu
Messages: 94
Registered: December 2006
Member

Thanks for ur reply yaar...

As u said, i have created new Index without unique constratint.
Now its working fine...

Thank you once again..
Previous Topic: How to pass object as parameter to procedure while calling from trigger
Next Topic: Correcting the year in a timestamp field
Goto Forum:
  


Current Time: Mon Dec 05 23:49:27 CST 2016

Total time taken to generate the page: 0.19825 seconds