APEX 4.1 - Populate DB based on right panel of shuttle

Question

APEX version: 4.1.1.00.23

I have a shuttle on a page, and when I move item(s) to the right panel from the left, I want to update a table in the database with what is in a select list.

In this case, when I select 'Analyst_1' from the drop down, it will populate the right side based on javascript.

DB table (before clicking button):
Field                          Analyst
Co-Borrower Credit Score       Analyst_1
Appraised Value                (null)
Appraisal Identifier           (null)

Then, after I move some items from the left panel to the right panel and click 'Apply Changes', I want 'Analyst_1' to be put in the analyst field on the DB for each of the field names on the right panel.

DB table (after clicking button):
Field                          Analyst
Co-Borrower Credit Score       Analyst_1
Appraised Value                Analyst_1
Appraisal Identifier           Analyst_1

Here is my code for when the button 'Apply Changes' is clicked.

Update Data_Table set ANALYST_NAME = :P51_ANALYST where FIELD = :P51_SHUTTLE_RIGHT;

Answer 1

Shuttle values are submitted to session state in a colon-separated string. For instance 'VALUE1:VALUE2:VALUE4:VALUE7'. Obviously that is not going work. You can deal with this in several ways of course. One is to use the string in an IN clause and split it into several elements

UPDATE data_table 
   SET analyst_name = :P51_ANALYST 
 WHERE field IN (SELECT a_field
                   FROM 
                     xmltable('/root/e/text()' passing xmltype('<root><e>'
                     || REPLACE(:P51_SHUTTLE_RIGHT,':','</e><e>')
                     || '</e></root>') columns a_field VARCHAR2(50) path '/');

This is just one flavour though, and there are many other examples and ways such as using regexp or connect by. Pick whichever you're comfortable with.

Another way is to loop over all values in the string with PLSQL code. You could use APEX_UTIL.STRING_TO_TABLE for this. Again, any flavour you like (eg INSTR+SUBSTR).

DECLARE
    l_vc_arr2    APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
    l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE('One:Two:Three');
    FOR z IN 1..l_vc_arr2.count LOOP
        htp.p(l_vc_arr2(z));
    END LOOP;
END;

Answer 2

I used the following code to get the DB to update the Analyst (select list) based ONLY on the items in the right panel to work.

NOTE: This only updated the DB based on the right panel. If you move something from the right panel, to the left panel, it will still appear on the right panel after the page is submitted.

Create page process (PL/SQL anonymous block) after submit:

declare
    tab apex_application_global.vc_arr2;
    l_count number;
begin
    tab := apex_util.string_to_table (:P51_SHUTTLE);
    for i in 1..tab.count 
    loop
    select count(*) into l_count from DQ_MANUAL_EDIT WHERE DQ_ATTRIBUTE = tab(i);
     if l_count > 0 then 
       UPDATE DQ_MANUAL_EDIT
       SET DQ_ANALYST = :P51_DQ_ANALYST
       WHERE DQ_ATTRIBUTE = tab(i);
    end if;
    end loop;
end;