This discussion is archived
6 Replies Latest reply: Nov 16, 2012 8:06 AM by 596740 RSS

APEX 4.1 - Populate DB based on right panel of shuttle

596740 Newbie
Currently Being Moderated
APEX version: 4.1.1.00.23
Oracle 11g

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.

[Screen Shot 1|http://i.stack.imgur.com/WhB6h.jpg]

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.

[Screen shot 2|http://i.stack.imgur.com/nMSvU.jpg]

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 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 '/'));
Edited by: Sid_244 on Nov 14, 2012 12:35 PM

Edited by: Sid_244 on Nov 14, 2012 12:37 PM
  • 1. Re: APEX 4.1 - Populate DB based on right panel of shuttle
    714270 Pro
    Currently Being Moderated
    Hi,

    If you just want to use the individual elements in the shuttle (right panel) for DB operations, you can use the following:
    DECLARE
     
    l_selected HTMLDB_APPLICATION_GLOBAL.VC_ARR2;
     
    BEGIN
     
     
      l_selected := HTMLDB_UTIL.STRING_TO_TABLE(:P51_SHUTTLE);
      FOR i IN 1..l_selected.count 
      LOOP
            //  your code here...access individual elements using  l_selected(i)
      END LOOP;
     
    END;
    Thanks,
    Rohit
  • 2. Re: APEX 4.1 - Populate DB based on right panel of shuttle
    596740 Newbie
    Currently Being Moderated
    Rohit -

    I tried your example, but when I click the 'Apply Changes' button, the DB table removes all of the items that are associated with the analyst in the select list. So if there was anything in the right panel or items that I moved over to the right panel, the analyst is removed from their attribute.

    Here is an example of what I am trying to accomplish:

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

    Here is the code that I ran:
    DECLARE
     
    l_selected HTMLDB_APPLICATION_GLOBAL.VC_ARR2;
     
    BEGIN
     
     
      l_selected := HTMLDB_UTIL.STRING_TO_TABLE(:P51_SHUTTLE);
      FOR i IN 1..l_selected.count 
      LOOP
            UPDATE DQ_MANUAL_EDIT
              SET DQ_ANALYST = :P51_ANALYST
              WHERE DQ_ATTRIBUTE in (l_selected(i));
      END LOOP;
     
    END;
    Other failed codes:
    declare  
     v_count number;
    begin
     --
     -- First check if the
    
     select count(*) into v_count
    from DQ_MANUAL_EDIT
     where dq_attribute = :P51_SHUTTLE_RIGHT
     and :P51_SHUTTLE_RIGHT is not null;
    
     if v_count > 0 then
    
      --
      -- If it exists then save it
      --
      update DQ_MANUAL_EDIT
      set DQ_ANALYST = :P51_DQ_ANALYST
      where DQ_ATTRIBUTE = :P51_SHUTTLE_RIGHT;
      
    
     else
    
      --
      -- Else insert a new record into the table
      --
      insert into DQ_MANUAL_EDIT
      (DQ_ANALYST, DQ_ATTRIBUTE)
      values
      (:P51_DQ_ANALYST, :P51_SHUTTLE_RIGHT);
    
     end if;
    
    end;
    --Test #2
    declare
        tab apex_application_global.vc_arr2;
    begin
        tab := apex_util.string_to_table (:P51_SHUTTLE_RIGHT);
        for i in 1..tab.count loop
            UPDATE DQ_MANUAL_EDIT
              SET DQ_ANALYST = :51_ANALYST
              WHERE DQ_ATTRIBUTE = :P51_SHUTTLE_RIGHT;
        end loop;
    end;
    --Test #3
    declare
        tab apex_application_global.vc_arr2;
    begin
        tab := apex_util.string_to_table (:P51_SHUTTLE_RIGHT);
        for i in 1..tab.count 
        loop
           UPDATE DQ_MANUAL_EDIT
           SET DQ_ANALYST = :51_ANALYST
           WHERE DQ_ATTRIBUTE = tab(i);
        end loop;
    end;
    --Test #4
    DECLARE
    var_test  VARCHAR2(2000):= NULL ;
    begin
    SELECT a_field into var_test
                       FROM 
                         xmltable('/root/e/text()' passing xmltype('<root><e>'
                         || REPLACE(:P51_SHUTTLE_RIGHT,':','</e><e>')
                         || '</e></root>') columns a_field VARCHAR2(50) path '/');
    end;
  • 3. Re: APEX 4.1 - Populate DB based on right panel of shuttle
    714270 Pro
    Currently Being Moderated
    Hi,

    I am not sure about the structure of your table - DQ_MANUAL_EDIT, but I assume that you will have multiple records for the same DQ_ANALYST with different attributes.
    Can't you try this?:
    // clear all records from DB for the selected analyst
    
    delete from  DQ_MANUAL_EDIT where DQ_ANALYST = :P51_ANALYST;
    
    FOR i IN 1..l_selected.count 
      LOOP
            insert into DQ_MANUAL_EDIT (DQ_ANALYST, DQ_ATTRIBUTE)
            values (:P51_ANALYST, l_selected(i) );
      END LOOP;
    BTW, I did not see any page item with the name P51_ANALYST; should it be P51_DQ_ANALYST?



    Thanks,
  • 4. Re: APEX 4.1 - Populate DB based on right panel of shuttle
    Jitendra Pro
    Currently Being Moderated
    Hi Sid_244 ,

    I think your test no#3 is very close.
    only you written wrong item name *:51_ANALYST* .
    Instead of *:51_ANALYST* use *:P51_DQ_ANALYST*

    //Edited your test no#3
    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);
         else
           insert into DQ_MANUAL_EDIT (DQ_ANALYST, DQ_ATTRIBUTE)
           values (:P51_DQ_ANALYST, tab(i) );
         end if;
        end loop;
    end;
    hope this will helps you,

    Regards,
    Jitendra
  • 5. Re: APEX 4.1 - Populate DB based on right panel of shuttle
    596740 Newbie
    Currently Being Moderated
    Rohit -
    You are correct the select list is 'P51_DQ_ANALYST', not P51_ANALYST.

    Jitendra -
    I am looking to update the table column only. I want to update the column 'DQ_ANALYST' with the name from the select list. Also, when I ran the code you provided, only the ID number (option value/return value) appeared on the right panel, not the text (display value).

    For example, if you choose the name 'Andrew Jones' from the select list, 'Green Grass' will appear on the right panel. If you then move over 'ATV' and 'Beads' from the left to the right panel of the shuttle, and click 'Apply Changes'. I want the process to update the database table column 'DQ_ANALYST' with 'Andrew Jones' for every record that has 'ATV' and 'Beads'.

    I can't insert or delete/insert because this page only interacts with the columns 'DQ_ANALYST' and 'DQ_ATTRIBUTE'. Inserting will work, but the only the 'DQ_ANALYST' and 'DQ_ATTRIBUTE' will have data. The rest of the columns will be null.

    Does that make sense?

    Edited by: Sid_244 on Nov 16, 2012 10:47 AM
  • 6. Re: APEX 4.1 - Populate DB based on right panel of shuttle
    596740 Newbie
    Currently Being Moderated
    I got it working!!!

    I changed the LOV shuttle code to this:
    select distinct DQ_ATTRIBUTE as display_value, DQ_ATTRIBUTE as return_value 
      from DQ_MANUAL_EDIT
      where dq_attribute not in
    (select distinct dq_attribute from dq_manual_edit where dq_analyst = :P51_DQ_ANALYST) and dq_analyst is null
     order by 1
    then using this code in the page process:
    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 to both of you for your help!!!!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points