Home » Developer & Programmer » Forms » Unable to update record with sequence number in multiple records block.
Unable to update record with sequence number in multiple records block. [message #220437] Tue, 20 February 2007 12:48 Go to next message
michaelv
Messages: 20
Registered: November 2006
Location: rockville, MD
Junior Member
Hi,
I have the following situation and i have been trying to figure out the solution with no luck.

I have a form , for example, which has 2 fields:

Sequence Name
----------- ----------
1 A1
2 A2
3 A3
4 A4

When i try to insert the a new record from the sequenc 1 or 2 i have the error when save the form:
UPDATE <table name> SET WHERE ROWID=:1
ORA-00001: unique constraint (table name) violated

(i quoted this from buit-in function DISPLAY_ERROR)

The new record with new sequence will be generated as followed if the cursor is from sequence 1
Sequence Name
----------- ----------
1 A1
2 B1
2 A2
3 A3
4 A4


as far as i know, the form is trying to insert new record with sequence 2 and update current 2 to 3, 3 to 4 , and 4 to 5.

The sequence is a primary key, by the way.

The update is where my problem is because it sees that 3 is already existed.

If i insert new record from sequence 3 or 4, everything is ok.

Any idea or suggestion to fix this will be greatly appreciated.

thanks.

[Updated on: Tue, 20 February 2007 12:50]

Report message to a moderator

Re: Unable to update record with sequence number in multiple records block. [message #220453 is a reply to message #220437] Tue, 20 February 2007 14:12 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How are you creating sequence numbers? It seems that it is not a sequence (meaning: an Oracle database object), but some kind of your algorithm. How does it look like?

If you used a sequence (i.e. "select sequence_name.nextval into :block.item from dual"), I guess you wouldn't have such problems because sequence would take care about it.

Quote:
the form is trying to insert new record with sequence 2 and update current 2 to 3, 3 to 4 , and 4 to 5.

I'm not sure I understood what you meant. Why would Oracle update anything on its own? Do you use database trigger(s)?

In other words: I'd say that use of a sequence might solve your problem.
Re: Unable to update record with sequence number in multiple records block. [message #220473 is a reply to message #220453] Tue, 20 February 2007 15:52 Go to previous messageGo to next message
michaelv
Messages: 20
Registered: November 2006
Location: rockville, MD
Junior Member
i have my own algorithm to re-assign the sequence number by first using :system.cursor_record to remember position of the new record. Then go through the rest of record .

When commit, the data should look like this (i appologize for not giving correct information in the example)

The new record with new sequence will be generated as followed if the cursor is from sequence 1
Sequence Name
----------- ----------
1 A1
2 B1
3 A2
4 A3
5 A4

thanks.
Re: Unable to update record with sequence number in multiple records block. [message #224928 is a reply to message #220473] Thu, 15 March 2007 23:40 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you solved your problem?

David
Previous Topic: excel to form 10g
Next Topic: Error occured while execution of program- pls help...
Goto Forum:
  


Current Time: Fri Dec 02 12:45:14 CST 2016

Total time taken to generate the page: 0.08452 seconds