On Jun 16, 10:39 am, Michael Moore <michaeljmo...@gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
As should I.
Thanks, Thomas.
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
