1 2 Previous Next 21 Replies Latest reply: Jul 3, 2008 3:20 PM by Marc Sewtz-Oracle RSS

    Tabular Form to insert multiple rows at once

    523585
      Reading the forum I see I should use a tabular form to create an insert page to create multiple records at once. However is there a way to do this without loading records from the table so it will just be a plain create page?

      Any help is appreciated, thank you
        • 1. Re: Tabular Form to insert multiple rows at once
          514908
          Hi,

          why not make your query return no rows?

          where clause:
          where ....
          and 1 = 2



          Louis-Guillaume
          • 2. Re: Tabular Form to insert multiple rows at once
            523585
            thanks for the tip I don't know why I didn't think of that. This works but when I try to add a row, all of the fields say null inside them and will not let me add values.
            • 3. Re: Tabular Form to insert multiple rows at once
              629628
              Look up using collections. You can then use the apex_collection.add_member function to add blank rows.
              • 4. Re: Tabular Form to insert multiple rows at once
                Marc Sewtz-Oracle
                The wizard generated tabular forms come with an "Add Rows" process, which by default adds one empty new row when clicking on "Add Row". This process can be edited to add more than one empty row, just specify the number of empty rows you'd like to add each time.

                Regards,
                Marc
                • 5. Re: Tabular Form to insert multiple rows at once
                  523585
                  Thanks for the tip. I tried your suggestion already and it did work, but what I need is for the blank rows to be there on page load, not after I click the 'Add Row' button.

                  Also when I click the Add Row button, I can only add the number of rows one time. For instance if I set it to add 5 rows, then if I click the button it will add 5 rows one time and after that the button is useless.


                  I tried using collections to create blank rows which does work but the outcome is the same unless I am doing something completely wrong here.

                  Any suggestions?
                  • 6. Re: Tabular Form to insert multiple rows at once
                    Marc Sewtz-Oracle
                    If you want to load the page with x empty rows already in place, you would be best of using collections, in this case though, you'll have to write your own process that transfers that data from the collections table into your actual data tables.

                    Regards,
                    Marc
                    • 7. Re: Tabular Form to insert multiple rows at once
                      523585
                      Marc,

                      That is where I am stuck, I have very little experience with application express as I have only been using it for a few days now. Any tips?
                      • 8. Re: Tabular Form to insert multiple rows at once
                        Marc Sewtz-Oracle
                        This is a frequently discussed subject on the forum, so I'd suggest looking around and visiting some of the many Blogs written by the APEX community. I also can highly recommend Denes Kubicek's sample application, he has many useful examples available there, here's one on manual tabular forms:

                        http://htmldb.oracle.com/pls/otn/f?p=31517:170

                        Regards,
                        Marc
                        • 9. Re: Tabular Form to insert multiple rows at once
                          523585
                          Thanks Marc,

                          I do agree this topic is frequently discussed and I have searched this forum and have already been to Denes Kubicek's sample application site. I just figured that since this topic seems to be so popular, somebody would have created some sort of tutorial on this by now. Or maybe I am just too inexperienced.

                          I'll keep playing around with it and see what happens
                          • 10. Re: Tabular Form to insert multiple rows at once
                            Marc Sewtz-Oracle
                            A very old but still useful tutorial can be found here:

                            http://www.oracle.com/technology/products/database/application_express/howtos/tabular_form.html

                            Regards,
                            Marc
                            • 11. Re: Tabular Form to insert multiple rows at once
                              523585
                              Hi Marc,

                              That tutorial was pretty useful. And I have it somewhat working.

                              Right now I have it auto displaying blank rows by default which is good and my collection is being created and everything. However, where I am having the problem now is updating the collection with the values from each field.
                              Here is my script I am using:

                              declare
                              c pls_integer := 0;
                              begin

                              for c1 in (
                              select seq_id from apex_collections
                              where collection_name = 'INSERT_BLANK'
                              order by seq_id)
                              loop
                              c := c+1;

                              if apex_collection.collection_exists(p_collection_name => 'INSERT_BLANK') then
                              for i in 1..apex_application.g_f01.count
                              loop
                              APEX_COLLECTION.UPDATE_MEMBER(
                              p_collection_name => 'INSERT_BLANK',
                              p_seq => c1.seq_id,
                              p_c001 => nvl(apex_application.g_f01(c),''),
                              p_c002 => nvl(apex_application.g_f02(c),''),
                              p_c003 => nvl(apex_application.g_f03(c),''),
                              p_c004 => nvl(apex_application.g_f04(c),''),
                              p_c005 => nvl(apex_application.g_f05(c),''),
                              p_c006 => nvl(apex_application.g_f06(c),''),
                              p_c007 => nvl(apex_application.g_f07(c),''));

                              end loop;
                              end if;
                              end loop;
                              end


                              I made a process On Submit Before Computation with this and another one that inserts the data in to the table:

                              for i in 1..apex_application.g_f01.count
                              loop
                              if apex_application.g_f02(i) is not null then
                              insert into pinfo_t
                              (id,
                              name,
                              start_date,
                              end_date,
                              value,
                              database_id,
                              host_id)
                              values
                              (apex_application.g_f02(i),
                              apex_application.g_f03(i),
                              apex_application.g_f04(i),
                              apex_application.g_f06(i),
                              apex_application.g_f07(i),
                              apex_application.g_f08(i),
                              apex_application.g_f09(i));
                              end if;
                              end loop;


                              It keeps coming back with "ORA-01403: no data found "

                              Any ideas?
                              • 12. Re: Tabular Form to insert multiple rows at once
                                Marc Sewtz-Oracle
                                You get an no-data-found error when trying to reference an array value with an invalid index. Have you checked your HTML source that you actually have arrays f01 - f07 defined? And is any of your arrays based on a checkbox column? Checkboxes work differently from other items.

                                Regards,
                                Marc
                                • 13. Re: Tabular Form to insert multiple rows at once
                                  523585
                                  Hi Marc,

                                  I am not using check boxes in any columns.

                                  So in order for me to use f01 - f07 I need to predefine these? Can you tell me where I can find an example of how to do this?

                                  I think that is the problem I am having.
                                  • 14. Re: Tabular Form to insert multiple rows at once
                                    Marc Sewtz-Oracle
                                    f01-f07 are the names of the arrays you reference in your code. In order for them to have values, they need to be part of your tabular form. So how did you create that tabular form? Using a wizard? Using apex_item API calls? Manually changing column from display-only to updateable? Whichever method you used, each updateable column corresponds to one f0x arrays. Non-updateable columns / display-only have no array associated and are not send to the server.

                                    Regards,
                                    Marc
                                    1 2 Previous Next