issue

[learning_bee]

We have two tables. Table A and Table B. We have a “row level” trigger on Table A and are using the “NEW” values, we want to update Table B when certain criteria pulled from Table A are met. Triggers have problems issuing “COMMIT’s” so we’re utilizing the “PRAGMA AUTONOMOUS_TRANSACTION” command in our trigger declaration. Our update statement is still not committing though.



We are positive that the procedure is getting to our UPDATE and that our WHERE clause is correct because we are inserting records into a message log throughout the procedure. In addition we also populated a variable with the COUNT from a select on the WHERE clause and inserted that into the message log as well. Everything is as would be expected.



However our UPDATE is still not committing. (We have COMMIT’s in several places of the code including directly after our update).



Any thoughts?

[slimdave]

What is the sign that the update is not commiting?

[learning_bee]

Ok, after creating a test procedure and attempting some hard codes we found that the problem was that the UPDATE statement isn’t reading our “FuelPercent” variable correctly and isn’t performing the update. We’re setting the FuelPercent to the Fuel Percent parameter but for some reason the system’s not regarding that as valid. The update works if we replace the FuelPercent variable with pFuelPercent. Why won’t it take the variable? Any ideas?

FuelPercent := pFuelPercent;

UPDATE GMSOWNR.CTB_SA_FUEL CF
SET CF.FUELPERCENT = FuelPercent
WHERE UPPER(RTRIM(LTRIM(CF.PIPELINE))) = UPPER(RTRIM(LTRIM(pPipeline)))
AND UPPER(RTRIM(LTRIM(CF.PRODMONTH))) = UPPER(RTRIM(LTRIM(pProdMonth)))
AND UPPER(RTRIM(LTRIM(CF.PRODYEAR))) = UPPER(RTRIM(LTRIM(pProdYear)))
AND UPPER(RTRIM(LTRIM(CF.POINTOFVIEW))) = UPPER(RTRIM(LTRIM(pFuelType)))
AND UPPER(RTRIM(LTRIM(CF.FUELRANK))) = UPPER(RTRIM(LTRIM(FuelCase)));

[marist89]

Originally posted by learning_bee
Ok, after creating a test procedure and attempting some hard codes we found that the problem was that the UPDATE statement isn’t reading our “FuelPercent” variable correctly and isn’t performing the update. We’re setting the FuelPercent to the Fuel Percent parameter but for some reason the system’s not regarding that as valid. The update works if we replace the FuelPercent variable with pFuelPercent. Why won’t it take the variable? Any ideas?

FuelPercent := pFuelPercent;

UPDATE GMSOWNR.CTB_SA_FUEL CF
SET CF.FUELPERCENT = FuelPercent
WHERE UPPER(RTRIM(LTRIM(CF.PIPELINE))) = UPPER(RTRIM(LTRIM(pPipeline)))
AND UPPER(RTRIM(LTRIM(CF.PRODMONTH))) = UPPER(RTRIM(LTRIM(pProdMonth)))
AND UPPER(RTRIM(LTRIM(CF.PRODYEAR))) = UPPER(RTRIM(LTRIM(pProdYear)))
AND UPPER(RTRIM(LTRIM(CF.POINTOFVIEW))) = UPPER(RTRIM(LTRIM(pFuelType)))
AND UPPER(RTRIM(LTRIM(CF.FUELRANK))) = UPPER(RTRIM(LTRIM(FuelCase)));

This is highly confusing because you're using a variable "FuelPercent" but you have that field in your table. I am guessing that Oracle is updating the cf.fuelpercent with cf.fuelpercent and not using your variable. Try using "lFuelPercent".