This discussion is archived
11 Replies Latest reply: Jan 5, 2006 12:23 PM by DietmarAust RSS

Session state not set until process completes?

VANJ Journeyer
Currently Being Moderated
If I have a after-submit process like
declare
l_foo varchar2(10);
begin
  -- use some logic to set l_foo
  :P1_FOO := l_foo;
   my_stored_proc;
end;
When I inspect the value of v('P1_FOO') inside my_stored_proc(), it is NULL! But the Session State in the Dev toolbar indeed shows a non-null value for it when the page branches back (to the same page).

Shouldn't that assignment statement just before calling my stored proc set session state (and commit it) so it should be accessible to my stored procedure? What am I missing?

Thanks
  • 1. Re: Session state not set until process completes?
    60437 Employee ACE
    Currently Being Moderated
    Vikas,

    The way it works is a little quirky. Consider:
      declare 
        l_val varchar2(3);
        l_new varchar2(3) := 'bar';
      begin
        l_val := v('P1_X'); -- assume current value of P1_X is 'foo'
        :P1_X := 'bar'; -- update session state after entire block has been executed
        l_val := v('P1_X'); -- l_val is still 'foo'
        htmldb_application.save_in_substitution_cache('P1_X',l_new); -- this will never be committed
        l_val := v('P1_X'); -- l_val is now 'bar'
      end;
    Session state is updated only after the entire block is executed but you can affect that by altering "in memory" session state using the API call I showed. We'll make this clearer in the documentation.
    Shouldn't that assignment statement ....
    Calls for an opinion.

    Scott
  • 2. Re: Session state not set until process completes?
    VANJ Journeyer
    Currently Being Moderated
    :P1_X := 'bar'; -- update session state after entire block has been executed
    Session state is updated only after the entire block is executed
    Wait a minute, we recently had a discussion at

    Re: HTMLDB_TOOLS by Vikas need some helps about rollback(Urgent !!!)
    Re: Question about Commit

    where you said that an assignment statement like the above causes a COMMIT?

    So, if I understand you correctly, the above statement
    :P1_X := 'foo';
    does a COMMIT, but session state is not updated?

    Um, so what exactly is being COMMITed?
    using the API call I showed.
    Regarding that API call, does it also set committed session state or only the in-memory value? In other words, is
    :P1_X := 'foo';
    equivalent to
    htmldb_application.save_in_substitution_cache('P1_X','foo');
    Another thing I observed...if I use
    htmldb_util.set_session_state('P1_X',l_foo);
    my_stored_proc;
    instead of
    :P1_X := l_foo;
    my_stored_proc;
    In the former case, my_stored_proc() can see the updated value using v('P1_FOO'), in the latter case, it cannot. Why the difference?

    So, what are we to learn from this? That if an after-submit process sets session state using bind variable notation in an assignment statement, it will NOT be visible to any stored procedures called in the same PL/SQL block?

    What constitutes a PL/SQL block here? The entire after-submit process? Or all the after-submit processes that fire on the page?

    But if htmldb_util.set_session_state is used, everything is fine.

    Can you please summarize?

    Thanks
  • 3. Re: Session state not set until process completes?
    60437 Employee ACE
    Currently Being Moderated
    Vikas,
    Can you please summarize?
    I will try:

    The post-execution values of session state items associated with bind variables in dynamically executed blocks are conveyed to session state (and committed) at the end of the block's execution phase whenever those values differ from the pre-execution value. This includes out parameters, select into variables, assignment LHS, and the occasional bind variable reference that fits none of those categories but for which the underlying session state value has been altered since the pre-execution values were gathered (you had a case like that as I recall). It might help to think of session-state alteration that occurs within dynamically executed anonymous blocks as an intended side-effect of the block's actual PL/SQL execution logic, i.e., the changes made to session state are deduced from changes to bind variables. The unit of granularity is the chunk of code dynamically executed, like a single page process, computation, etc.

    htmldb_application.save_in_substitution_cache updates the in-memory arrays only so you can reference any items thus altered using v or other ways if you need to before the anonymous block has completed execution.

    Scott
  • 4. Re: Session state not set until process completes?
    VANJ Journeyer
    Currently Being Moderated
    Thanks for the summary, but I am not sure I understand a few things

    1. In that other thread you said "however session-state altering statements within your code will cause a commit, e.g., :P1_ITEM := null;"

    What exactly is being committed here? As you demonstrated by your sample code above, even after the above assignment statement is executed, v('P1_ITEM') does NOT have access to the updated value, so I don't understand what is committed

    2. Regarding the following 3 statements
    :P1_ITEM := 'foo';
    htmldb_util.set_session_state('P1_ITEM','foo');
    htmldb_application.save_in_substitution_cache('P1_ITEM','foo');
    Which one(s) have a COMMIT embedded in them? This would be important for us to know because it would affect the transaction boundary of the larger process/block that the statement is in.

    3. Why is a change made to session state by htmldb_util.set_session_state() accessible to the v function whereas the same change made using a direct assignment using bind variable notation is not?

    Does this mean that using the former over the latter should be preferred?

    4. Given (3) above, htmldb_application.save_in_substitution_cache seems to be redundant. htmldb_util.set_session_state() updates the "real" session state and that too right away, so if I always use that API exclusively to update session state, I would never need htmldb_application.save_in_substitution_cache, right?

    Thanks
  • 5. Re: Session state not set until process completes?
    60437 Employee ACE
    Currently Being Moderated
    1. >> I don't understand what is committed

    Work is commtted, specifically inserts/updates on wwv_flow_data and sometimes the preferences table.

    2. The first statement commits when the containing block is executed (at the end). The second commits right away. The third updates the in-memory array only.

    3. >>Why is a change made to session state by htmldb_util.set_session_state() accessible to the v function whereas the same change made using a direct assignment using bind variable notation is not?

    set_session_state updates/inserts into the array and v fetches from the array. Changes to committed session state (in the table) through bind variables are also reflected in the array and are thus available to v after the end of the execution of the anonymous block (per my earlier, more detailed explanation). The only difference is the timing.

    4. They are not redundant. One does half the job. You can use set_session_state to get it all done immediately in preference to other methods within any given anonymous block. However this prevents the block from handling exceptions and doing a rollback of any work performed up to but not including that call. Using save_in_substitution_cache would allow you to reference the saved value later in the same block (before the commit) but without the exception handling problem. It also might be useful during rendering if you wanted to make it look like some item had a certain value so that the page works the way you want it to during rendering only. No, I don't have a real world example but the procedure was created because it was needed internally and we do use it.

    Scott
  • 6. Re: Session state not set until process completes?
    VANJ Journeyer
    Currently Being Moderated
    The third updates the in-memory array only.
    Ok, but when does it commit? I guess it doesn't commit at all?

    So, given your explanation on the commit behaviour of each of the 3 options, if I have the following 3 after-submit processes:

    1.
    insert into ...
    :P1_ITEM := 'foo';
    rollback;
    2.
    insert into ...
    htmldb_util.set_session_state('P1_ITEM','foo');
    rollback;
    3.
    insert into ...
    htmldb_application.save_in_substitution_cache('P1_ITEM','foo');
    rollback;
    In which process(es) would my INSERT statement be rolled back?

    In which process(es) would the modification to session state (P1_ITEM) be rolled back?

    Thanks

    Message was edited by:
    Vikas
  • 7. Re: Session state not set until process completes?
    60437 Employee ACE
    Currently Being Moderated
    First, See my corrected text above (changed "and including" to "but not including").
    I guess it doesn't commit at all?
    Right, no commit for save_in_substitution_cache; it's not DML.
    In which process(es) would my INSERT statement be rolled back?
    The tests that would have made it unnecessary to ask would have shown that inserts will be rolled back in cases 1 and 3. Please advise if your results are at odds with my claims.
    In which process(es) would the modification to session state (P1_ITEM) be rolled back?
    Session state changes are never rolled back unless there is an internal error. Statements in dynamically executed anonymous blocks that change bind variables associated with session state result in those changes being committed unless a rollback is issued by the engine in response to an unhandled exception raised in the block, e.g.,
      declare x number := 1;
      begin
        :P1_X := 'foo';
        x := x/0;
      end;
    Scott
  • 8. Re: Session state not set until process completes?
    VANJ Journeyer
    Currently Being Moderated
    Scott: Thank you so much for being so patient with my questions. This was a very useful discussion.

    Thanks.
  • 9. Re: Session state not set until process completes?
    DietmarAust Oracle ACE
    Currently Being Moderated
    Thanks as well :).

    I believe this was a very interesting discussion for many of us.

    Scott, I have one more question:
    If I properly understand it then the commits for the session handling is done in the current running session.

    Was there a specific reason for not using autonomous transactions?

    Thanks,
    ~Dietmar.
  • 10. Re: Session state not set until process completes?
    60437 Employee ACE
    Currently Being Moderated
    If I properly understand it then the commits for the session handling is done in the current running session. Was there a specific reason for not using autonomous transactions?
    I don't know if there was.

    Scott
  • 11. Re: Session state not set until process completes?
    DietmarAust Oracle ACE
    Currently Being Moderated
    Thanks anyway.

    Sometimes these kind of design decisions give us a deeper understanding of the workings of the system.

    ~Dietmar.