add PRAGMA AUTONOMOUS_TRANSACTION as shown below. Mike FUNCTION car_acknowledged_score (p_vendor_id IN NUMBER ,p_month IN VARCHAR2 ,p_year IN VARCHAR2) RETURN NUMBER IS PRAGMA AUTONOMOUS_TRANSACTION v_acknowledged_result NUMBER; v_acknowledged_score NUMBER;
On Mon, Jun 15, 2009 at 6:14 AM, Chris <christopherc...@hotmail.com> wrote: > > Hi All, > > I have the following function which calculates and returns a figure. > What I am trying to do is to put an 'IF' statement in the function to > say if the returning value is > 28 then update a table in the > database. I still want the returning figure to be calculated but for > some reason I am getting the following error: > > "report error: > ORA-14551: cannot perform a DML operation inside a query " > > /*This function calculates a value and if the acknowledgment takes > longer than 28 days the vendor gets 2 penalty points and a > acknowledgement score of 0*/ > > FUNCTION car_acknowledged_score > (p_vendor_id IN NUMBER > ,p_month IN VARCHAR2 > ,p_year IN VARCHAR2) > RETURN NUMBER > IS > > v_acknowledged_result NUMBER; > v_acknowledged_score NUMBER; > > BEGIN > > SELECT xxmel_apex_vendor_rating_pkg.car_acknowledged_result > (p_vendor_id, p_month, p_year) > INTO v_acknowledged_result > FROM dual; > > IF v_acknowledged_result <= 4 THEN v_acknowledged_score := 6; > ELSIF v_acknowledged_result BETWEEN 4 AND 7 THEN > v_acknowledged_score := 4; > ELSIF v_acknowledged_result BETWEEN 7 AND 10 THEN > v_acknowledged_score := 2; > ELSIF v_acknowledged_result BETWEEN 10 AND 28 THEN > v_acknowledged_score := 0; > ELSIF v_acknowledged_result > 28 THEN v_acknowledged_score := 0; > --2 points deducted for being greater than 28 > UPDATE xxmel_apex_vrd vrd > SET vrd.PENATLY_POINTS = (NVL(vrd.PENATLY_POINTS,0) + 2) > WHERE vrd.DATE_PERIOD_MONTH = p_month > AND vrd.DATE_PERIOD_YEAR = p_year > AND vrd.SUPPLIER_ID = p_vendor_id; > COMMIT; > END IF; > > RETURN ROUND(v_acknowledged_score,1); > > END car_acknowledged_score; > > Any help would be great, thanks > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en -~----------~----~----~----~------~----~------~--~---
