Home » Applications » Oracle Fusion Apps & E-Business Suite » ORA-00001 Unique Constraint
ORA-00001 Unique Constraint [message #223056] Wed, 07 March 2007 07:20 Go to next message
asha123
Messages: 23
Registered: November 2006
Location: Jordan
Junior Member
Dear all:

I'm trying to migrate data to OM--> Price list window
using my own package, I have successed in this but only when I enter one item to the list(i.e one line to qp_list_lines table)

I know that the "list_line_no" should be unique while the "list_header_id" should be repeated for the same price list(master and detail)

and Iam using function to select the max(list_line_no) +1 each time
so it will generate unique no.

list_line_no // List_header_id
12015 17028
12016 17028
12017 17028

but I am facing the the error:
Error ORA-00001 Unique Constraint (QP.QP_LIST_LINES_U1) violated

is it bug or what? my environment is 11.5.10!

Many thanks in advance

Regards

Asha

[Updated on: Wed, 07 March 2007 07:33]

Report message to a moderator

Re: ORA-00001 Unique Constraint [message #223058 is a reply to message #223056] Wed, 07 March 2007 07:23 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
why not use a sequence than using max(some_column) because there are certain transaction level commit which has not yet happened and you end up getting wrong max number.


Regards
Prashant
Re: ORA-00001 Unique Constraint [message #223063 is a reply to message #223058] Wed, 07 March 2007 07:38 Go to previous messageGo to next message
asha123
Messages: 23
Registered: November 2006
Location: Jordan
Junior Member
Dear Prashant:

I am sure about the max no. , also when I do select statement for the rejected numbers it returns null, so its not exists there at the DB

I will use sequence as you told me but I dont think that will resolve the error because I tried to modify it with other much larg values but it still giving me the same error!!!

Regards,

Asha
Re: ORA-00001 Unique Constraint [message #223066 is a reply to message #223063] Wed, 07 March 2007 07:48 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Just see all the triggers but all this is very risky as this is base table.

you can disable them make change enable back again but still it is risky try it on your dev box.

Regards
Prashant
Re: ORA-00001 Unique Constraint [message #223075 is a reply to message #223056] Wed, 07 March 2007 08:07 Go to previous messageGo to next message
asha123
Messages: 23
Registered: November 2006
Location: Jordan
Junior Member
Dear Prashant:

Thank you for the suggestions, I tried to disable the all the triggers on that base table but as you can expect, the error is about constraint violate , so that didn't solve the problem!

I don't know can I try to drop the constraint then recreate it?

Regards
Asha
Re: ORA-00001 Unique Constraint [message #223076 is a reply to message #223075] Wed, 07 March 2007 08:14 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Try to disable constraint rather than droping it.
if you toad you can use GUI to do that.

but it gives you error where try to execute delete command on the table???
Re: ORA-00001 Unique Constraint [message #223077 is a reply to message #223056] Wed, 07 March 2007 08:22 Go to previous messageGo to next message
asha123
Messages: 23
Registered: November 2006
Location: Jordan
Junior Member
I tried to disable it:

the error says that the constraint name is :
QP.QP_LIST_LINES_U1
but no such name!!

so I disable it as follows:
select * from all_cons_columns where column_name ='LIST_LINE_ID'
alter table QP_LIST_LINES
disable constraint SYS_C0033872

but it still wont work!!!

Re: ORA-00001 Unique Constraint [message #223078 is a reply to message #223077] Wed, 07 March 2007 08:28 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
what is the operation (DML) you trying to do?
Re: ORA-00001 Unique Constraint [message #223080 is a reply to message #223056] Wed, 07 March 2007 08:38 Go to previous messageGo to next message
asha123
Messages: 23
Registered: November 2006
Location: Jordan
Junior Member
I'm migrating data using my own package directly to the base table
(sure using temp table to validate all the data before starting inserting this data to the base table using simple for loop and insert statement)..

Is that what you meant,and you want to know about!
Re: ORA-00001 Unique Constraint [message #223087 is a reply to message #223080] Wed, 07 March 2007 08:46 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
do you know which are the records throwing these exceptions?
Re: ORA-00001 Unique Constraint [message #223089 is a reply to message #223056] Wed, 07 March 2007 08:53 Go to previous messageGo to next message
asha123
Messages: 23
Registered: November 2006
Location: Jordan
Junior Member
yes its all the records!!!

when I insert only one line ( for each price list) its working fine
but when I try to insert two (for the same price list) it reject it and gives the error!!!
Re: ORA-00001 Unique Constraint [message #223258 is a reply to message #223056] Thu, 08 March 2007 03:30 Go to previous message
asha123
Messages: 23
Registered: November 2006
Location: Jordan
Junior Member
Dear All:

I know the cause of the problem so i decide to write it to be as reference for others:

first you should be loading different items ( for one price list )
so items must not be repeated

Secondly there is List_line_id (for price list lines) that should be automatically generated by the system, in my case because iam migrating data directly to the base table, I generate it using sequence, also theres List_line_no that must be generated in order
to not get the "unique error"

Thanks you Prashant for your effort.

Regards

Asha

[Updated on: Thu, 08 March 2007 03:32]

Report message to a moderator

Previous Topic: Multi Org
Next Topic: Problem with AR in NewCustomer Interface Program
Goto Forum:
  


Current Time: Tue Dec 06 16:27:52 CST 2016

Total time taken to generate the page: 0.14546 seconds