Home » Developer & Programmer » Application Express & MOD_PLSQL » Check Duplicate Values (Oracle 11g,Win 7 32-bit,Apex 4.0)
Check Duplicate Values [message #614397] Wed, 21 May 2014 05:44 Go to next message
Xandot
Messages: 96
Registered: January 2014
Location: India
Member
Hello all,
I want to create a validation to the items here i have one column like "Component Name"
if the user put the name of component and if its already into the DB so i want to restrict that duplicate entry.

I am not want to create unique key because already some duplicate values are there and user don't want to delete
previous data so please tell me how can i do this.

So how can i create this validation in my form.


Thanks
Re: Check Duplicate Values [message #614404 is a reply to message #614397] Wed, 21 May 2014 06:54 Go to previous messageGo to next message
Littlefoot
Messages: 19329
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you're kind of wrong. You can add a unique constraint and leave current values intact. Here's now.

This is your current situation:
SQL> create table test (id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> insert into test values (1);

1 row created.

SQL> select * From test;

        ID
----------
         1
         1
As you can see, duplicates exist. Now, add unique constraint but DISABLE it and tell Oracle NOT to validate existing values:
SQL> alter table test add constraint uk_t_id unique (id) disable novalidate;

Table altered.

The next step is to create an index; normally, unique constraint creates (unique!) index automatically, not it didn't do that now:
SQL> select index_name from user_indexes where table_name = 'TEST';

no rows selected
Fine; let's create the index (non-unique!) ourselves:
SQL> create index i1_t_id on test (id);

Index created.

Finally, enable the constraint:
SQL> alter table test enable novalidate constraint uk_t_id;

Table altered.


Testing:
SQL> insert into test values (1);
insert into test values (1)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_T_ID) violated


SQL> insert into test values (2);

1 row created.

SQL> insert into test values (2);
insert into test values (2)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UK_T_ID) violated


SQL> select * From test;

        ID
----------
         1
         1
         2

SQL>


Nice, eh? No need for any Apex programming. Let the database do its job.
Re: Check Duplicate Values [message #614405 is a reply to message #614404] Wed, 21 May 2014 07:16 Go to previous messageGo to next message
Xandot
Messages: 96
Registered: January 2014
Location: India
Member
OK thanks Littlefoot for teaching me that...

Its working correctly ..

Can i give any message when user create a duplicate entry because now it raise error so user can't understand it ...


Re: Check Duplicate Values [message #614410 is a reply to message #614405] Wed, 21 May 2014 08:25 Go to previous messageGo to next message
BlackSwan
Messages: 22526
Registered: January 2009
Senior Member
>Can i give any message when user create a duplicate entry because now it raise error so user can't understand it ...

It will be faster & easier to train the user.
Re: Check Duplicate Values [message #614418 is a reply to message #614410] Wed, 21 May 2014 10:06 Go to previous messageGo to next message
Xandot
Messages: 96
Registered: January 2014
Location: India
Member
Absolutely right blackswan...

But is there any via to do the same ???

Re: Check Duplicate Values [message #614427 is a reply to message #614418] Wed, 21 May 2014 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What "same"?

Re: Check Duplicate Values [message #614469 is a reply to message #614427] Thu, 22 May 2014 00:52 Go to previous messageGo to next message
Littlefoot
Messages: 19329
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I didn't pay much attention to it, but Google did return some results. For example, Transform ORA-xxxx messages into something more user friendly in APEX or Oracle APEX: User friendly exceptions and logging. You'll see that these solutions aren't simple at all.

However, if you upgrade your Apex version to 4.1 (by the way, why didn't you already do it? 4.2 is the latest version), you'd be able to use improvements as described in APEX 4.1 Error Handling Improvements Part 1.

[Updated on: Thu, 22 May 2014 00:53]

Report message to a moderator

Re: Check Duplicate Values [message #614573 is a reply to message #614469] Fri, 23 May 2014 07:53 Go to previous messageGo to next message
Xandot
Messages: 96
Registered: January 2014
Location: India
Member
Ok thanks Littlefoot.......
Re: Check Duplicate Values [message #614574 is a reply to message #614427] Fri, 23 May 2014 07:53 Go to previous message
Xandot
Messages: 96
Registered: January 2014
Location: India
Member
@michel cadot :-- same is point to the ..
"Can i give any message when user create a duplicate entry because now it raise error so user can't understand it ..."
Previous Topic: Updating Data in %ROWTYPE ?
Next Topic: Limited data export in excel
Goto Forum:
  


Current Time: Tue Jul 29 20:47:00 CDT 2014

Total time taken to generate the page: 0.24140 seconds