AUTOCOMMIT and PRAGMA TRANSACTION ????

BILL Carlisle
BILL Carlisle used Ask the Experts™
on
I have APEX with Autocommit.
I want to have this not affect the other autocommits going on in the 3 other processess in a PAGE in an app.

is it possible?

  PROCEDURE "UPDATE_STATUS"(
    p_order_id         IN PD_ORDER.ORDER_ID%TYPE,
    p_status_id        IN  PD_ORDER.ORDER_STATUS%TYPE) AS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    select count(*) into lcnt etc
    if(lcnt > 0) then
        lcnt2 := add_row(user_id);
             if(lcnt2 > 0) then
                 lcnt3:=call_set_id();
    if lcnt3 >0 then
     COMMIT;
    else
      rollback;
    end if;
END

  END;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Top Expert 2012
Commented:
I'm not sure what the question is.


pragma autonomous_transaction  causes that procedure to create a separate transaction from the rest of the session.

So, yes, the commit/rollback in that procedure will not affect anything going on outside of the procedure.  similarly,  any uncommitted work done outside of the procedure won't be seen inside the procedure either.
BILL CarlisleAPEX Developer

Author

Commented:
Thank you Sean!
I broke the real one done piece by piece and found I was committing at another point.
Works great now..

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial