procedure sample ( a_in IN varchar2)
    IS
    v_row number;
    v1_row number;
    v2_row number;
    cursor c1 IS
    select a_value, b_value.., from source_table<where condition>;
    /* cursor c1 selecting 46 millions record, but inserted few records to the below two destinations tables based on conditions, 
    source_table is a force view*/ 
    Begin
    for i in c1
    loop
        v_row := 0;
    
        select count(1) into v_row from table_item where item = i.a_value||'_'||a_in;
        if v_row > 0 then
        
           select count(1) into v1_row from destination_table1
           where item1 = (i.b_value||'_'||a_in);
           if v1_row = 0 then
              insert into destination_table1
              (a_value, b_value)
              values(i.a_value, i.b_value);
              commit;
           end if;
           if i.b_value is not null then
              v2_row := 0;
              select count(1) into v2_row from destination_table2 where item2 = (i.a_value ||'_'||a_in) and 
              item3 = (i.b_value||'_'||a_in);
           
              if v2_row = 0 then
                 insert into destination_table2 (item2, item3) values (i.a_value ||'_'||a_in, 
                 i.b_value||'_'||a_in);
                 commit;
              end if;
           end if;
       end if;
     end loop;
    End sample;
    /* this procedure is taking approx. 20 mins to complete */