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
-~----------~----~----~----~------~----~------~--~---
