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