PACKAGE PK_EXCEL_TO_DB IS
  TYPE tKeyValue IS RECORD (
    CROUTE         VARCHAR2(255),
    VROUTE         VARCHAR2(1000),
    CTRNDATE       VARCHAR2(255),
    VTRNDATE       VARCHAR2(1000),
    CTTIME	        VARCHAR2(255),
    VTTIME         VARCHAR2(1000),
    CTID           VARCHAR2(255),
    VTID           VARCHAR2(1000));
  TYPE tDataList IS TABLE OF tKeyValue
  index by binary_integer;
  
  PROCEDURE PR_DO_INSERT(i_lData IN tDataList);	  
END;


PACKAGE BODY PK_EXCEL_TO_DB IS
    PROCEDURE PR_DO_INSERT(i_lData IN tDataList) IS
    CC_ROUTE         VARCHAR2(255);
    VV_ROUTE         VARCHAR2(1000);
    CC_TRNDATE       VARCHAR2(255);
    VV_TRNDATE       VARCHAR2(1000);
    CC_TTIME	        VARCHAR2(255);
    VV_TTIME         VARCHAR2(1000);
    CC_TID           VARCHAR2(255);
    VV_TID           VARCHAR2(1000);
 BEGIN
    FOR i IN 1..i_lData.count 
    LOOP
      CC_ROUTE:=CC_ROUTE || ',' || i_ldata(i).CROUTE;
      VV_ROUTE:=VV_ROUTE|| ',''' || i_ldata(i).VROUTE || '''';
      
      CC_TRNDATE :=CC_TRNDATE  || ',' || i_ldata(i).CTRNDATE ;
      VV_TRNDATE :=VV_TRNDATE || ',''' || i_ldata(i).VTRNDATE  || '''';
      
      CC_TTIME :=CC_TTIME  || ',' || i_ldata(i).CTTIME ;
      VV_TTIME :=VV_TTIME || ',''' || i_ldata(i).VTTIME  || '''';
      
      CC_TID :=CC_TID  || ',' || i_ldata(i).CTID ;
      VV_TID :=VV_TID || ',''' || i_ldata(i).VTID|| '''';
    END LOOP;
    --EXECUTE IMMEDIATE 
    FORMS_DDL('INSERT INTO TEMP2 (' || SUBSTR(CC_ROUTE, 2) || ' ' || 
				       SUBSTR(CC_TRNDATE, 2) || ' ' || 
				       SUBSTR(CC_TTIME, 2) || '' || 
				       SUBSTR(CC_TID, 2) || ') 
                         VALUES (' ||  SUBSTR(VV_ROUTE,2) || '  ' || 
				       SUBSTR(VV_TRNDATE,2) || '' || 
				       SUBSTR(VV_TTIME,2) || ' ' || 
				       SUBSTR(VV_TID,2)|| ')');                                   
 commit;                                            
  END;
END;

DECLARE

  l_sql varchar2(32767);
.....
BEGIN

......
l_sql := 'INSERT INTO TEMP2 (' || SUBSTR(CC_ROUTE, 2) || ' ' || 
				       SUBSTR(CC_TRNDATE, 2) || ' ' || 
				       SUBSTR(CC_TTIME, 2) || '' || 
				       SUBSTR(CC_TID, 2) || ') 
                         VALUES (' ||  SUBSTR(VV_ROUTE,2) || '  ' || 
				       SUBSTR(VV_TRNDATE,2) || '' || 
				       SUBSTR(VV_TTIME,2) || ' ' || 
				       SUBSTR(VV_TID,2)|| ')';

message(l_sql);

end;