1 2 3 Previous Next 38 Replies Latest reply: Jun 12, 2012 3:03 PM by 517508 Go to original post RSS
      • 15. Re: questions about apex collections sequence number vs. attribute number?
        517508
        great. thanks for the tip. I just saw a posting and will try and replicate, though on our page, the underlying table to this collection/view cannot be updated or inserted to until the very end as it is based on the data entered into two earlier collections added/deleted on page.

        Edited by: KEH813 on Jun 7, 2012 2:04 PM
        • 16. Re: questions about apex collections sequence number vs. attribute number?
          517508
          ok. I have the following now in place...

          I have created the view VIEW_CATCH_COLLECTION

          then created the INSTEAD OF TRIGGER:
          CREATE OR REPLACE VIEW catch_collection_view (
             n,
             effort_seq,
             species_itis,
             disposition_code,
             reported_qty,
             unit_measure,
             sale_disp,
             price,
             permit_id,
             market_code,
             grade_code,
             date_sold,
             c044,
             unit_count,
             hms_flag,
             hms_fin_flag,
             finsattached,
             finsnotattached,
             finsunknown,
             explanation,
             nature_of_sale,
             hms_area_code )
          AS
          select 
          seq_id  n,
           c033   effort_seq,
           c034  SPECIES_ITIS,
            c035  disposition_code,  
            c036  reported_qty,
                    c037 unit_measure,
                    c038  sale_disp,
                    c039  PRICE,
                    c040  Permit_ID, 
                    c041  MARKET_CODE,
                    c042  GRADE_CODE, 
                    c043 date_sold,
                    c044  ,
                    c045 unit_count,
                    c046  hms_flag,
                    c009  hms_fin_flag,
                    c010  finsattached,
                    c011 finsnotattached,
                    c012  finsunknown,
                    c013  explanation,
                    c014 nature_of_sale,
                    c047  hms_area_code
                    from apex_collections where collection_name = 'CATCH_C'
          /
          
          
          -- Triggers for CATCH_COLLECTION_VIEW
          
          CREATE OR REPLACE TRIGGER catch_insert_to_collection
           INSTEAD OF
            INSERT
           ON catch_collection_view
          REFERENCING NEW AS NEW OLD AS OLD
          BEGIN
          --
          DECLARE
          --
          v_col_name VARCHAR2(100) := 'CATCH_C'; 
          --
          BEGIN
          --
          IF INSERTING THEN
          --
          IF NOT APEX_COLLECTION.COLLECTION_EXISTS ( p_collection_name => v_col_name ) THEN
          --
          APEX_COLLECTION.CREATE_COLLECTION(p_collection_name => v_col_name );
          --
          END IF;
          --
          APEX_COLLECTION.ADD_MEMBER(
                    p_collection_name => v_col_name,
                    p_c033 => :new.effort_seq,
                    p_c034 => :new.SPECIES_ITIS,
                    p_c035 => :new.disposition_code,  
                    p_c036 => :new.reported_qty,
                    p_c037 => :new.unit_measure,
                    p_c038 => :new.sale_disp,
                    p_c039 => :new.PRICE,
                    p_c040 => :new.Permit_ID, 
                    p_c041 => :new.MARKET_CODE,
                    p_c042 => :new.GRADE_CODE, 
                    p_c043 => :new.date_sold,
                    p_c044 => :new.c044,
                    p_c045 => :new.unit_count,
                    p_c046 => :new.hms_flag,
                    p_c009 => :new.hms_fin_flag,
                    p_c010 => :new.finsattached,
                    p_c011 => :new.finsnotattached,
                    p_c012 => :new.finsunknown,
                    p_c013 => :new.explanation,
                    p_c014 => :new.nature_of_sale,
                    p_c047 => :new.hms_area_code); 
          
          ELSIF updating THEN
                raise_application_error(-20000,'CATCH_C Not implemented');
          ELSIF deleting THEN
          --
          APEX_COLLECTION.DELETE_MEMBER(
          p_collection_name => v_col_name,
          p_seq => :OLD.N);
          --
          IF APEX_COLLECTION.COLLECTION_MEMBER_COUNT ( p_collection_name => v_col_name ) = 0 THEN
          --
          APEX_COLLECTION.DELETE_COLLECTION ( p_collection_name => v_col_name );
          --
          END IF; 
          --
          END IF;
          --
          END;
          --
          END;
          /
          
          
          -- End of DDL Script for View SAFIS.CATCH_COLLECTION_VIEW
          I have yet to re-attack the apex form but if you see this, am I on the right track...and do I need to get rid of the MRU and MRD for this region on the form...and replace with a process...or will update automatically fire. Currently, when I now press the ADD_ROW nothing happens.

          thanks again. this is really helpful to me.

          Edited by: KEH813 on Jun 7, 2012 2:24 PM
          • 17. Re: questions about apex collections sequence number vs. attribute number?
            517508
            I have now created a tabular form for the view CATCH_COLLECTION_VIEW. The key is ROWID. The wizard automatically creates the MRU and MRD process, and I have the INSTEAD OF trigger in place (as seen above).

            I expected that by pressing the ADD ROW button, a new row would appear...but that is not the case. Is there something else I need to do. I will also research more. thanks
            • 18. Re: questions about apex collections sequence number vs. attribute number?
              fac586
              KEH813 wrote:
              I have now created a tabular form for the view CATCH_COLLECTION_VIEW. The key is ROWID. The wizard automatically creates the MRU and MRD process, and I have the INSTEAD OF trigger in place (as seen above).

              I expected that by pressing the ADD ROW button, a new row would appear...but that is not the case. Is there something else I need to do. I will also research more. thanks
              This has got rather complicated—and the time difference doesn't help. If you could reproduce on apex.oracle.com I'd be able to give it a look over the weekend.
              • 19. Re: questions about apex collections sequence number vs. attribute number?
                517508
                you are fabulous...thanks.

                I have placed the application here, but it doesn't seem to work as the db is not attached, and there is an app that runs before it (logon app)....but at least you can see the page/regions:

                http://apex.oracle.com/pls/apex/f?p=4000:1:466206823557001::NO:RP:FB_FLOW_ID,F4000_P1_FLOW:44930,44930

                application - e1ticket
                p400
                region 55 is the region selecting from view (region 51 is original query on collection).

                thanks for you help. Karen

                ps. and don't spend too much time on weekend!
                • 20. Re: questions about apex collections sequence number vs. attribute number?
                  fac586
                  KEH813 wrote:
                  you are fabulous...thanks.

                  I have placed the application here, but it doesn't seem to work as the db is not attached, and there is an app that runs before it (logon app)....but at least you can see the page/regions:

                  http://apex.oracle.com/pls/apex/f?p=4000:1:466206823557001::NO:RP:FB_FLOW_ID,F4000_P1_FLOW:44930,44930

                  application - e1ticket
                  p400
                  region 55 is the region selecting from view (region 51 is original query on collection).
                  I really need to see it in the App Builder (especially if it's not fully runnable). Can you post guest developer credentials for your workspace?
                  thanks for you help. Karen

                  ps. and don't spend too much time on weekend!
                  Have you seen the weather we're getting on this side of the Atlantic?! It'll be a nice safe, comfortable way of passing the time...
                  • 21. Re: questions about apex collections sequence number vs. attribute number?
                    517508
                    I have created the account TEST/FISH which has developer privs.

                    in general the application is designed for commerial seafood dealers to track their purchases of species and to provide additional info on highly migratory species like Shark. Essentially, this page is broken into three regions...TRIP, EFFORT, CATCH...a trip may have many efforts and and effort many catches. We initially created this with collections to allow for multiple tabular forms...the primary keys are generated when the report is saved and data moved from the collection to the table. I am hoping that by using the VIEW and INSTEAD of trigger that we can more easily (omg! it has to be easier) modify the form as needed.

                    again, many thanks!

                    yikes, that Scottish weather is tough...but such a beautiful city...was there a couple years ago, sunny every day.

                    Edited by: KEH813 on Jun 8, 2012 9:59 AM
                    • 22. Re: questions about apex collections sequence number vs. attribute number?
                      fac586
                      KEH813 wrote:
                      I have created the account TEST/FISH which has developer privs.
                      Workspace?
                      in general the application is designed for commerial seafood dealers to track their purchases of species and to provide additional info on highly migratory species like Shark. Essentially, this page is broken into three regions...TRIP, EFFORT, CATCH...a trip may have many efforts and and effort many catches. We initially created this with collections to allow for multiple tabular forms...the primary keys are generated when the report is saved and data moved from the collection to the table. I am hoping that by using the VIEW and INSTEAD of trigger that we can more easily (omg! it has to be easier) modify the form as needed.
                      I've previously done some fisheries related work here, and from what appears on the forum from time to time there a lot of people using APEX for fishy things...
                      • 23. Re: questions about apex collections sequence number vs. attribute number?
                        517508
                        oops. sorry the apex.oracle.com workspace is: http://apex.oracle.com/pls/apex/f?p=4500:1000:417272566749201

                        workspace: KEH
                        user: TEST
                        pwd: FISH

                        ahh..fishing indeed...much floundering about. yikes..tuna in. ;)
                        • 24. Re: questions about apex collections sequence number vs. attribute number?
                          517508
                          hmmm. I am just looking at my INSTEAD OF trigger and think that may be the culprit.... I currently have this;
                          create or replace TRIGGER catch_insert_to_collection
                             INSTEAD OF INSERT ON catch_collection_view
                          
                          REFERENCING OLD AS OLD NEW AS NEW 
                          BEGIN
                          --
                          DECLARE
                          --
                          v_col_name VARCHAR2(100) := 'CATCH_C'; 
                          --
                          BEGIN
                          --
                          IF INSERTING THEN
                          --
                          IF NOT APEX_COLLECTION.COLLECTION_EXISTS ( p_collection_name => v_col_name ) THEN
                          --
                          APEX_COLLECTION.CREATE_COLLECTION(p_collection_name => v_col_name );
                          --
                          END IF;
                          --
                          
                            apex_collection.add_member('CATCH_C',
                                  p_c033 => 0, -- effort,
                                  p_c034 => '0', -- species_itis,
                                  p_c035 => '001', -- disposition,
                                  p_c036 => null, -- reported quantity,
                                  p_c037 => '0', -- unit measure,
                                  p_c038 => '0', -- sales disposition,
                                  p_c039 => null, -- price,
                                  p_c040 => 0, -- dealer permit id,
                                  p_c041 => '0', -- market code,
                                  p_c042 => '9999', -- grade code,
                                  p_c043 => null,--date sold
                                  p_c044 => null); 
                          
                          /* ELSIF updating THEN
                                APEX_COLLECTION.UPDATE_MEMBER(
                                    p_collection_name => v_col_name,
                                    p_c033 => :new.effort_seq,
                                    p_c034 => :new.SPECIES_ITIS,
                                    p_c035 => :new.disposition_code,  
                                    p_c036 => :new.reported_qty,
                                    p_c037 => :new.unit_measure,
                                    p_c038 => :new.sale_disp,
                                    p_c039 => :new.PRICE,
                                    p_c040 => :new.Permit_ID, 
                                    p_c041 => :new.MARKET_CODE,
                                    p_c042 => :new.GRADE_CODE, 
                                    p_c043 => :new.date_sold,
                                    p_c044 => :new.c044,
                                    p_c045 => :new.unit_count,
                                    p_c046 => :new.hms_flag,
                                    p_c009 => :new.hms_fin_flag,
                                    p_c010 => :new.finsattached,
                                    p_c011 => :new.finsnotattached,
                                    p_c012 => :new.finsunknown,
                                    p_c013 => :new.explanation,
                                    p_c014 => :new.nature_of_sale,
                                    p_c047 => :new.hms_area_code); */
                          
                          ELSIF deleting THEN
                          --
                          APEX_COLLECTION.DELETE_MEMBER(
                          p_collection_name => v_col_name,
                          p_seq => :OLD.N);
                          --
                          IF APEX_COLLECTION.COLLECTION_MEMBER_COUNT ( p_collection_name => v_col_name ) = 0 THEN
                          --
                          APEX_COLLECTION.DELETE_COLLECTION ( p_collection_name => v_col_name );
                          --
                          END IF; 
                          --
                          END IF;
                          --
                          END;
                          --
                          END;
                          first, I think I need a seperate trigger for INSERT, UPDATE, DELETE...and there doe not appear to be a command APEX_COLLECTION.UPDATE, but rather must use UPDATE_MEMBER_ATTRIBUTE.....

                          Edited by: KEH813 on Jun 8, 2012 1:31 PM
                          • 25. Re: questions about apex collections sequence number vs. attribute number?
                            fac586
                            KEH813 wrote:
                            hmmm. I am just looking at my INSTEAD OF trigger and think that may be the culprit.... I currently have this;

                            first, I think I need a seperate trigger for INSERT, UPDATE, DELETE...
                            Yes.
                            and there doe not appear to be a command APEX_COLLECTION.UPDATE, but rather must use UPDATE_MEMBER_ATTRIBUTE.....
                            There is an <tt>apex_collection.update_member</tt> method.
                            • 26. Re: questions about apex collections sequence number vs. attribute number?
                              517508
                              thank you again.

                              I created the following, thanks....


                              When I update the view via the form, and press SUBMIT, I receive a WEB PAGE not found error. I just want to make certain I understand...I should now be able to create a new tabular form region, based on the view CATCH_COLLECTION_VIEW and with a primary key of rowid....and use the default buttons ADD, SUBMIT? or is there something I am missing?

                              thanks.

                              CREATE OR REPLACE TRIGGER UPDATE_catch_to_collection
                               INSTEAD OF
                                UPDATE
                               ON catch_collection_view
                              REFERENCING NEW AS NEW OLD AS OLD
                              BEGIN
                              --
                              DECLARE
                              --
                              v_col_name VARCHAR2(100) := 'CATCH_C'; 
                              --
                              BEGIN
                              --
                              IF UPDATING THEN
                              --
                              IF NOT APEX_COLLECTION.COLLECTION_EXISTS ( p_collection_name => v_col_name ) THEN
                              --
                              APEX_COLLECTION.CREATE_COLLECTION(p_collection_name => v_col_name );
                              --
                              END IF;
                              --
                              
                               
                                    APEX_COLLECTION.UPDATE_MEMBER(
                                        p_collection_name => v_col_name,
                                        p_seq => :new.n,
                                        p_c033 => :new.effort_seq,
                                        p_c034 => :new.SPECIES_ITIS,
                                        p_c035 => :new.disposition_code,  
                                        p_c036 => :new.reported_qty,
                                        p_c037 => :new.unit_measure,
                                        p_c038 => :new.sale_disp,
                                        p_c039 => :new.PRICE,
                                        p_c040 => :new.Permit_ID, 
                                        p_c041 => :new.MARKET_CODE,
                                        p_c042 => :new.GRADE_CODE, 
                                        p_c043 => :new.date_sold,
                                        p_c044 => :new.c044,
                                        p_c045 => :new.unit_count,
                                        p_c046 => :new.hms_flag,
                                        p_c009 => :new.hms_fin_flag,
                                        p_c010 => :new.finsattached,
                                        p_c011 => :new.finsnotattached,
                                        p_c012 => :new.finsunknown,
                                        p_c013 => :new.explanation,
                                        p_c014 => :new.nature_of_sale,
                                        p_c047 => :new.hms_area_code);  
                              
                               
                              --
                              END IF;
                              --
                              END;
                              --
                              END;
                              /
                              
                              
                              -- End of DDL Script for Trigger SAFIS.UPDATE_CATCH_TO_COLLECTION
                              Edited by: KEH813 on Jun 11, 2012 7:10 AM

                              Edited by: KEH813 on Jun 11, 2012 7:12 AM
                              • 27. Re: questions about apex collections sequence number vs. attribute number?
                                fac586
                                KEH813 wrote:
                                thank you again.

                                I created the following, thanks....


                                When I update the view via the form, and press SUBMIT, I receive a WEB PAGE not found error.
                                Check your web server logs for the underlying error.
                                I just want to make certain I understand...I should now be able to create a new tabular form region, based on the view CATCH_COLLECTION_VIEW and with a primary key of rowid....and use the default buttons ADD, SUBMIT? or is there something I am missing?
                                I'm not keen on the use of ROWIDs. Not sure that will even work on a view.

                                You don't want this:
                                IF UPDATING THEN
                                --
                                IF NOT APEX_COLLECTION.COLLECTION_EXISTS ( p_collection_name => v_col_name ) THEN
                                --
                                APEX_COLLECTION.CREATE_COLLECTION(p_collection_name => v_col_name );
                                --
                                END IF;
                                The INSTEAD OF UPDATE trigger will only fire on UPDATE, and creating the collection on UPDATE makes no sense anyway.

                                I had a look at it but it's a very complex page. Knowing nothing about the app and without being able to see it running and get the errors/debug in real time it's hard to figure out where the problems are.

                                Sort out the triggers then try building a little report/form test app on top of the view using the wizards, with a PK (sequence column? Will be unique in a collection.) rather than ROWID.
                                • 28. Re: questions about apex collections sequence number vs. attribute number?
                                  517508
                                  thanks, I just recreated the region for CATCH_COLLECTION_VIEW and set primary key = N, with existing trigger. The same http 404 WEB PAGE NOT found appears.


                                  Is there a way to set up this application so that you could run it?


                                  I also created a simple application w/one page that creates the collection and then shows a tabular form. When I update the form (tied to view), and hit SUBMIT, I receive the following:

                                  1 error has occurred•ORA-01031: insufficient privileges (Row 1)


                                  I granted SELECT, INSERT, UPDATE, DELETE on apex_collection to PUBLIC and that seemed to resolve the error.... now I am able to update via the form, but it does not appear to save the change in the view.

                                  Edited by: KEH813 on Jun 11, 2012 8:23 AM

                                  Edited by: KEH813 on Jun 11, 2012 8:28 AM
                                  • 29. Re: questions about apex collections sequence number vs. attribute number?
                                    fac586
                                    KEH813 wrote:
                                    thanks, I just recreated the region for CATCH_COLLECTION_VIEW and set primary key = N, with existing trigger. The same http 404 WEB PAGE NOT found appears.


                                    Is there a way to set up this application so that you could run it?
                                    Only by creating all the schema(s) objects and data on apex.oracle.com.
                                    I also created a simple application w/one page that creates the collection and then shows a tabular form. When I update the form (tied to view), and hit SUBMIT, I receive the following:
                                    Can you create that on apex.oracle.com?
                                    1 error has occurred•ORA-01031: insufficient privileges (Row 1)


                                    I granted SELECT, INSERT, UPDATE, DELETE on apex_collection to PUBLIC and that seemed to resolve the error.... now I am able to update via the form, but it does not appear to save the change in the view.
                                    Only run DDL against APEX schema objects if instructed do so by an Oracle support rep. Revoke this now.

                                    I'm out tonight but I'll look at the simplified app on apex.oracle.com tomorrow if you get it operable there.