DDF, You are correct, it does not work with simple functions. In order for it to work as a function, it would have to be a TABLE function. I know from having done it many times that you can do an UPDATE in a table function if you use PRAGMA AUTONOMOUS_TRANSACTION. But you probably already knew that too.
Regards, Mike On Mon, Jun 15, 2009 at 11:55 AM, ddf <orat...@msn.com> wrote: > > > > On Jun 15, 12:10 pm, Michael Moore <michaeljmo...@gmail.com> wrote: > > 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- Hide quoted text - > > > > - Show quoted text - > > My tests show that doesn't work: > > SQL> create table emp_track > 2 as select empno, ename, deptno, sal, hiredate+17 chg_dt > 3 from emp; > > Table created. > > SQL> > SQL> create or replace function track_emp(eno in number) > 2 return date > 3 is > 4 cdte date; > 5 pragma autonomous_transaction; > 6 begin > 7 select chg_dt > 8 into cdte > 9 from emp_track > 10 where empno = eno; > 11 > 12 if cdte < sysdate then > 13 update emp_track > 14 set chg_dt = sysdate > 15 where empno = eno; > 16 end if; > 17 > 18 return cdte; > 19 > 20 end; > 21 / > > Function created. > > SQL> > SQL> show errors > No errors. > SQL> > SQL> select track_emp(empno) > 2 from emp; > select track_emp(empno) > * > ERROR at line 1: > ORA-06519: active autonomous transaction detected and rolled back > ORA-06512: at "BING.TRACK_EMP", line 20 > > > SQL> > > > > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
