SQL & PL/SQL

begin

   CURSOR temp_rec_tap_cur
   IS
      SELECT file_id, file_name, sender_pmn, recipient_pmn, call_date,
             call_date_only, call_type, call_number, FIRST_RECORD,
             service_type, service_code, home_bid, serve_bid,
             chargeable_subs_type, imsi_min, msisdn_mdn, air_charges,
             air_charges_sdr, air_time, national_call_charges,
             national_call_charges_sdr, national_call_time,
             international_call_charges, international_call_charges_sdr,
             international_call_time, dir_assist_charges,
             dir_assist_charges_sdr, dir_assist_time, other_charges,
             other_charges_sdr, other_time, volume_charges,
             volume_charges_sdr, volume_units, tot_charges, tot_charges_sdr,
             tot_duration, state_tax, state_tax_sdr, local_tax,
             local_tax_sdr, state_and_use_tax, state_and_use_tax_sdr, va_tax,
             va_tax_sdr, other_tax, other_tax_sdr, charge_refund_indicator,
             advised_charge_currency, advised_charge, advised_charge_sdr,
             advised_charge_commission, advised_charge_commission_sdr,
             exchange_rate, mcc, mnc, process_date, chargeable_units,
             record_num, mscid
        FROM temp_records_tap;

   TYPE temp_rec_tab IS TABLE OF temp_rec_tap_cur%ROWTYPE;

   v_test_tab      temp_rec_tab;
   v_rec_num       num_tab;
   v_filename      temp_records_tap.file_name%TYPE;
   v_error_code    tap_reject.ERROR_CODE%TYPE;
   v_rej_value     tap_reject.field_rej%TYPE;
   v_count         NUMBER;
   v_error_index   NUMBER;
   v_count1 NUMBER;
   v_error_cnt NUMBER;
   l_ind number;
   
BEGIN
   SELECT file_name
     INTO v_filename
     FROM temp_records_tap
    WHERE ROWNUM < 2;

SELECT COUNT(*) into v_count1 from temp_records_tap;  


   OPEN temp_rec_tap_cur;

   LOOP
      BEGIN
         FETCH temp_rec_tap_cur
         BULK COLLECT INTO v_test_tab LIMIT v_count1;
         
        

         FORALL i IN v_test_tab.FIRST .. v_test_tab.LAST SAVE EXCEPTIONS
            INSERT INTO tapdetail_tapin
                 VALUES v_test_tab (i)
              RETURNING       record_num
            BULK COLLECT INTO v_rec_num;

      EXCEPTION
         WHEN OTHERS
         THEN

      V_ERROR_CNT:=sql%bulk_exceptions.count;

      for ind in 1..v_error_cnt
      loop
      l_ind:=sql%bulk_exceptions(ind).error_index;
      insert into record_num_session
      values
      v_test_tab(l_ind).record_num;   --------PL/SQL: ORA-00904: : invalid identifier
      end loop;
      END;

           
   EXIT WHEN temp_rec_tap_cur%NOTFOUND;    
      
   END LOOP;

   COMMIT;

   p_retval :=1;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      p_retval := 0;
      p3_errorlog ('TAPINDETAWARE', SQLERRM, v_filename);
END;
/