OraFAQ Forum: Forms » how to scan duplicate records with out saving in to database

Home » Developer & Programmer » Forms » how to scan duplicate records with out saving in to database (form61)

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

how to scan duplicate records with out saving in to database [message #307390]

Tue, 18 March 2008 14:21

suresh.naga
Messages: 1
Registered: March 2008

Junior Member

hi

how to scan duplicate records been entered by user in the form and raise error before saving in to database.

please help me out it is P1 issue i need to clear ASAP.

Report message to a moderator

Re: how to scan duplicate records with out saving in to database [message #307401 is a reply to message #307390]

Tue, 18 March 2008 16:23

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Welcome to the OraFAQ Forum!

Perhaps you didn't notice, but we have written the OraFAQ Forum Guide. Among other instructions, you'll find the "How to get a quick answer to your question?" section.

You may have guessed that you aren't the very first person who would like to perform such a validation. Guess what? You were right! So - use Forum's Search engine and search for the answer yourself. It has been asked and answered in the past.

Shortly: use the WHEN-VALIDATE-RECORD trigger and test whether such an item combination has been used before. If it was, raise an exception.

Also, words as "ASAP", "urgent" and similar tend to cause temporary blindness and some people are simply unable to answer such questions. Note that this is just a forum; noone sits here and waits for new problems to be solved urgently. If you are in a hurry, hire a consultant.

Report message to a moderator

Re: how to scan duplicate records with out saving in to database [message #307487 is a reply to message #307401]

Wed, 19 March 2008 00:31

thomasscaria
Messages: 21
Registered: July 2007
Location: Kuwait

Junior Member

use this

PROCEDURE CHECK_DUPLICATE_REC(B_NAME IN CHAR, F_NAME1 IN CHAR) IS
BEGIN
DECLARE
NO_OF_RECS NUMBER;
FLD_VAL1 VARCHAR2(80);
TMP_VAL1 VARCHAR2(80);
CUR_BLK VARCHAR2(30);
CUR_FLD VARCHAR2(30);
NO_OPT VARCHAR2(1) := 'N';
DUPLICATE EXCEPTION;
BEGIN
CUR_BLK := NAME_IN('SYSTEM.CURRENT_BLOCK');
CUR_FLD := NAME_IN('SYSTEM.CURSOR_FIELD');
IF B_NAME = CUR_BLK THEN
LAST_RECORD;
ELSE
GO_BLOCK(B_NAME);
LAST_RECORD;
END IF;
NO_OF_RECS := NAME_IN('SYSTEM.CURSOR_RECORD');
<< OUTER >>
FOR X IN 1..NO_OF_RECS LOOP
GO_RECORD(X);
TMP_VAL1 := NULL;
TMP_VAL2 := NULL;
TMP_VAL3 := NULL;
TMP_VAL4 := NULL;
IF NAME_IN('SYSTEM.LAST_RECORD') <> 'TRUE' THEN
FLD_VAL1 := NAME_IN(F_NAME1);
<< INNER >>
FOR Y IN X+1..NO_OF_RECS LOOP
GO_RECORD(Y);
TMP_VAL1 := NAME_IN(F_NAME1);
IF TMP_VAL1 = FLD_VAL1 THEN
RAISE DUPLICATE; --- FIELD1 ONLY.
END IF;
END LOOP; ---- END INNER LOOP.
END IF;
END LOOP; ---- END OUTER LOOP.
GO_BLOCK(CUR_BLK);
GO_FIELD(CUR_FLD);
EXCEPTION
WHEN DUPLICATE THEN
MESSAGE('Error: Duplicate record ! ');
RAISE FORM_TRIGGER_FAILURE;
END;
END;

[Updated on: Wed, 19 March 2008 00:34]

Report message to a moderator

Re: how to scan duplicate records with out saving in to database [message #307504 is a reply to message #307487]

Wed, 19 March 2008 00:55

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

Senior Member
Account Moderator

@Thomas,

You need to read the OraFAQ Forum Guide as well. FORMAT your code and use 'code' tags!!

@OP

Looping is not the way to go because of validation issues. Use a dynamic 'record_group' and populate it with the keys that have been used. Then test against the database to see if anyone else has used it and against the 'record group' so see if the 'key' has been used in this form. Alternatively just have a unique index on the table and wait for the database to tell you that the entry has been used previously.

Search this forum for 'record group validate' for similar threads.

David

Report message to a moderator

Previous Topic:	Image Item
Next Topic:	pop-up form

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 14 13:04:45 CST 2024