Home » Developer & Programmer » Forms » how to scan duplicate records with out saving in to database (form61)
how to scan duplicate records with out saving in to database [message #307390] Tue, 18 March 2008 14:21 Go to next message
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.
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 Go to previous messageGo to next message
Littlefoot
Messages: 20888
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.

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 Go to previous messageGo to next message
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 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT 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
Previous Topic: Image Item
Next Topic: pop-up form
Goto Forum:
  


Current Time: Fri Dec 02 21:01:56 CST 2016

Total time taken to generate the page: 0.05583 seconds