6 Replies Latest reply on Jan 29, 2019 12:48 AM by LA County APEX

    Update records in a table from Apex_Collection does not work

    rober584812

      Update a collection of Apex is a big problem, investigate in some threads of this community and in tutorials and has not served me anything.

      The new data that is entered is not updated in the database.

      I expose in detail how I generated the tabular form:

       

      1) Create the Collection.

      Captura20190112095633.png

      IF apex_collection.collection_exists ('NOMINA_ESTUDIANTES_COLL') = FALSE THEN

      apex_collection.create_collection_from_query(

           p_collection_name => 'NOMINA_ESTUDIANTES_COLL',

           p_query => 'select NUMERO_matricula alias1,

                              periodo alias2,

                              alumno_id alias3,

                              apellidos alias4,

                              nombres alias5,

                              curso_id alias6,

                              paralelo alias7,

                              nivel_id alias8,

                              subnivel_id alias9,

                              asignatura_id alias10,

                              tai1 as tai1,

                              wwv_flow_item.md5(NUMERO_matricula,periodo,alumno_id, apellidos, nombres,curso_id,paralelo,nivel_id,subnivel_id,asignatura_id,tai1)

                       from reporte_parcial where periodo=2018   /* and nivel_id=300 and curso_id=3 and subnivel_id=14*/',

           p_generate_md5 => 'YES');

       

      END IF;

      Captura20190112114345.png

       

       

      2) Generate the report.

       

      SELECT

           apex_item.DISPLAY_AND_SAVE(1,c001,8)as NUMERO_matricula

           ,apex_item.DISPLAY_AND_SAVE(2,c002,8)as periodo

           ,apex_item.DISPLAY_AND_SAVE(3,c003) as alumno_id

           ,apex_item.DISPLAY_AND_SAVE(4,c004,8) as APELLIDOS

           ,apex_item.DISPLAY_AND_SAVE(5,c005,8)as  NOMBRES

           ,apex_item.DISPLAY_AND_SAVE(6,c006,8)as CURSO

           ,apex_item.hidden(7,c007,8)as PARALELO

           ,apex_item.text(8, c010)as  TAI

      FROM apex_collections

      WHERE collection_name= 'NOMINA_ESTUDIANTES_COLL' and c002=2018 and c008=300 and c009=14 and c006=3 /*and c006=:P7_CURSO

        AND c002=:P7_PERIODO*/  AND C002='2018' and c010=7

      ORDER BY APELLIDOS,

             NOMBRES;

       

      3) Encode the process of updating the collection.

      Captura20190112095555.png

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

          apex_debug_message.log_message(x);

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

          apex_debug_message.log_message(x);

          apex_collection.update_member_attribute(p_collection_name=>'NOMINA_ESTUDIANTES_COLL',

                                         p_seq=>apex_application.g_f01(x),

                                         p_attr_number =>1,

                                         p_attr_value=>apex_application.g_f02(x));

                                      

          apex_collection.update_member_attribute(p_collection_name=>'NOMINA_ESTUDIANTES_COLL',

                                         p_seq=>apex_application.g_f01(x),

                                         p_attr_number =>2,

                                         p_attr_value=>apex_application.g_f08(x));

      end loop;

       

      end loop;

       

       

       

      4) Create the process to update the database.

      Captura20190112113332.png

       

      BEGIN

       

         FOR  indice in 1..apex_application.g_f01.count LOOP

       

               UPDATE RENDIMIENTO_ACADEMICO3

               SET TAI1=apex_application.g_f08(indice)

               WHERE PERIODO='2018' AND NUMERO_MATRICULA=apex_application.g_f01(indice) AND ASIGNATURA_ID=7;

          

               COMMIT;

          END LOOP;

       

      END;

       

      Captura20190112113345.png

      When I press the update button, it shows the message NO DATA FOUND

      Suggestions.

        • 1. Re: Update records in a table from Apex_Collection does not work
          LA County APEX

          The p_seq and apex_application.g_f01(x) does not match.

          Try:

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

              for i in(select * from apex_collections where collection_name = 'NOMINA_ESTUDIANTES_COLL'

                          where c001 = apex_application.g_f01(x)) loop

              apex_collection.update_member_attribute(p_collection_name=>'NOMINA_ESTUDIANTES_COLL',

                                             p_seq=>i.seq,

                                             p_attr_number =>1,

                                             p_attr_value=>apex_application.g_f02(x));

                                         

              apex_collection.update_member_attribute(p_collection_name=>'NOMINA_ESTUDIANTES_COLL',

                                             p_seq=>i.seq,

                                             p_attr_number =>2,

                                             p_attr_value=>apex_application.g_f08(x));

          end loop;

          end loop;

          • 2. Re: Update records in a table from Apex_Collection does not work
            Mike Kutz

            I cheat.

             

            I have a template based code generator that creates all the magic code I need to create a VIEW ON COLLECTION and make it work with classic "form on table (with report)" by providing a replacement for "Automatic DML".  It also works with IG.

             

            It also like making INSERT INTO VIEW_ON_COLLECTION SELECT ..   and MERGE REAL_TABLE USING VIEW_ON_COLLECTION statements a lot easier to work with.

             

            Example result of generated code can be found here:

            Re: A "Wizard Progress List Template" to save data in a single table

             

            I have a working example on apex.oracle.com (but need to reset passwords to get you the correct link)

             

            Let me know what you think.

             

            MK

             

            PS - DML via DA is usually "not a good Web Design"

            • 3. Re: Update records in a table from Apex_Collection does not work
              Scott Wesley

              I like to think of it with a Forms mindset - DML was never cool in a Forms program unit, ever. It should be treated with the same mindset in APEX, just for different less reasons.

              • 4. Re: Update records in a table from Apex_Collection does not work
                Mike Kutz

                Another problem I see with the OP's process is this:  COMMIT is being called from within the LOOP.

                 

                I consider "Partial modifications" to be another "bad idea".  Last thing an end-user expects to see is "only the first to rows were modified".  Either all rows should be modified or no rows should be modified.

                 

                3rd problem I see (or don't see):  The Report needs to be refreshed after the DML operation.  This is implicitly handled via "Process on submit" call  (instead of a DA)

                 

                4th problem:  possible "lost update" due to "multi-user environment"

                There is no "lock row then check row's hash value" process.  That means, you don't know if someone has changed the data since you looked at it.

                 

                The general process seems sound:

                1. copy data to APEX Collection
                2. modify data in APEX Collection
                3. refresh report (might be missing but is implied via "Submit")
                4. update/cancel data per end-user's request. (OP's "Update" is being done against the Form, not APEX Collection)

                 

                The individual steps (that the OP posted) could use some tweaks.

                 

                MK

                • 5. Re: Update records in a table from Apex_Collection does not work
                  rober584812

                  LA County APEX wrote:

                   

                  The p_seq and apex_application.g_f01(x) does not match.

                  Try:

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

                  for i in(select * from apex_collections where collection_name = 'NOMINA_ESTUDIANTES_COLL'

                  where c001 = apex_application.g_f01(x)) loop

                  apex_collection.update_member_attribute(p_collection_name=>'NOMINA_ESTUDIANTES_COLL',

                  p_seq=>i.seq,

                  p_attr_number =>1,

                  p_attr_value=>apex_application.g_f02(x));

                   

                  apex_collection.update_member_attribute(p_collection_name=>'NOMINA_ESTUDIANTES_COLL',

                  p_seq=>i.seq,

                  p_attr_number =>2,

                  p_attr_value=>apex_application.g_f08(x));

                  end loop;

                  end loop;

                  I solved the problem by referencing the collection with Apex_application

                   

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

                   

                      apex_collection.update_member_attribute(p_collection_name=>'NOMINA_ESTUDIANTES_COLL',

                                                     p_seq=>x,

                                                     p_attr_number =>1,

                                                     p_attr_value=>apex_application.g_f02(x));

                                              

                      apex_collection.update_member_attribute(p_collection_name=>'NOMINA_ESTUDIANTES_COLL',

                                                     p_seq=>x,

                                                     p_attr_number =>2,

                                                     p_attr_value=>apex_application.g_f08(x));

                   

                  end loop;

                   

                  However, although it  the solved problem, when import from my environment Apex 18.2 in Linux CentOS 7 to Apex 18.2 in Windows 10 and execute  the application, at update the collection show the message:

                  No Data Found.

                  How it possible?

                  • 6. Re: Update records in a table from Apex_Collection does not work
                    LA County APEX

                    You need to fetch the seq from the collection (do not use the x value to update the collection).