I have this procedure. Problem is that insert into table doesnt work. Can someone give me a point where is problem? Im pretty sure that insert is valid, maybe there is problem with execute immediate, I dont know. When I send insert into dbms_output and run it in sql developer, then it works. Also v_active_table has right value.
create or replace PROCEDURE multiplicate_mts_data_delete AS
v_active_table Varchar2(100);
v_source_table Varchar2(100);
BEGIN
v_active_table := null;
select value into v_active_table from settings where setting = 'mts.data.table';
IF v_active_table = 'MTS_API_1' THEN
v_source_table := 'MTS_API_2';
else
v_source_table := 'MTS_API_1';
END IF;
execute IMMEDIATE 'truncate table ' || v_active_table;
execute IMMEDIATE ' insert into ' || v_active_table ||' (MTSID ,
PRODUCT,
SERVICELEVEL,
CHARGECODE ,
CHARGECODENAME,
CHARGECODELOCALNAME,
ORIGINZONE,
DESTINATIONZONE ,
CATEGORY ,
SHIPPERCOUNTRY ,
ORIGINCOUNTRY ,
ORIGINSTATION ,
CONSIGNEECOUNTRY ,
DESTINATIONCOUNTRY,
DESTINATIONSTATION ,
AIRLINE ,
DIRECTFLIGHT,
ROUTING ,
FREQUENCY ,
DIRECTMOVEMENT,
FREQUENCYUNIT ,
PRICINGLEVEL ,
CAMPAIGNNAME ,
TRUCKTYPE ,
WEIGHTRATIO ,
CURRENCY ,
CHARGECODEINFOFLAG,
COSTBASE,
MINRATE,
MAXRATE,
UNIT ,
RATEPERUNIT,
RATEWM ,
RATEPERSHIPMENT,
RATEPERENTRY ,
NUMFREELINESINCLUDED,
RATEPERLINEITEM,
PERCENTAGE ,
CHAGECODEREFERENCE,
ALLIN ,
RATEFORDEDICATEDVEHICLE ,
TRUCKSIZEMETRICTONES,
INCLUSIVETOFRT ,
ONREQUEST ,
NOTES ,
TRANSITTIMEUNIT,
TRANSITTIME,
TRANSITTIMECFSTOPORT ,
TRANSITTIMEPORTTOCFS ,
VALIDFROM,
VALIDTO,
WBMIN ,
WBMAX,
OPERATOR1,
AMOUNT1,
UNITPRICE1 ,
OPERATOR2 ,
AMOUNT2 ,
UNITPRICE2,
OPERATOR3 ,
AMOUNT3,
UNITPRICE3 ,
OPERATOR4,
AMOUNT4,
UNITPRICE4 ,
OPERATOR5,
AMOUNT5,
UNITPRICE5 ,
OPERATOR6,
AMOUNT6 ,
UNITPRICE6 ,
OPERATOR7,
AMOUNT7 ,
UNITPRICE7 ,
OPERATOR8,
AMOUNT8 ,
UNITPRICE8,
OPERATOR9,
AMOUNT9 ,
UNITPRICE9,
OPERATOR10,
AMOUNT10 ,
UNITPRICE10,
DISCOUNT1 ,
DENSITY1,
DISCOUNT2,
DENSITY2,
DISCOUNT3,
DENSITY3 ,
DISCOUNT4 ,
DENSITY4 ,
DISCOUNT5 ,
DENSITY5 ,
DISCOUNT6 ,
DENSITY6 ,
DISCOUNT7 ,
DENSITY7 ,
DISCOUNT8 ,
DENSITY8 ,
DISCOUNT9 ,
DENSITY9 ,
DISCOUNT10 ,
DENSITY10 ,
DISCOUNT11 ,
DENSITY11 ,
DISCOUNT12 ,
DENSITY12 ,
ORIGINSTATIONNAME ,
DESTINATIONSTATIONNAME ,
CARRIER ,
CONTAINERTYPE ,
RATEPERCONTAINER ,
RATEPERCONTAINERPERDAY ,
FREETIME ,
FREETIMEUNIT ,
ORIGINLOCALITY ,
ORIGINZIPCODEFROM ,
ORIGINZIPCODETO ,
WEIGHTBREAK ,
WEIGHTBREAKUNIT ,
TEUTHERESHOLDFORRATEAPPLICABILITY ,
TRANSHIPMENTPORT ,
FREQUENCYOFSAILINGS ,
FREQUENCYOFSAILINGSUNIT ,
DESTINATIONLOCALITY ,
DESTINATIONZIPCODEFROM ,
DESTINATIONZIPCODETO ,
UPDATEDTS ,
ORIGINZONELR ,
DESTINATIONZONELR ,
CYTOCYTRANSITTIME ,
COMMODITYCODE ,
COMMODITYDESC ,
DEMURRAGEANDDETENTIONCOMBINEDFLAG ,
NUMBEROFFREEDAYS ,
EXCLUDEDDAYS ,
RATEPERWMPERDAY ,
SERVICELEVELCODE ,
ADDITIONALCHARGECODESINCLUDED ,
AFPTRANSITTIME ,
AFPBASIC ,
AFPMINMFSC ,
AFPRATEPERKGMFSC ,
AFPCOSTWEIGHTBASEMFSC ,
AFPMINMSSC ,
AFPRATEPERKGMSSC ,
AFPCOSTWEIGHTBASEMSSC ,
AFPRATEPERENTRYOCLE ,
AFPRATEPERLINEITEMOCAL ,
AFPRATEPERENTRYDCLE ,
AFPRATEPERLINEITEMDCAL ,
AFPchargeCodeCargoScreening,
AFPminCargoScreening,
AFPratePerKgCargoScreening,
AFPratePerShptCargoScreening,
AFPchargeCodeDGSurcharge,
AFPminDGSurcharge,
AFPratePerKgDGSurcharge,
AFPratePerShptDGSurcharge,
AFPCargoScreeningFlag,
AFPDGSurchargeFlag,
AFPCo2e,
AFPChargecodeBiofuel,
AFPPerKgBiofuel,
AFPBioFuelFlag,
AFPChargecodeOffsetting,
AFPPerKgOffsetting,
AFPOffsettingFlag,
AFPChargecodeBiofuelOffsetting,
AFPPerKgBiofuelOffsetting,
AFPBioFuelOffsettingFlag,
RATEPERTON ) select MTSID ,
PRODUCT ,
SERVICELEVEL,
CHARGECODE ,
CHARGECODENAME,
CHARGECODELOCALNAME,
ORIGINZONE,
DESTINATIONZONE ,
CATEGORY ,
SHIPPERCOUNTRY ,
ORIGINCOUNTRY ,
ORIGINSTATION ,
CONSIGNEECOUNTRY ,
DESTINATIONCOUNTRY,
DESTINATIONSTATION ,
AIRLINE ,
DIRECTFLIGHT,
ROUTING ,
FREQUENCY ,
DIRECTMOVEMENT,
FREQUENCYUNIT ,
PRICINGLEVEL ,
CAMPAIGNNAME ,
TRUCKTYPE ,
WEIGHTRATIO ,
CURRENCY ,
CHARGECODEINFOFLAG,
COSTBASE,
MINRATE,
MAXRATE,
UNIT ,
RATEPERUNIT,
RATEWM ,
RATEPERSHIPMENT,
RATEPERENTRY ,
NUMFREELINESINCLUDED,
RATEPERLINEITEM,
PERCENTAGE ,
CHAGECODEREFERENCE,
ALLIN ,
RATEFORDEDICATEDVEHICLE ,
TRUCKSIZEMETRICTONES,
INCLUSIVETOFRT ,
ONREQUEST ,
NOTES ,
TRANSITTIMEUNIT,
TRANSITTIME,
TRANSITTIMECFSTOPORT ,
TRANSITTIMEPORTTOCFS ,
VALIDFROM,
VALIDTO,
WBMIN ,
WBMAX,
OPERATOR1,
AMOUNT1,
UNITPRICE1 ,
OPERATOR2 ,
AMOUNT2 ,
UNITPRICE2,
OPERATOR3 ,
AMOUNT3,
UNITPRICE3 ,
OPERATOR4,
AMOUNT4,
UNITPRICE4 ,
OPERATOR5,
AMOUNT5,
UNITPRICE5 ,
OPERATOR6,
AMOUNT6 ,
UNITPRICE6 ,
OPERATOR7,
AMOUNT7 ,
UNITPRICE7 ,
OPERATOR8,
AMOUNT8 ,
UNITPRICE8,
OPERATOR9,
AMOUNT9 ,
UNITPRICE9,
OPERATOR10,
AMOUNT10 ,
UNITPRICE10,
DISCOUNT1 ,
DENSITY1,
DISCOUNT2,
DENSITY2,
DISCOUNT3,
DENSITY3 ,
DISCOUNT4 ,
DENSITY4 ,
DISCOUNT5 ,
DENSITY5 ,
DISCOUNT6 ,
DENSITY6 ,
DISCOUNT7 ,
DENSITY7 ,
DISCOUNT8 ,
DENSITY8 ,
DISCOUNT9 ,
DENSITY9 ,
DISCOUNT10 ,
DENSITY10 ,
DISCOUNT11 ,
DENSITY11 ,
DISCOUNT12 ,
DENSITY12 ,
ORIGINSTATIONNAME ,
DESTINATIONSTATIONNAME ,
CARRIER ,
CONTAINERTYPE ,
RATEPERCONTAINER ,
RATEPERCONTAINERPERDAY ,
FREETIME ,
FREETIMEUNIT ,
ORIGINLOCALITY ,
ORIGINZIPCODEFROM ,
ORIGINZIPCODETO ,
WEIGHTBREAK ,
WEIGHTBREAKUNIT ,
TEUTHERESHOLDFORRATEAPPLICABILITY ,
TRANSHIPMENTPORT ,
FREQUENCYOFSAILINGS ,
FREQUENCYOFSAILINGSUNIT ,
DESTINATIONLOCALITY ,
DESTINATIONZIPCODEFROM ,
DESTINATIONZIPCODETO ,
UPDATEDTS ,
ORIGINZONELR ,
DESTINATIONZONELR ,
CYTOCYTRANSITTIME ,
COMMODITYCODE ,
COMMODITYDESC ,
DEMURRAGEANDDETENTIONCOMBINEDFLAG ,
NUMBEROFFREEDAYS ,
EXCLUDEDDAYS ,
RATEPERWMPERDAY ,
SERVICELEVELCODE ,
ADDITIONALCHARGECODESINCLUDED ,
AFPTRANSITTIME ,
AFPBASIC ,
AFPMINMFSC ,
AFPRATEPERKGMFSC ,
AFPCOSTWEIGHTBASEMFSC ,
AFPMINMSSC ,
AFPRATEPERKGMSSC ,
AFPCOSTWEIGHTBASEMSSC ,
AFPRATEPERENTRYOCLE ,
AFPRATEPERLINEITEMOCAL ,
AFPRATEPERENTRYDCLE ,
AFPRATEPERLINEITEMDCAL ,
AFPchargeCodeCargoScreening,
AFPminCargoScreening,
AFPratePerKgCargoScreening,
AFPratePerShptCargoScreening,
AFPchargeCodeDGSurcharge,
AFPminDGSurcharge,
AFPratePerKgDGSurcharge,
AFPratePerShptDGSurcharge,
AFPCargoScreeningFlag,
AFPDGSurchargeFlag,
AFPCo2e,
AFPChargecodeBiofuel,
AFPPerKgBiofuel,
AFPBioFuelFlag,
AFPChargecodeOffsetting,
AFPPerKgOffsetting,
AFPOffsettingFlag,
AFPChargecodeBiofuelOffsetting,
AFPPerKgBiofuelOffsetting,
AFPBioFuelOffsettingFlag,
RATEPERTON from ' || v_source_table;
commit;
END