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" <NDATFM_at_labor.state.ny.us> 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).
Received on Mon Sep 22 2003 - 11:04:41 CDT
