我有这个过程：
create or replace procedure multiplicate_mts_api_data(number_of_runs NUMBER) AS
v_number_of_runs Number;
v_active_mts_table Varchar2(100);
v_product Varchar2(30) := 'TMP';
Begin
IF number_of_runs > 3 then
v_number_of_runs := 3;
else
v_number_of_runs := number_of_runs;
END IF;
select value into v_active_mts_table from settings where setting = 'mts.data.table';
For i IN 1..v_number_of_runs
Loop
execute IMMEDIATE ' insert into ' || v_active_mts_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 ID_GENERATOR.nextval ,
'|| v_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_active_mts_table || ' where product != '|| v_product;
End loop;
End;
当我尝试运行它时，我得到错误：
Error report -
ORA-00904: "TMP": invalid identifier
ORA-06512: at "DEV_CRDB.MULTIPLICATE_MTS_API_DATA", line 17
ORA-06512: at line 2
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
有人能告诉我如何解决这个问题吗？如果我尝试通过sql开发人员运行这个插件，它会顺利运行。我是plsql的初学者，所以如果这是一个伪问题，请耐心等待。也许sql插入串联有问题，我不知道。谢谢Petr
这可能是：
查看错误：
ORA-00904: "TMP": invalid identifier
ORA-06512: at "DEV_CRDB.MULTIPLICATE_MTS_API_DATA", line 17
您在此处将TMP分配给一个变量：
v_product Varchar2(30) := 'TMP';
然后在此处引用v_product:
RATEPERTON ) select ID_GENERATOR.nextval ,
'|| v_product ||' ,
SERVICELEVEL,
看起来上面的行导致了错误。原因是在"；v_product"-只是串联所需的引号。因此选择变成
...
SELECT ID_GENERATOR.nextval ,
TMP,
SERVICELEVEL,
...
并且可能不存在称为"0"的列；TMP"；在引发错误的表格中。
我的猜测是你想选择值"；TMP"；，而不是列TMP。值"；TMP"；需要用引号括起来，并且这些引号需要转义，因此请尝试以下操作：
RATEPERTON ) select ID_GENERATOR.nextval ,
'''|| v_product ||''' ,
SERVICELEVEL,
试试看。