Re: Best approach for my problem

look up exception handling in the PL/SQL manual.

"Reddy" <gureddy_at_hotmail.com> wrote in message news:142f748f.0108160651.7ee03a88_at_posting.google.com...
> Hi guys,
>
> I need your help in solving the following problem:
>
> I have staging table with the following structure:
>
> Deal# Division# Product# Processed_flag
> ----------------------------------------------
> 100 126 121212
> 100 127 121213
> 100 126 121213
> 100 128 121215
> 101 126 121215
> 101 127 121215
> 101 126 121216
> ....
> ....
>
> I need to write to following tables as follows:
>
> Deal table division table Product table
>
> deal# Deal# div# deal# prod#
> ---- ------ ------ ----- ------
> 100 100 126 100 121212
> 101 100 127 100 121213
> 100 128 100 121215
> 101 126 101 121215
> 101 127 101 121216
>
>
> I defined 3 cursors for the processing the rows.
>
> 1. deal_cursor is select distinct deal_no from staging_table where
> processed_flag is not null;
> 2. division_cursor is select distinct division from staging_table
> where deal_no = t_deal_no;
> 3. product_cursor is select distinct product from staging_table where
> deal_no = t_deal_no;
>
> for d_rec in deal_cursor
> loop
> insert into deal table;
> t_deal_no := d_rec.deal_no;
> for div_rec in division_cursor
> loop
> insert into division table;
> end loop;
>
> for prod_rec in product_cursor
> loop
> insert into product table;
> end loop;
> end loop;
>
> The trick is I need to log an error if there is a problem with the
> data:
> eg:
>
> 101 126 121215 ------> If the product is not valid in this record,
> I
> should NOT set the processed_flag to 'Y'. So
> the
> division 126 for deal 100 also should not be
> inserted.
>
> HOW DO I DO THAT? Please help
>
> Thanks in advance
>
> -Reddy
Received on Fri Aug 17 2001 - 04:44:34 CEST