I have been trying the procedure method and trigger method .Also deleted
all indexes and constraints barring primary.
The deletion for a longer period between the dates ends up hanging the
process.
There are about 1.8 lac records in the detail table.
Any other trick to try out. My customer is breathing on my neck.
regards
> From: "Nataraj S" <nataraj@...>
> >
> > CREATE PROCEDURE REMOVAL
> > AS
> > declare variable bill_id INTEGER;
> >
> > begin
> >
> > for select bill_id from drug_bill where bill_date between '01-JAN-2001'
> > and '01-FEB-2001' into :bill_id do
> > begin
> > delete from drug_bill_item where bill_id=:bill_id;
> > delete from drug_bill where bill_id=:bill_id;
> > end
> > end
> >
> > How do I send the bill_id in drug_bill as a parameter to the procedure ?
> Does :bill_id automatically reference the data in the drug_bill
> > table?
>
> The bill_id variable is filled in for each iteration of the drug_bill table
> that matches the criteria of (bill_date between ... and ...) from the
> drug_bill table. That id is then used to delete the corresponding records
> from the tables inside the for loop.
>
> A procedure just to delete a single bill_id would look something like this:
>
> CREATE PROCEDURE REMOVALID(bill_id Int)
> AS
> begin
> delete from drug_bill_item where bill_id=:bill_id;
> delete from drug_bill where bill_id=:bill_id;
> end;
>
> HTH
> Woody