Oracle data load - extract data from excel sheet.

prompt Loading Tail records ...
SET serveroutput ON SIZE 1000000
DECLARE
   
CURSOR c1 
   IS
Select 'A321' profs_id, 'N15N' AIR_REG, 102 pcdtm_code,'N' EXCHANGE_INDICATOR,trunc(sysdate-82) STARTDATE  from dual union
Select 'A321' profs_id, 'N15A' AIR_REG, 102 pcdtm_code,'N' EXCHANGE_INDICATOR,trunc(sysdate-82) STARTDATE  from dual union
Select 'A321' profs_id, 'N16N' AIR_REG, 102 pcdtm_code,'N' EXCHANGE_INDICATOR,trunc(sysdate-82) STARTDATE  from dual;
v_records_processed number;
v_profs_id          number;

Begin
  
v_records_processed := 0;

FOR c1rec IN c1
    LOOP
     
     Begin
      select profs.id 
        into v_profs_id
        from dss.profiles profs 
       where profs.profile = c1rec.PROFS_ID
        and profile_type = 'AC'; 
          
          exception when no_data_found then
            dbms_output.put_line('No Profile: ' ||  c1rec.AIR_REG);
              v_profs_id := null; 
     End;
       
            Begin 
             insert into dss.agreement_term_tails 
                       (AGTERM_ID
                       ,PROFS_ID_AIR
                       ,AIR_REG
                       ,PCDTM_CODE
                       ,EXCHANGE_INDICATOR
                       ,STARTDATE
                       ,PCDTM_DIV_NO)
                values(  2795
                       , v_profs_id
                       , c1rec.AIR_REG    
                       , c1rec.PCDTM_CODE
                       , c1rec.EXCHANGE_INDICATOR
                       , c1rec.STARTDATE
                       , 7);    
        
            End;
               v_records_processed := v_records_processed + 1;
          END LOOP;
 
    dbms_output.put_line('# of rows processed: '|| TO_CHAR(v_records_processed));

                       IF SQL%NOTFOUND
                        THEN
                           DBMS_OUTPUT.PUT_LINE('No Records Processed');
                       END IF;
   COMMIT;
End; 
/ 

Select allOpen in new window