13 Replies Latest reply on Jan 26, 2014 3:49 PM by RWErene81

    Multi row "insert into" from Tabular Form in a other table

    RWErene81

      Hi,

       

      i have create a tabular form with row selector. At default, there are button like delete or update....but how can i create a buttun (and process) where the selected rows insert into in a other table?

       

      I know how i can create an insert, but id dont now how i can get the rows wich are selected.

       

      please help me,

       

      thx

      René

        • 1. Re: Multi row "insert into" from Tabular Form in a other table
          vincent_deelen

          Hi Rene,

           

          Do you mean you have a tabular form on table x and you want to insert selected rows into table y?

          If so, you can try writing an insert routine based on this old blogpost:

          http://vincentdeelen.blogspot.com/2013/06/custom-multi-row-processing-using.html

           

          Regards,

          Vincent Deelen

          http://vincentdeelen.blogspot.com

          1 person found this helpful
          • 2. Re: Multi row "insert into" from Tabular Form in a other table
            RWErene81

            Hi,

             

            thanks Vincent Deelen. Perfekt....But, it don't worked in my application.

             

            What i do:

             

            1. Hidden Item (default)

            Name: P600_ID

             

             

            2. create dynamic Action:

             

            var rowS = $('.uReportStandard input[type=checkbox]:checked').map(
                                   function() {
                                                return $(this).parent().parent().find('td[headers="NUMMER_EINHEITLICH"] input').val();
                                            }
                                 ).get().join(':');
            $('#P600_ID').val(rowS);
            

            (NUMMER_EINHEITLICH is a column of my Table x)

             

            3. Create Button (default)

            Name: Insert_into

             

            4. Create Process (default):

            DECLARE
               l_ID_arr    APEX_APPLICATION_GLOBAL.VC_ARR2;
            BEGIN
               l_ID_arr := APEX_UTIL.STRING_TO_TABLE(:P600_ID);
               FOR i IN 1..l_ID_arr.count   LOOP
                 INSERT INTO TABLE_Y(
                    column a,
                    column b,
                    column c,
                    column d,
                    column e
            )
            SELECT * TABLE_X
            WHERE NUMMER_EINHEITLICH = l_ID_arr(i);
               END LOOP;
            END;
            

            So....i dont know where the mistake is.

             

            Question: i dont have a 'join' between the Button and the process. Is it right?

            • 3. Re: Multi row "insert into" from Tabular Form in a other table
              vincent_deelen

              Hi Rene,

              In your Create Process, line 13, you miis the "from" keyword. But I assume that's a type error.

              Can you share the credentials of your apex.oracle example? It's hard to tell what is going wrong based on the above.

               

              Regards,

              Vincent

              http://vincentdeelen.blogspot.com

              1 person found this helpful
              • 4. Re: Multi row "insert into" from Tabular Form in a other table
                RWErene81

                Hi,

                 

                of course. The missing key word "from" is a mistake from copy it to this forum.

                 

                in my Apex example i build your example 1:1 and this dosent work too.

                 

                Workspace: TEST_RENE

                USER: rwerene@web.de

                Password: Test123!!

                Application: 9658

                 

                If you find the mistake, pleas tell me :-)

                 

                thx

                René

                • 5. Re: Multi row "insert into" from Tabular Form in a other table
                  vincent_deelen

                  Hi René,

                   

                  I got your test application working. There were a few little problems:

                  First: you use a different theme for your application, as a result the class selector for the tabular form is different ('report-standard', rather then 'uReportStandard '), that's why the javascript function didn't work. Note that you can check the class selector for your tabular form, or any other element, your browsers debug pane (f12 or hover over element, right-click and choose "inspect element" from the mouse-menu). Use a proper browser like FF or Chrome for debugging.

                  Second: in your raise_sal process you had the 'Run Process' option set to "Once PEr Session or when Reset", rather then the default "Once per Page Visit". As a result the process would only run the first time you click the raise_sal button.

                  Third: in your raise_sal process you didn't specify the "When Button Pressed" option. That way the process would run on any page submit, that means also when you press another button.

                   

                  Hope this will help you fix your original page as well.

                   

                  Regards,

                  Vincent

                  http://vincentdeelen.blogspot.com

                  1 person found this helpful
                  • 6. Re: Multi row "insert into" from Tabular Form in a other table
                    RWErene81

                    THX THX THX THX THX THX

                    Perfekt, very good.

                     

                    Tomorrow i test it in my origin Application

                     

                    Vincent, you make my day :-)

                    • 7. Re: Multi row "insert into" from Tabular Form in a other table
                      RWErene81

                      Taadaa, here i,m again...because my insert into dont work

                       

                      Can everybody look at my workspace an tell me, where the error is? I dont find the error....

                       

                      Workspace: TEST_RENE

                      USER: rwerene@web.de

                      Password: Test123!!

                      Application: 9658

                      Page 2

                       

                      THX

                      René

                      • 8. Re: Multi row "insert into" from Tabular Form in a other table
                        vincent_deelen

                        Hi René,

                        I've fixed a few things for you. Your javascript function looked like this:

                        var RowId = $('.report-standard input[type=checkbox]:checked').map(function() {
                                                                                                         return $(this).parent().parent().find('td[headers="ROWID"] input').val();
                                                                                                       }
                                       ).get().join(':');
                        
                        
                           $('#P2_ID).val(empNoS);

                         

                        Problem here is that your report is display only, so there are no input items. Therefore "...input').val();" wont work. Also you started with declaring a variable called RowId, but you're assigning the value of variable EmpNoS to your page item. That won't work either, Last, you forgot a quote a in the $('#P2_ID) part.

                        I changed your function to:

                        var RowId = $('.report-standard input[type=checkbox]:checked').map(function() {
                                                                                                         return $(this).parent().parent().find('td[headers="ROWID"]').text();
                                                                                                       }
                                       ).get().join(':');
                        
                        
                           $('#P2_ID').val(RowId);
                        

                         

                        Another problem is that you don't have a primary key value in your report, instead you use the rowid. This is not bad per se, but you should add the rowid column to your report: the javascript function relies on it. I've added the ROWID to your report, so now that part works, but it doesn't look nice. I'd suggest you adda primary key column to your tables, which you can include in your report. Alternatively you can add a javascript function that hides your rowid column on runtime, but that's not really fancy either

                         

                        There is still a problem with your insert proces that I can't find out what it is, you'd have to check that in something like sql developer or toad.

                         

                        Regards,

                        Vincent

                        http://vincentdeelen.blogspot.com

                        1 person found this helpful
                        • 9. Re: Multi row "insert into" from Tabular Form in a other table
                          RWErene81

                          Perfekt,

                           

                          the error with the insert was an problem with a date field.

                           

                          1. Thx

                          2. Thx

                          3. Thx

                          .

                          .

                          .

                          N. THX

                           

                          But now i have Problem...mhm...perherps you can help me again.

                           

                          1. The source isn't a table, it is a view.

                          2. The Column 'Kommentar' in the view ist not a Column from the source table, this column is generate if i open the view (i dont know how i can call this)

                          3. The column is a "Text field" in the form

                          4. If i give an Input in this field an select it with the row selector the mulit row update wrote it in the target table, but not in the source table

                           

                          Is this possible?

                           

                          THX René

                          • 10. Re: Multi row "insert into" from Tabular Form in a other table
                            RWErene81

                            Hi,

                             

                            i have a additional question:

                             

                            How i have to change the Code from the dynamic Action and from the process if my primary Key is a combined key of two columns?

                             

                            Dynamic Action original

                             

                            varempNoS=$('.uReportStandard input[type=checkbox]:checked').map(
                                                   function() {
                                                                return $(this).parent().parent().find('td[headers="EMPNO"] input').val(); // I think here is the place where the combined key is
                                                              }
                                                 ).get().join(':');
                            $('#P1_EMPNOS').val(empNoS);
                            

                             

                             

                            and the original prosecc

                             

                            DECLARE
                               l_empno_arr    APEX_APPLICATION_GLOBAL.VC_ARR2;
                            BEGIN
                               l_empno_arr := APEX_UTIL.STRING_TO_TABLE(:p1_empnos);
                               FOR i IN 1..l_empno_arr.count
                               LOOP
                                  update  emp
                                  set   sal = sal*1.1
                                  where empno = l_empno_arr(i);  -- I think here is the place for the combined primary key
                               END LOOP;
                            END;
                            

                             

                             

                            THX

                            René

                            • 11. Re: Multi row "insert into" from Tabular Form in a other table
                              vincent_deelen

                              Hi René,

                               

                              You should be able to use a compound primary key. Simplest thing, I think, would be to create a page item for each column of your pk and do the javascript mapping function for each column, eg:

                              var pkCol1 = $('.uReportStandard input[type=checkbox]:checked').map(function() {
                                                                                                               return $(this).parent().parent().find('td[headers="PK_COL1"] input').val();
                                                                                                             }
                                             ).get().join(':');
                              
                              
                                 $('#P1_PK_COL1').val(pkCol1);
                              
                              var pkCol2 = $('.uReportStandard input[type=checkbox]:checked').map(function() {
                                                                                                               return $(this).parent().parent().find('td[headers="PK_COL2"] input').val();
                                                                                                             }
                                             ).get().join(':');
                              
                              
                                 $('#P1_PK_COL2').val(pkCol2);
                              
                              
                              
                              
                              
                              
                              

                              where you obviously need to replace PK_COL1 and PK_COL2 with the actual name of your primary key columns.

                               

                              The pl/sql process will look something like:

                              DECLARE
                                     l_pk1_arr    APEX_APPLICATION_GLOBAL.VC_ARR2;
                                     l_pk2_arr    APEX_APPLICATION_GLOBAL.VC_ARR2;
                              BEGIN
                                    l_pk1_arr := APEX_UTIL.STRING_TO_TABLE(:p1_pk_col1);
                                   l_pk2_arr := APEX_UTIL.STRING_TO_TABLE(:p1_pk_col2);
                                     FOR i IN 1..l_pk_col1_arr.count LOOP
                                       update  emp
                                         set   sal = sal*1.1
                                         where pk_col1 = l_pk_col1_arr(i)
                              and pk_col2 = l_pk_col2_arr(i);
                                     END LOOP;
                              END;
                              
                              

                               

                              Regarding you previous question with the added comments field: I can't think of a proper solutiion. You can try to include the comments field in your view query as an apex_item, eg:

                              create or replace view my_view

                              as

                              select col1

                              ,          col2

                              , ....

                              , apex_item.text(p_idx => 1) as comments

                              from my_table

                              ;

                              Please read the documentation on apex_item for more details on how to use: http://docs.oracle.com/cd/E17556_01/doc/apirefs.40/e15519/apex_item.htm#CHDCDHJI

                              With the comments field as part of your view, you should be able to include it as a normal column in your insert statement.

                              Hopefully it works, because I've never tried it before

                               

                              Regards,

                              Vincent

                              http://vincentdeelen.blogspot.com

                              1 person found this helpful
                              • 12. Re: Multi row "insert into" from Tabular Form in a other table
                                RWErene81

                                Hey,

                                 

                                thanks for it, i test it an give you feedback as soon as possinle.

                                 

                                Lg

                                René

                                • 13. Re: Multi row "insert into" from Tabular Form in a other table
                                  RWErene81

                                  Hi,

                                   

                                  i have test it, but my solution don,t go.

                                   

                                  I use the Apex items but not Javascript/ JQuerry.

                                   

                                  My process is like

                                  FOR I in 1..APEX_APPLICATION.G_F01.COUNT LOOP
                                      insert into PRM_CHK_BSTBS_ABGL_EDIT
                                          (
                                              STICHTAG,
                                  .
                                  .
                                  .
                                              Kommentar -- this is not a column from he source table
                                          )
                                      VALUES
                                      Select    *
                                      From    PRM_CHK_BSTBS_ABGL
                                      WHERE    NUMMER_EINHEITLICH = to_number(APEX_APPLICATION.G_F01(i));
                                  Select    *
                                      From    PRM_CHK_BSTBS_ABGL
                                  
                                  
                                  
                                  
                                  WHERE    NUMMER_EINHEITLICH = to_number(APEX_APPLICATION.G_F01(i));
                                  
                                  
                                  
                                  END LOOP;
                                  

                                   

                                  And i try in the select

                                   

                                  select

                                  f02, f03, .....f16

                                  from xyz

                                   

                                  But this dosn't work.

                                   

                                  Anyone an Idea?

                                   

                                  THX

                                  René