6 Replies Latest reply: Nov 16, 2012 10:06 AM by 596740 RSS

    APEX 4.1 - Populate DB based on right panel of shuttle

    596740
      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
          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
            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
              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
                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
                  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
                    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!!!!