| ORA-00001 Unique Constraint [message #223056] |
Wed, 07 March 2007 07:20  |
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  |
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 |
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 |
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 |
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 #223077 is a reply to message #223056] |
Wed, 07 March 2007 08:22 |
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 #223080 is a reply to message #223056] |
Wed, 07 March 2007 08:38 |
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 #223089 is a reply to message #223056] |
Wed, 07 March 2007 08:53 |
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 |
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
