Oracle Apex set session state

Question

I have multiple items on my page, all share the same name structure:

P1_ITEM_NUMBER_1, P1_ITEM_NUMBER_2, P1_ITEM_NUMBER_3 and so on.

I'm trying to set the value for all with a loop, but the set session function is not doing it. Any idea why?

declare

v_text varchar2(100);

BEGIN

    FOR v_initial IN 1..100 LOOP
        SELECT text_col,
            id_number
        INTO v_col1,
            v_col2
        FROM my_table;

    v_text := column1;

    APEX_UTIL.SET_SESSION_STATE('P1_ITEM_NUMBER_'|| v_initial, v_text);
    END LOOP;
END;

Text_column is a varchar2 col and id_number is just an ordered sequence (starts with one).

Thanks

Answer 1

That piece of code is invalid; not that it won't affect session state, it won't even compile because

• you're selecting into variables that aren't declared
• v_text is being set to column1 which is also not declared
• another possible problem is no_data_found or too_many_rows exception raised by select statement unless it contains exactly one row; there's no filter there

Maybe you meant to do something like this?

begin
  for cur_r in (select id_number, text_col from my_table) loop
    apex_util.set_session_state('P1_ITEM_NUMBER_' || cur_r.id_number,
                                 cur_r.text_col);
  end loop;
end;