4 Replies Latest reply: Sep 20, 2013 4:23 PM by Sira RSS

    How to load data from different table but save it to another table?

    T101_cyberdyne

      Hi all,

       

      i've been using apex and i understand how it updates and saves records normally.

      For this case, i would need to load from a table but save it to another table upon submit button.

      I'm aware that it would be easy like forms where i could do a manual insert process. im doing it in tabular forms and i dont really know how to manipulate this process.

      suppose that i'm loading a set of questionnaires on the tabular form and have a yes/no selection.

      so the questions would come from table A and i would need to save it to table B.

      in this case i cant just set default value for the each row of tabular for the questions coz it would be dynamic and this example is on a small scale. i would need it to load from table A and save to table B.

      could anyone guide me on doing this or provide any tutorials of some sort?

        • 1. Re: How to load data from different table but save it to another table?
          JPL_12

          What making a view on table A, and doing the updates via an 'instead-of' trigger?

          Make sure that your view is key-preserving (the primary key on table A should be in the view, and still be a unique record identifier).

           

          Just wondering, how this would look for your users, if the submitted values are not visible in table A because you're updating table B..

          • 2. Re: How to load data from different table but save it to another table?
            Epic Fail

            Maybe this is what you are after.

            1) Create report region using SQL similar to this:

            select apex_item.hidden(1,rownum) ||

                   apex_item.display_and_save(2,question_column) as Question,

                   apex_item.text(3,null,4,4) as response

            from   table_a

             

             

            apex_item package documentation found:

            http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_item.htm

             

            The pidx parameter (first number) in every apex_item is used to identify that element in that row of the "report"/tabular form.

             

             

            2)  Create a on submit procedure kinda like this.

            begin

              for i in 1..apex_application.g_f01.count loop  -- identifies the "apex_item.hidden(1,rownum)" from above and gets a count of how many times it occurs

                  insert into table_b(question, response)

                               values( apex_application.g_f02(i), apex_application.g_f03(i) );

              end loop;

            end;

            • 3. Re: How to load data from different table but save it to another table?
              T101_cyberdyne

              That's what i ended up doing. thanks!

              • 4. Re: How to load data from different table but save it to another table?
                Sira

                Hi,

                 

                I am new to Apex and pl/sql. I need to create a similar application where I need to load from a table A(which has default values) into a tabular form,which user should be able to edit and save it to table B upon clicking the submit button.

                 

                I could create a report region using sql as "EpicFail" had mentioned in Part 1.

                But I am not able to create a submit button (based on a dynamic action) as mentioned in Part 2. When user clicks submit, action is to insert values from Apex_items into table B using Arrays(this is what i understood from Part 2) . I am not sure how Apex arrays work. Can someone explain or point me in the right direction.

                 

                This is the code I am trying to execute as suggested in Part 2 by "EpicFail"

                begin

                  for i in 1..apex_application.g_f01.count loop 

                      insert into table B(product_id, weights,seq,action)

                                   values(apex_application.g_f03(i), apex_application.g_f04(i), apex_application.g_f05(i), apex_application.g_f06(i)) ;

                  end loop;

                end;

                 

                Also table B has a primary key populated from a sequence. In the insert statement of the above procedure how should i refer to this sequence.

                 

                Any help will be greatly appreciated.