Re: Complex Structures within PL/SQL

On Apr 23, 9:31 am, Le Tubs <dlaw..._at_yahoo.co.uk> wrote:
> Hi
>
> Apologies if this gets posted twice
>
> I am relatively new to PL/SQL and want to do is find a list of
> duplicate records, then recording the duplicate c_rec id's in some
> sort of structure which I can extract at a later date
> this is what I have got so far ....
>
> TYPE DupID as Table of (
>
>         wID NUMBER;
>         AID NUMBER;
>         BID Number;
> )
>
> select ???? = A.PAYROLL_ID, A.C_REC, B.C_REC from FMAPPING A where
>                 (SELECT * FROM FMAPPING B where
>                         A.PAYROLL_ID = B.PAYROLL_ID AND A.C_REC <> B.C_REC);
>
> -- at this point can just extract each row, and I will have access to
> the payroll_id, and the unique c_rec
>
> So is this correct, but how do I load the DupID table with the
> contents of the select statment (I am assuming that the sql is
> correct)
>
> I would ideally like to learn what I'm doing right and wrong, so
> ideally what I'm after are pointers to reference sites, decent example
> sites or books rather than just the answer. Any tips or pointers ectra
> will be greatly appreciated.
>
> Thanking you in advance for your time and consideration.
>
> LeTubs

Welcome to the group! I've put together a mini-faq to help you here: http://www.dbaoracle.net/readme-cdos.htm

In general, if there is something you can do with SQL rather than PL, you should do it that way.

There are a number of sites where this dup question is a faq, such as http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15258974323143 and http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_eliminate_duplicates_rows_from_a_table.3F

Google Steven Feuerstein for lots of good PL tips, if that is your primary objective.

Of course, the best place to start is with the Oracle concepts manual, then look at the application and PL/SQL users guides. See http://tahiti.oracle.com .

jg

--
@home.com is bogus.
Why you want to wait for "new features" to age:
http://www.freelists.org/archives/oracle-l/04-2008/msg00633.html