Ah! should have thought of that. Thanks! On Tue, Jun 16, 2009 at 7:01 AM, Thomas Olszewicki <thom...@cpas.com> wrote:
> > On Jun 16, 9:02 am, ddf <orat...@msn.com> wrote: > > On Jun 15, 4:37 pm, Michael Moore <michaeljmo...@gmail.com> wrote: > > > > > 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 > > > > No, I didn't, but I do now. :) > > > > David Fitzjarrell > > Michael and David. > Simple function actually do work with Pragma Autonomous Transaction, > but you have to > finish the transaction within the function call. > Check this out: > > create table emp_track > as select empno, ename, deptno, sal, hiredate+17 chg_dt > from scott.emp; > > create or replace function track_emp(eno in number) return date is > cdte date; > pragma autonomous_transaction; > begin > select chg_dt into cdte from emp_track where empno = eno; > > if cdte < sysdate then > update emp_track set chg_dt = sysdate where empno = > eno; > -- Finish the aut. transaction. > commit; > ------------------------------- > end if; > > return cdte; > end; > / > > select track_emp(empno) from scott.emp; > > SQL> select track_emp(empno) from scott.emp; > > TRACK_EMP(EMPNO) > ---------------- > 1/3/1981 > 3/9/1981 > 3/11/1981 > ... > > > HTH > Thomas > > > --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
