Question:
I have 8 tables for example A,B,.....H.

Now from these tables I need to delete record based on a criteria as date (something like -delete all records from these tables created before 30th march). Now these column has constraints and has to be deleted in the order A,B.....H.

These are the common column in all the tables......Parent ID and Created_date

Can I get some advice on how it can be acheived? I can delete it individually by running a delete statment, as you all know that is fairly simple. But I have around 6000 rows so its difficult.

If you have control over your constraints, maybe try setting them up as DELETE CASCADE :

SQL> create table parent (pk number primary key);

Table created.

SQL> create table child (pk number primary key,
  2                      fk number references parent(pk) 
  3                         on delete cascade);

Table created.

SQL> create table child_of_child (pk number primary key,
  2                               fk number references child(pk)
  3                                  on delete cascade);

Table created.

SQL> insert into parent values (1);
SQL> insert into child values (10, 1);
SQL> insert into child_of_child values (100, 10);

SQL> insert into parent values (2);
SQL> insert into child values (20, 2);
SQL> insert into child_of_child values (200, 20);

SQL> insert into parent values (3);
SQL> insert into child values (30, 3);
SQL> insert into child_of_child values (300, 30);

9 rows inserted

SQL> select * from parent;

        PK
----------
         1
         2
         3

SQL> select * from child;

        PK         FK
---------- ----------
        10          1
        20          2
        30          3

SQL> select * from child_of_child;

        PK         FK
---------- ----------
       100         10
       200         20
       300         30

Then, when you delete from the parent, the children go with :

SQL> delete from parent where pk in (1,2);

2 rows deleted.

SQL> select * from parent;

        PK
----------
         3

SQL> select * from child;

        PK         FK
---------- ----------
        30          3

SQL> select * from child_of_child;

        PK         FK
---------- ----------
       300         30

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips