Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> duplicate rows script

duplicate rows script

From: Bunyamin K.Karadeniz <bunyamink_at_havelsan.com.tr>
Date: Fri, 26 Jan 2001 09:46:35 +0200
Message-Id: <10753.127567@fatcity.com>


  I want to share a script written by one my friends with you. That deletes all the duplicate rows under all tables of a user. Leaves only one of the duplicate rows.

                The rows must be mirrors of each other( all fields same) to
be deleted.

SOME OF YOU MAY use this script .

                            Bunyamin K. Karadeniz


> DECLARE
> cursor_name INTEGER;
>
> CURSOR tab_cur IS SELECT table_name
> FROM tabs;
>
> CURSOR col_cur(row_tname VARCHAR2) IS SELECT column_name
> FROM user_tab_columns
> WHERE TABLE_NAME = row_tname;
>
> tab_row tab_cur%ROWTYPE;
> col_row col_cur%ROWTYPE;
> col_names VARCHAR2(600);
> lc_sql VARCHAR2(600);
> rows_processed INTEGER;
>
> BEGIN
> FOR tab_row IN tab_cur LOOP
> BEGIN
> lc_sql := NULL;
> col_names := NULL;
> FOR col_row IN col_cur(tab_row.table_name) LOOP
> col_names := col_row.column_name || ',' || col_names;
> END LOOP;
> col_names := RTRIM(col_names, ',');
> cursor_name := dbms_sql.open_cursor;
> lc_sql := 'DELETE FROM ' || tab_row.table_name || ' WHERE rowid NOT
IN (SELECT s FROM (SELECT ' ||
> col_names ||', MAX(rowid) s FROM ' || tab_row.table_name || ' GROUP BY '
|| col_names || '))';
> dbms_sql.parse(cursor_name, lc_sql, dbms_sql.native);
> rows_processed := dbms_sql.execute(cursor_name);
> dbms_sql.close_cursor(cursor_name);
>
> EXCEPTION WHEN OTHERS THEN
> dbms_sql.close_cursor(cursor_name);
> -- dbms_output.put_line(lc_sql);
Received on Fri Jan 26 2001 - 01:46:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US