14 Replies Latest reply: May 10, 2013 8:24 AM by fac586 RSS

    is it possible to update tables, using view on collection with db package?

    KarenH
      I am wondering if the following will work....as so far, I cannot seem to get it right.

      I have a complicated apex 4.2 tabluar form based on a collection. The collection was becoming difficult to debug with all the c001, c002 columns and so we have created a view based on the collection:
      CREATE OR REPLACE VIEW landings_collection_view (
         seq_id,
         landing_seq,
         species_itis,
         grade_code,
         market_code,
         unit_measure,
         disposition_code,
         gear_code,
         reported_quantity,
         price,
         dollars,
         c013,
         c014,
         additional_measure_flag,
         addnl_reported_quantity,
         addnl_unit_quantity,
         fins_flag,
         finsattached,
         finsnotattached,
         finsunknown,
         fins_code,
         area_fished,
         sub_area_fished,
         local_area_code,
         explanation,
         nature_of_sale,
         hms_area_code,
         sale_price,
         hms_flag )
      AS
      SELECT
      seq_id,
      c003 landing_seq,
      c004 species_itis, 
      c005 grade_code,
      c006 market_code, 
      c007 unit_measure,
      c008 disposition_code,
      c009 gear_code,
      c010 reported_quantity,
      c011 price,
      c012 dollars,
      c013,
      c014 ,
      c017 additional_measure_flag,
      c018 addnl_reported_quantity,
      c019 addnl_unit_quantity,
      c020 fins_flag,
      c021 finsattached,
      c022 finsnotattached,
      c023 finsunknown,
      c024 fins_code,
      c025 area_fished, 
      c026 sub_area_fished,
      c027 local_area_code,
      c028 explanation,
      c029 nature_of_sale,
      c040 hms_area_code,
      c041 sale_price,
      c050 hms_flag
      from apex_collections
       where collection_name = 'SPECIES_COLLECTION'
      I next created an apex page with a tabular form based on the following query from the view:
      select 
      "ROWID",
      "SEQ_ID",
      "LANDING_SEQ",
      "SPECIES_ITIS",
      "GRADE_CODE",
      "MARKET_CODE",
      "UNIT_MEASURE",
      "DISPOSITION_CODE",
      "GEAR_CODE",
      "REPORTED_QUANTITY",
      "PRICE",
      "DOLLARS",
      "C013",
      "C014",
      "ADDITIONAL_MEASURE_FLAG",
      "ADDNL_REPORTED_QUANTITY",
      "ADDNL_UNIT_QUANTITY",
      "FINS_FLAG",
      "FINSATTACHED",
      "FINSNOTATTACHED",
      "FINSUNKNOWN",
      "FINS_CODE",
      "AREA_FISHED",
      "SUB_AREA_FISHED",
      "LOCAL_AREA_CODE",
      "EXPLANATION",
      "NATURE_OF_SALE",
      "HMS_AREA_CODE",
      "SALE_PRICE",
      "HMS_FLAG"
      from "#OWNER#"."LANDINGS_COLLECTION_VIEW"
      I am updating the field PRICE and when I hit the submit buttong, the following process is called:
      electronic_dealer_report.update_edr(:P110_DEALER_RPT_ID);
      that db procedure is:
      -- Start of DDL Script for Package Body SAFIS.ELECTRONIC_DEALER_REPORT
      -- Generated 09-May-2013 11:14:38 from SAFIS@SAFISD.world
      
      CREATE OR REPLACE 
      PACKAGE BODY electronic_dealer_report
      IS
      --
      -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
      -- directory of SQL Navigator
      --
      -- Purpose: Briefly explain the functionality of the package body
      --
      -- MODIFICATION HISTORY
      -- Person      Date    Comments
      -- ---------   ------  ------------------------------------------
         -- Enter procedure, function bodies as shown below
      
         PROCEDURE update_edr
          ( v_dealerreportid IN number  )
          IS
      
         BEGIN
      
      
             FOR rec IN (SELECT c.landing_seq, c.species_itis, c.grade_code, c.market_code, c.unit_measure, c.disposition_code, c.gear_code, c.reported_quantity, c.price,
                               c.dollars, c013, c014,  c.additional_measure_flag, c.addnl_reported_quantity, c.addnl_unit_quantity, c.fins_flag,
                               c.fins_code, c.area_fished, c.sub_area_fished, c.local_area_code,c.explanation,c.nature_of_sale,c.hms_area_code,c.sale_price
                          FROM landings_collection_view c )
            LOOP
               IF rec.landing_seq IS NOT NULL THEN
                  UPDATE landings
                     SET unit_measure = rec.unit_measure,
                         reported_quantity = rec.reported_quantity,
                         dollars = rec.dollars,
                         disposition_code = rec.disposition_code,
                         grade_code = rec.grade_code,
                         species_itis = rec.species_itis,
                         market_code = rec.market_code,
                         price = rec.price,
                         gear_code = rec.gear_code,
                         area_fished = rec.area_fished,
                         sub_area_fished = rec.sub_area_fished,
                         local_area_code = rec.local_area_code
                   WHERE dealer_rpt_id = v_dealerreportid AND
                         landing_seq = rec.landing_seq;
      
                  IF rec.additional_measure_flag = 'Y' THEN
                     INSERT INTO landings_addl_measure
                                 (dealer_rpt_id,
                                  landing_seq,
                                  unit_measure,
                                  reported_quantity)
                          VALUES (v_dealerreportid,
                                  rec.landing_seq,
                                  rec.addnl_unit_quantity,
                                  rec.addnl_reported_quantity);
                   END IF;
      
                  IF rec.fins_flag = 'Y' THEN
                     INSERT INTO landings_hms
                                 (dealer_rpt_id,
                                  landing_seq,
                                  fins_attached,
                                  explanation,
                                  nature_of_sale,
                                  HMS_AREA_CODE,
                                  SALE_PRICE)
                          VALUES (v_dealerreportid,
                                  rec.landing_seq,
                                  rec.fins_code,
                                  rec.explanation,
                                  rec.nature_of_sale,
                                  rec.hms_area_code,
                                  rec.sale_price);
      
                     if rec.fins_code = 'Y' then
                         update landings_hms set nature_of_sale = null
                         where  landing_seq = rec.landing_seq;
                     end if;
                   /* IF v_latereport is not null THEN
                          insert into landings_hms
                                  (dealer_rpt_id, late_report)
                              values (v_dealerreportid, v_latereport);
                      end if;
                      IF v_modifieddata is not null THEN
                          insert into landings_hms
                              (dealer_rpt_id, modified_data)
                          values (v_dealerreportid, v_modifieddata);
                      end if; */
                  END IF;
               ELSE
                  INSERT INTO landings
                              (dealer_rpt_id,
                               unit_measure,
                               reported_quantity,
                               dollars,
                               disposition_code,
                               grade_code,
                               species_itis,
                               market_code,
                               price,
                               gear_code,
                               de,
                             --  ue,
                               area_fished,
                               sub_area_fished,
                               local_area_code)
                       VALUES (v_dealerreportid,
                               rec.unit_measure,
                               rec.reported_quantity,
                               rec.dollars,
                               rec.disposition_code,
                               rec.grade_code,
                               rec.species_itis,
                               rec.market_code,
                               rec.price,
                               rec.gear_code,
                               SYSDATE,
                             --  v_loggedinuser,
                               rec.area_fished,
                               rec.sub_area_fished,
                               rec.local_area_code);
      
                  IF rec.additional_measure_flag = 'Y' THEN
                     INSERT INTO landings_addl_measure(dealer_rpt_id,
                                                       landing_seq,
                                                       unit_measure,
                                                       reported_quantity)
                          VALUES (v_dealerreportid,
                                  landings_seq.CURRVAL,
                                  rec.addnl_unit_quantity,
                                  rec.addnl_reported_quantity);
                   END IF;
      
                   IF rec.fins_flag = 'Y' THEN
      
                     INSERT INTO landings_hms
                                 (dealer_rpt_id,
                                  landing_seq,
                                  fins_attached,
                                  explanation,
                                  nature_of_sale,
                                  HMS_AREA_CODE,
                                  SALE_PRICE)
                          VALUES (v_dealerreportid,
                                  landings_seq.CURRVAL,
                                  rec.fins_code,
                                  rec.explanation,
                                  rec.nature_of_sale,
                                  rec.hms_area_code,
                                  rec.sale_price);
      
                          if rec.fins_code = 'Y' then
                              update landings_hms set nature_of_sale = null
                              where  dealer_rpt_id = v_dealerreportid;
                          end if;
      
                        /*  IF v_latereport is not null THEN
                          insert into landings_hms
                                  (dealer_rpt_id, late_report)
                              values (v_dealerreportid, v_latereport);
                      end if;
                      IF v_modifieddata is not null THEN
                          insert into landings_hms
                              (dealer_rpt_id, modified_data)
                          values (v_dealerreportid, v_modifieddata);
                      end if; */
                  END IF;
      end if;
      
            END LOOP;
      
      
      
      
      
      
      
      
      
         EXCEPTION
            WHEN no_data_found THEN
                null ;
         END;
      
         -- Enter further code below as specified in the Package spec.
      END;
      /
      
      -- Grants for Package Body
      GRANT EXECUTE ON electronic_dealer_report TO public
      /
      
      
      -- End of DDL Script for Package Body SAFIS.ELECTRONIC_DEALER_REPORT
      The code executes without issue and a SUCCESS message apears....however, the underlying table, LANDINGS is not updated.


      sooooo.....is this even possible?

      thanks!
      Karen

      Edited by: KarenH on May 9, 2013 11:12 AM
        • 1. Re: is it possible to update tables, using view on collection with db package?
          fac586
          KarenH wrote:
          I am wondering if the following will work....as so far, I cannot seem to get it right.

          I have a complicated apex 4.2 tabluar form based on a collection. The collection was becoming difficult to debug with all the c001, c002 columns and so we have created a view based on the collection:

          ...

          The code executes without issue and a SUCCESS message apears....however, the underlying table, LANDINGS is not updated.


          sooooo.....is this even possible?
          Don't see any reason why it shouldn't be.

          Suggest you add some instrumentation messages to the procedure using the <tt>apex_debug</tt> (or equivalent for your version) and see what's going on via a debug trace.

          Note that the exception handler is pointless as there is no code in the procedure that will raise <tt>no_data_found</tt>.
          EXCEPTION
          WHEN no_data_found THEN
          null ;
          END;
          • 2. Re: is it possible to update tables, using view on collection with db package?
            KarenH
            thank you!

            I am not very familiar with the debug feature in apex 4.2, so bear with me.

            I placed the following into my UPDATE_EDR package (after the update to landings stmt)
            dbms_output.put_line('landing upldate for: '||rec.landing_seq);
            but when I run again through apex form, nothing appears.... what am I missing.

            thanks again. Karen

            ps. I also took out the exception.

            Edited by: KarenH on May 9, 2013 11:47 AM
            • 3. Re: is it possible to update tables, using view on collection with db package?
              fac586
              KarenH wrote:
              thank you!

              I am not very familiar with the debug feature in apex 4.2, so bear with me.

              I placed the following into my UPDATE_EDR package (after the update to landings stmt)
              dbms_output.put_line('landing upldate for: '||rec.landing_seq);
              but when I run again through apex form, nothing appears.... what am I missing.
              To create log messages in the APEX debug trace, use <tt>apex_debug.message</tt>, not <tt>dbms_output.put_line</tt>.

              Enable Debug mode before completing and submitting the form, after the page is submitted, disable Debug and view the debug trace.
              • 4. Re: is it possible to update tables, using view on collection with db package?
                KarenH
                ok. here goes.

                My process now reads:
                apex_debug.message ('begin update of '||:P110_DEALER_RPT_ID);
                
                 electronic_dealer_report.update_edr(:P110_DEALER_RPT_ID);
                I refresh the page, click on DEBUG at the bottom of the page. Change the prices, hit SUBMIT (success msg), hit VIEW DEBUG and see the following:
                ...Process "update eDR" - Type: PLSQL
                ...Execute Statement: begin apex_debug.message ('begin update of '||:P110_DEALER_RPT_ID); electronic_dealer_report.update_edr(:P110_DEALER_RPT_ID); end; 
                
                begin update of 2664253
                 
                Branch point: After Processing
                ...Evaluating Branch: "AFTER_PROCESSING" Type: REDIRECT_URL Button: (No Button Pressed) Condition: (Unconditional)  
                Redirecting to f?p=500:113:17389121879152:::::&success_msg=Dealer%20Report%20Updated.%2F4FFEE942129B434E3AD616DA77635738%2F
                 
                Stop APEX Engine detected
                 
                Stop APEX Engine detected
                 Final commit
                so..the good news is that the variable P110_DEALER_RPT_ID seems to be passed...but then nothing else seems to happen.

                thank you again. I really appreciate your thoughts on this.

                Karen
                • 5. Re: is it possible to update tables, using view on collection with db package?
                  fac586
                  KarenH wrote:
                  ok. here goes.

                  My process now reads:
                  apex_debug.message ('begin update of '||:P110_DEALER_RPT_ID);
                  
                  electronic_dealer_report.update_edr(:P110_DEALER_RPT_ID);
                  I refresh the page, click on DEBUG at the bottom of the page. Change the prices, hit SUBMIT (success msg), hit VIEW DEBUG and see the following:
                  ...Process "update eDR" - Type: PLSQL
                  ...Execute Statement: begin apex_debug.message ('begin update of '||:P110_DEALER_RPT_ID); electronic_dealer_report.update_edr(:P110_DEALER_RPT_ID); end; 
                  
                  begin update of 2664253
                  
                  Branch point: After Processing
                  ...Evaluating Branch: "AFTER_PROCESSING" Type: REDIRECT_URL Button: (No Button Pressed) Condition: (Unconditional)  
                  Redirecting to f?p=500:113:17389121879152:::::&success_msg=Dealer%20Report%20Updated.%2F4FFEE942129B434E3AD616DA77635738%2F
                  
                  Stop APEX Engine detected
                  
                  Stop APEX Engine detected
                  Final commit
                  so..the good news is that the variable P110_DEALER_RPT_ID seems to be passed...but then nothing else seems to happen.
                  Which is possible given the code in the procedure. You need to add similar debug messages at various points in the procedure&mdash;like inside the loop and the various <tt>if</tt> branches&mdash;to determine the process flow.
                  • 6. Re: is it possible to update tables, using view on collection with db package?
                    KarenH
                    great. thanks. I just placed the message :
                    apex_debug.message ('update landings for dealer rpt '||v_dealerreportid);
                    inside the electronics_dealer_report.update_edr

                    those messages now show up as expected when I run the debug.....so again, good news in that the variable is correctly being passed. I will play around with others, and perhaps I need a commit statement. I will keep you posted. thanks again,
                    Karen
                    • 7. Re: is it possible to update tables, using view on collection with db package?
                      KarenH
                      ugh. I have added quite a few debug msgs...and commit statements, but still no luck. When I look at the debugger all the msg appear as expected, complete with differing landings_seq as they move through the loop. There do not appear to be any errors occuring.

                      any thoughts on what else I might test or look for.

                      thanks. Karen
                      • 8. Re: is it possible to update tables, using view on collection with db package?
                        fac586
                        KarenH wrote:
                        ugh. I have added quite a few debug msgs...and commit statements, but still no luck. When I look at the debugger all the msg appear as expected, complete with differing landings_seq as they move through the loop. There do not appear to be any errors occuring.
                        Post the procedure code with the debug messages and the debug trace.
                        any thoughts on what else I might test or look for.
                        Add debug messages that print the value of <tt>sql%rowcount</tt> after the insert and update statements.
                        • 9. Re: is it possible to update tables, using view on collection with db package?
                          KarenH
                          ok. I will get back to you.

                          But basically, I just want to make certain that what we are trying to do, can in fact be done. And that is, create a view of an apex_collection, display the view on an apex page in a tabular form region. Make changes to that tabular form..and when SUBMIT have it call the database package which updates a TABLE.

                          in the past, when just using collections as the source of a tabular form, I seem to remember having to issue all sorts of update_member commands.....but since we are now using views, has that been taken out of the mix? also, do I need an APPLYmru on the view: LANDINGS_COLLECTION_VIEW. I have taken it out because we are instead calling the package.

                          hmmm. ;)



                          thanks.
                          • 10. Re: is it possible to update tables, using view on collection with db package?
                            fac586
                            KarenH wrote:
                            ok. I will get back to you.

                            But basically, I just want to make certain that what we are trying to do, can in fact be done. And that is, create a view of an apex_collection, display the view on an apex page in a tabular form region. Make changes to that tabular form..and when SUBMIT have it call the database package which updates a TABLE.

                            in the past, when just using collections as the source of a tabular form, I seem to remember having to issue all sorts of update_member commands.....but since we are now using views, has that been taken out of the mix?
                            No. The collection still needs to be maintained using the <tt>apex_collection</tt> API. As long as you do that then the rest of your approach should just work.
                            also, do I need an APPLYmru on the view: LANDINGS_COLLECTION_VIEW. I have taken it out because we are instead calling the package.
                            You can't base an ApplyMRU process on a view on a collection. Remember we tried that before with an INSTEAD OF trigger on the view to handle the collection maintenance, but that's not possible as Oracle raises an insufficient privileges exception because we don't have update privs on the objects underlying the collection (annoyingly, as we have no intention of doing so).
                            • 11. Re: is it possible to update tables, using view on collection with db package?
                              KarenH
                              yikes. I feel like I just ran around a circle. so....my hope had been that using a view on a collection would make everything simpler....but in fact, it doesn't really do that...it only makes the initial query in the tabular region simpler because I can look at column names as opposed to c001, c002....


                              can I use the apex_collection API within my database package (electronic_dealer_report)? or is it better to just have a process in the form. I will be using the same logic in other places, so my preferece would be the package.

                              again, many thanks for your help!

                              Karen
                              • 12. Re: is it possible to update tables, using view on collection with db package?
                                KarenH
                                also, is there a good example using views on collections? thanks
                                • 13. Re: is it possible to update tables, using view on collection with db package?
                                  fac586
                                  KarenH wrote:
                                  yikes. I feel like I just ran around a circle. so....my hope had been that using a view on a collection would make everything simpler....but in fact, it doesn't really do that...it only makes the initial query in the tabular region simpler because I can look at column names as opposed to c001, c002....
                                  Every little helps...
                                  can I use the apex_collection API within my database package (electronic_dealer_report)? or is it better to just have a process in the form. I will be using the same logic in other places, so my preferece would be the package.
                                  For complex applications the best approach is to locate as much of the code as possible in packages, and just call these from APEX regions and processes. APEX APIs can be used in your packages, some of them&mdash;like <tt>apex_collection</tt>&mdash;can only be called from a valid APEX session.
                                  • 14. Re: is it possible to update tables, using view on collection with db package?
                                    KarenH
                                    every little bit does help. you are right. I just want more!

                                    so, I think I am getting the picture... I will create a view (LANDINGS_COLLECTION_VIEW) based on my collection (SPECIES_COLLECTION). The application will query the view and allow the user to manipulate (add/delete/update) fields. Once SUBMIT is hit, two packages will be called:

                                    electronic_dealer_reports.update_species_collection - this package will update the species collection with a series of update_members commands:
                                     APEX_COLLECTION.UPDATE_MEMBERS (
                                            p_collection_name => 'SPECIES_COLLECTION',
                                            p_seq  => l_seq,
                                            p_c001 => l_carr,
                                            p_n001 => l_narr,
                                            p_d001 => l_darr);
                                    ...I have yet to design.
                                    The second package called will be electronic_dealer_reports.update_edr (as shown above). This package will update the table LANDINGS by selecting from the view, LANDINGS_COLLECTION_VIEW (which is based on the newly updated, species_collection).


                                    I that it? or is there a simplier way? again, this code will be used in several places.

                                    thanks for your help. Karen