[Oracle-l] PL/SQL Question:Eliminate duplicate rows

[Oracle-l] PL/SQL Question:Eliminate duplicate rows

	Johan Muller
	Sep 19, 2003 at 7:04 pm

Looking for an a sample cursor routine to load a PK enabled table to eliminate
any dupes from the load table.

Do you Yahoo!?

Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johan Muller
INET: bad_dba_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Search Discussions

8 responses
Oldest
Nested

Mercadante, Thomas F

at Sep 19, 2003 at 7:54 pm

⇧

Johann,

how about the following. what this does is, using the inner begin/end
block, catches when an insert would fail because of the PK failure and
ignores the error.

This is very quick and dirty - it will work fine if you are not working with
a huge amount of data.

declare

cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
exception

when too_many_rows then
null;

end;
end loop;
end;
/

If you are talking about lots and lots of data, you could easily query the
table you are inserting into, testing for the existence of the value you are
attempting to insert. If you find it, skip the insert. Like this:

declare
rec_count number;
cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
select count(*)

into rec_count
from new_table
where col1 = c1_rec.col1; -- this assumes that col1 is the pk!
if rec_count = 0 then
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
end if;
exception
when too_many_rows then
null;

end;
end loop;
end;
/

Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, September 19, 2003 3:05 PM
To: Multiple recipients of list ORACLE-L

Looking for an a sample cursor routine to load a PK enabled table to
eliminate
any dupes from the load table.

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johan Muller
INET: bad_dba_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Ron Thomas

at Sep 19, 2003 at 8:09 pm

⇧

except your too_many_rows exception should be dup_val_on_index...

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
Each new user of a new system uncovers a new class of bugs. -- Kernighan

NDATFM_at_labor.stat
e.ny.us To: ORACLE-L_at_fatcity.com
Sent by: cc:
ml-errors_at_fatcity Subject: RE: PL/SQL Question:Eliminate duplicate rows
.com

09/19/2003 01:54
PM
Please respond to
ORACLE-L

Johann,

how about the following. what this does is, using the inner begin/end
block, catches when an insert would fail because of the PK failure and
ignores the error.

This is very quick and dirty - it will work fine if you are not working with
a huge amount of data.

declare

cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
exception

when too_many_rows then
null;

end;
end loop;
end;
/

If you are talking about lots and lots of data, you could easily query the
table you are inserting into, testing for the existence of the value you are
attempting to insert. If you find it, skip the insert. Like this:

declare
rec_count number;
cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
select count(*)

into rec_count
from new_table
where col1 = c1_rec.col1; -- this assumes that col1 is the pk!
if rec_count = 0 then
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
end if;
exception
when too_many_rows then
null;

end;
end loop;
end;
/

Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, September 19, 2003 3:05 PM
To: Multiple recipients of list ORACLE-L

Looking for an a sample cursor routine to load a PK enabled table to
eliminate
any dupes from the load table.

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johan Muller
INET: bad_dba_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Thomas
INET: rthomas_at_hypercom.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Mladen Gogala

at Sep 19, 2003 at 8:09 pm

⇧

Easy way:
delete from table where rowid not in (select max(rowid) from table group by
PK);
Complicated way:
Alter table mytab enable constraint PK exceptions into exceptions;

Then, you should see how many rows are duplicated and use the method 1
on that set of rowids. If the table in question is a multi-gigabyte table
and the number of rows is relatively small, then the second method is
much, much faster.

--
Mladen Gogala
Oracle DBA

-----Original Message-----
From: ml-errors_at_fatcity.com On
Behalf Of Johan Muller
Sent: Friday, September 19, 2003 3:05 PM
To: Multiple recipients of list ORACLE-L
Subject: PL/SQL Question:Eliminate duplicate rows

Looking for an a sample cursor routine to load a PK enabled
table to eliminate any dupes from the load table.

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design
software http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johan Muller
INET: bad_dba_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
and in the message BODY, include a line containing: UNSUB
ORACLE-L (or the name of mailing list you want to be removed
from). You may also send the HELP command for other
information (like subscribing).

Note:
This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: mladen_at_wangtrading.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Mercadante, Thomas F

at Sep 19, 2003 at 8:29 pm

⇧

-----Original Message-----
Sent: Friday, September 19, 2003 4:10 PM
To: Multiple recipients of list ORACLE-L

except your too_many_rows exception should be dup_val_on_index...

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
Each new user of a new system uncovers a new class of bugs. -- Kernighan

NDATFM_at_labor.stat

e.ny.us To:

ORACLE-L_at_fatcity.com

Sent by: cc:

ml-errors_at_fatcity Subject: RE: PL/SQL

Question:Eliminate duplicate rows

.com

09/19/2003 01:54

PM

Please respond to

ORACLE-L

Johann,

how about the following. what this does is, using the inner begin/end
block, catches when an insert would fail because of the PK failure and
ignores the error.

This is very quick and dirty - it will work fine if you are not working with
a huge amount of data.

declare

cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
exception

when too_many_rows then
null;

end;
end loop;
end;
/

If you are talking about lots and lots of data, you could easily query the
table you are inserting into, testing for the existence of the value you are
attempting to insert. If you find it, skip the insert. Like this:

declare
rec_count number;
cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
select count(*)

into rec_count
from new_table
where col1 = c1_rec.col1; -- this assumes that col1 is the pk!
if rec_count = 0 then
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
end if;
exception
when too_many_rows then
null;

end;
end loop;
end;
/

Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, September 19, 2003 3:05 PM
To: Multiple recipients of list ORACLE-L

Looking for an a sample cursor routine to load a PK enabled table to
eliminate
any dupes from the load table.

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johan Muller
INET: bad_dba_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Thomas
INET: rthomas_at_hypercom.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Igor Neyman

at Sep 19, 2003 at 8:49 pm

⇧

Check SQL Reference for "exception_clause" when creating Primary Key.
Could help to do what you need just using SQL (no PL/SQL).

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Mercadante, Thomas F
Sent: Friday, September 19, 2003 2:55 PM
To: Multiple recipients of list ORACLE-L

Johann,

how about the following. what this does is, using the inner begin/end
block, catches when an insert would fail because of the PK failure and
ignores the error.

This is very quick and dirty - it will work fine if you are not working
with
a huge amount of data.

declare

cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
exception

when too_many_rows then
null;

end;
end loop;
end;
/

If you are talking about lots and lots of data, you could easily query
the
table you are inserting into, testing for the existence of the value you
are
attempting to insert. If you find it, skip the insert. Like this:

declare
rec_count number;
cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
select count(*)

into rec_count
from new_table
where col1 = c1_rec.col1; -- this assumes that col1 is the pk!
if rec_count = 0 then
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
end if;
exception
when too_many_rows then
null;

end;
end loop;
end;
/

Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, September 19, 2003 3:05 PM
To: Multiple recipients of list ORACLE-L

Looking for an a sample cursor routine to load a PK enabled table to
eliminate
any dupes from the load table.

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johan Muller
INET: bad_dba_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: ineyman_at_perceptron.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Ron Thomas

at Sep 19, 2003 at 9:19 pm

⇧

Hi Tom-

Submitting to a listserve is like living in a small town. Make 1 little oops and everybody knows
about it.

Do I need to look over my shoulder on the way to my car tonight???;)

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
Each new user of a new system uncovers a new class of bugs. -- Kernighan

NDATFM_at_labor.stat
e.ny.us To: ORACLE-L_at_fatcity.com
Sent by: cc:
ml-errors_at_fatcity Subject: RE: PL/SQL Question:Eliminate duplicate rows
.com

09/19/2003 02:29
PM
Please respond to
ORACLE-L

You know, I never use that exception, so I can't remember it correctly.

You are correct, of course - thanks for embarrasing me in front of thousands
and thousands and thousands (how many Jared??) of people! :)

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, September 19, 2003 4:10 PM
To: Multiple recipients of list ORACLE-L

except your too_many_rows exception should be dup_val_on_index...

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
Each new user of a new system uncovers a new class of bugs. -- Kernighan

NDATFM_at_labor.stat

e.ny.us To:

ORACLE-L_at_fatcity.com

Sent by: cc:

ml-errors_at_fatcity Subject: RE: PL/SQL

Question:Eliminate duplicate rows

.com

09/19/2003 01:54

PM

Please respond to

ORACLE-L

Johann,

how about the following. what this does is, using the inner begin/end
block, catches when an insert would fail because of the PK failure and
ignores the error.

This is very quick and dirty - it will work fine if you are not working with
a huge amount of data.

declare

cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
exception

when too_many_rows then
null;

end;
end loop;
end;
/

If you are talking about lots and lots of data, you could easily query the
table you are inserting into, testing for the existence of the value you are
attempting to insert. If you find it, skip the insert. Like this:

declare
rec_count number;
cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
select count(*)

into rec_count
from new_table
where col1 = c1_rec.col1; -- this assumes that col1 is the pk!
if rec_count = 0 then
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
end if;
exception
when too_many_rows then
null;

end;
end loop;
end;
/

Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, September 19, 2003 3:05 PM
To: Multiple recipients of list ORACLE-L

Looking for an a sample cursor routine to load a PK enabled table to
eliminate
any dupes from the load table.

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johan Muller
INET: bad_dba_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Thomas
INET: rthomas_at_hypercom.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Thomas
INET: rthomas_at_hypercom.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Mercadante, Thomas F

at Sep 22, 2003 at 1:19 pm

⇧

Ron,

Doesn't matter. On the way to your car, or the gym, or to watch your kids
play ball - my Uncle Guido can find you anywhere you go!! :)

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, September 19, 2003 5:20 PM
To: Multiple recipients of list ORACLE-L

Hi Tom-

Submitting to a listserve is like living in a small town. Make 1 little
oops and everybody knows
about it.

Do I need to look over my shoulder on the way to my car tonight???;)

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
Each new user of a new system uncovers a new class of bugs. -- Kernighan

NDATFM_at_labor.stat

e.ny.us To:

ORACLE-L_at_fatcity.com

Sent by: cc:

ml-errors_at_fatcity Subject: RE: PL/SQL

Question:Eliminate duplicate rows

.com

09/19/2003 02:29

PM

Please respond to

ORACLE-L

You know, I never use that exception, so I can't remember it correctly.

You are correct, of course - thanks for embarrasing me in front of thousands
and thousands and thousands (how many Jared??) of people! :)

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, September 19, 2003 4:10 PM
To: Multiple recipients of list ORACLE-L

except your too_many_rows exception should be dup_val_on_index...

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
Each new user of a new system uncovers a new class of bugs. -- Kernighan

NDATFM_at_labor.stat

e.ny.us To:

ORACLE-L_at_fatcity.com

Sent by: cc:

ml-errors_at_fatcity Subject: RE: PL/SQL

Question:Eliminate duplicate rows

.com

09/19/2003 01:54

PM

Please respond to

ORACLE-L

Johann,

how about the following. what this does is, using the inner begin/end
block, catches when an insert would fail because of the PK failure and
ignores the error.

This is very quick and dirty - it will work fine if you are not working with
a huge amount of data.

declare

cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
exception

when too_many_rows then
null;

end;
end loop;
end;
/

If you are talking about lots and lots of data, you could easily query the
table you are inserting into, testing for the existence of the value you are
attempting to insert. If you find it, skip the insert. Like this:

declare
rec_count number;
cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
select count(*)

into rec_count
from new_table
where col1 = c1_rec.col1; -- this assumes that col1 is the pk!
if rec_count = 0 then
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
end if;
exception
when too_many_rows then
null;

end;
end loop;
end;
/

Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, September 19, 2003 3:05 PM
To: Multiple recipients of list ORACLE-L

Looking for an a sample cursor routine to load a PK enabled table to
eliminate
any dupes from the load table.

Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johan Muller
INET: bad_dba_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Thomas
INET: rthomas_at_hypercom.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Thomas
INET: rthomas_at_hypercom.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message

to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Johan Muller

at Sep 22, 2003 at 4:04 pm

⇧

Job well done, Tom, the embarrasment is of no consequence. The routine was
modified to include Ron's pointer.

Stats for Tom's first routine below: load table 1.3 million rows, results table
(deduped rows) 48,307. Completion time (via sqlplus over network) 63 seconds.

Second routine (same tables) : 21 seconds!

(Oracle 8.1.7 on AIX 4.3 IBM M80 (6 way Risc 6000 4 Gig Ram 1.2 TB IBM Shark
attached array)

"Mercadante, Thomas F" wrote:

You know, I never use that exception, so I can't remember it correctly.

You are correct, of course - thanks for embarrasing me in front of thousands
and thousands and thousands (how many Jared??) of people! :)

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, September 19, 2003 4:10 PM
To: Multiple recipients of list ORACLE-L

except your too_many_rows exception should be dup_val_on_index...

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com
Each new user of a new system uncovers a new class of bugs. -- Kernighan

NDATFM_at_labor.stat

e.ny.us To:
ORACLE-L_at_fatcity.com

Sent by: cc:

ml-errors_at_fatcity Subject: RE: PL/SQL
Question:Eliminate duplicate rows

.com

09/19/2003 01:54

PM

Please respond to

ORACLE-L

Johann,

how about the following. what this does is, using the inner begin/end
block, catches when an insert would fail because of the PK failure and
ignores the error.

This is very quick and dirty - it will work fine if you are not working with
a huge amount of data.

declare

cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
exception
when too_many_rows then
null;
end;
end loop;
end;
/

If you are talking about lots and lots of data, you could easily query the
table you are inserting into, testing for the existence of the value you are
attempting to insert. If you find it, skip the insert. Like this:

declare
rec_count number;
cursor c1 is
select col1, col2
from some_table;
begin
for c1_rec in c1 loop
begin
select count(*)
into rec_count
from new_table
where col1 = c1_rec.col1; -- this assumes that col1 is the pk!
if rec_count = 0 then
insert into new_table(col1, col2)
values (c1_rec.col1, c1_rec.col2);
end if;
exception
when too_many_rows then
null;
end;
end loop;
end;
/

Good Luck!

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Friday, September 19, 2003 3:05 PM
To: Multiple recipients of list ORACLE-L

Looking for an a sample cursor routine to load a PK enabled table to
eliminate
any dupes from the load table.

__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johan Muller
INET: bad_dba_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Ron Thomas
INET: rthomas_at_hypercom.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

Do you Yahoo!?

Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Johan Muller
INET: bad_dba_at_yahoo.com

Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).

reply | permalink

Related Discussions

Discussion Navigation

view	thread \| post

Discussion Overview

group	oracle-l @
categories	oracle
posted	Sep 19, '03 at 7:04p
active	Sep 22, '03 at 4:04p
posts	9
users	5
website	oracle.com

responses	expanded	Hotkey: s
font	variable	Hotkey: f
user style	avatars	Hotkey: a

[Oracle-l] PL/SQL Question:Eliminate duplicate rows

Search Discussions

Related Discussions

5 users in discussion