Home » Developer & Programmer » Forms » how to insert duplicate values into another table.
how to insert duplicate values into another table. [message #381563] Sun, 18 January 2009 07:03 Go to next message
lancer26
Messages: 52
Registered: May 2006
Location: Pakistan
Member

aoa!
i have created two tables a and b

create table a
( code number primary key);

create table b
( code number,
dat date);

insert into table a (code)
values(1234);
insert into table a (code)
values(1235);
commit;
select * from a ;
a
-----
1234
1235
--------------------------------
insert into table a (code)
values(1234);
commit;
ERROR at line 1:
ORA-00001: unique constraint (GMS.SYS_C0030897) violated

i need, when user inserts duplicate data it should be insert into another table b.


how can i do in forms 6i? please help. thanks in advance.
Re: how to insert duplicate values into another table. [message #381565 is a reply to message #381563] Sun, 18 January 2009 07:29 Go to previous messageGo to next message
javed.khan
Messages: 340
Registered: November 2006
Location: Banglore
Senior Member

No Message Body

[Updated on: Sun, 18 January 2009 07:34]

Report message to a moderator

Re: how to insert duplicate values into another table. [message #381627 is a reply to message #381565] Mon, 19 January 2009 00:37 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Use an 'exception' handler and in it write to the second table.

David
Re: how to insert duplicate values into another table. [message #381654 is a reply to message #381563] Mon, 19 January 2009 02:34 Go to previous messageGo to next message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
In oracle forms, create on-error trigger on the block and write following:

DECLARE
errnum NUMBER := ERROR_CODE;
errtxt VARCHAR2 (80) := ERROR_TEXT;
errtyp VARCHAR2 (3) := ERROR_TYPE;
BEGIN
IF errtyp = 'FRM' AND errnum = '40508'
THEN
IF (DBMS_ERROR_CODE = -1)
THEN
INSERT INTO duplicate_values_table
VALUES (code, SYSDATE);

COMMIT;
RAISE form_trigger_failure;
END IF;
END IF;
END;
Re: how to insert duplicate values into another table. [message #381958 is a reply to message #381563] Tue, 20 January 2009 07:42 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'd put the insert and commit inside a stored procedure with pragma autonomous_transaction declared if I was you.
Then call that from the on-error trigger.

A blanket commit in an on-error trigger might do more than you expect.

[Updated on: Tue, 20 January 2009 07:43]

Report message to a moderator

Re: how to insert duplicate values into another table. [message #382198 is a reply to message #381958] Wed, 21 January 2009 05:58 Go to previous messageGo to next message
lancer26
Messages: 52
Registered: May 2006
Location: Pakistan
Member

thanks to all. my problem is solved.
Re: how to insert duplicate values into another table. [message #382226 is a reply to message #381563] Wed, 21 January 2009 07:09 Go to previous message
parag_narkhede
Messages: 110
Registered: January 2008
Location: Pune
Senior Member
Great!!!
But when you got your problem solved, please specify which way work for you to solve the problem. So that people who will search this forum in future, will get benefit.

Previous Topic: Add_List_Element
Next Topic: HOW TO USE SQLLDR IN FORM
Goto Forum:
  


Current Time: Fri Dec 02 23:10:36 CST 2016

Total time taken to generate the page: 0.12917 seconds