APEX 4.1 - Populate DB based on left panel of shuttle

Question

APEX version: 4.1.1.00.23 Example: http://apex.oracle.com/pls/apex/f?p=27554:51 login: demo pw: demo

I have a shuttle on a page, and when I move item(s) to the left panel from the right, I want to update a table column 'Analyst' in the database with 'null'

When the page loads there may or may not be items in the right panel.

enter image description here

DB table (when page loads):
Field                          Analyst
Co-Borrower Credit Score       Analyst_1
Appraised Value                Analyst_1
Appraisal Identifier           Analyst_1

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

enter image description here

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

Here is my current page process (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;

THANKS IN ADVANCE!!

Tom Tom · Accepted Answer · 2012-11-26T09:04:29

There is no access to the left side of the shuttle. The right side has the values which will be submitted. So you can only change those records by figuring out the changes between the database values and the submitted ones.
If possible, first delete all associated records and then insert the new values.

declare
    tab apex_application_global.vc_arr2;
    l_count number;
begin
    DELETE FROM DQ_MANUAL_EDIT
    WHERE DQ_ANALYST = :P51_DQ_ANALYST;

    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 
       INSERT INTO DQ_MANUAL_EDIT (DQ_ANALYST, DQ_ATTRIBUTE)
       VALUES (:P51_DQ_ANALYST, tab(i));
    end if;
    end loop;
end;

If you do not want the delete and keep the records but with NULL values, then you could first update the records to NULL

declare
    tab apex_application_global.vc_arr2;
    l_count number;
begin
    UPDATE DQ_MANUAL_EDIT
    SET DQ_ANALYST = NULL
    WHERE DQ_ANALYST = :P51_DQ_ANALYST;

    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;

That'd be the most straightforward way when you use the loop structure.

APEX 4.1 - Populate DB based on left panel of shuttle

2 Answers