OraFAQ Forum: Forms » how to insert duplicate values into another table.

Home » Developer & Programmer » Forms » how to insert duplicate values into another table.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to insert duplicate values into another table. [message #381563]

Sun, 18 January 2009 07:03

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.

Report message to a moderator

Re: how to insert duplicate values into another table. [message #381565 is a reply to message #381563]

Sun, 18 January 2009 07:29

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

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia

Senior Member
Account Moderator

Use an 'exception' handler and in it write to the second table.

David

Report message to a moderator

Re: how to insert duplicate values into another table. [message #381654 is a reply to message #381563]

Mon, 19 January 2009 02:34

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;

Report message to a moderator

Re: how to insert duplicate values into another table. [message #381958 is a reply to message #381563]

Tue, 20 January 2009 07:42

cookiemonster
Messages: 13975
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

lancer26
Messages: 52
Registered: May 2006
Location: Pakistan

Member

thanks to all. my problem is solved.

Report message to a moderator

Re: how to insert duplicate values into another table. [message #382226 is a reply to message #381563]

Wed, 21 January 2009 07:09

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.

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 14 17:27:09 CDT 2026