1 2 Previous Next 17 Replies Latest reply: May 2, 2014 2:08 PM by Doolius RSS

    Update 2 tables in APEX on Submit

    Doolius

      In short, I need to update certain columns across 2 tables on submit.

       

      Table A
      pk1
      pk2
      date1
      date2

       

      Table B
      pk1
      pk2
      date1
      date2

       

      Table A and Table B have like records that can be matched by a pk1/pk2 combination.

       

      When DATE1 is updated in table A I need to check table B for:
      1. The pk1/pk2 match
      2. If A.date1 is null then update it with B.date1
      3. If A.date1 is not null then compare both dates and if:
           3a. A.date1 > B.date1 replace B.date1 with A.date1
           3b. A.date1 < B.date1 replace A.date1 with B.date1

       

      Same goes for DATE2.

       

      Baiscally I need to be able to do what the below code does everytime the user clicks SUBMIT.

      merge into A a
      using(
           select distinct
           pk1,
           pk2,
           date1,
           date2
           from B) b
      on(a.pk1 = b.pk1 and a.pk2 = b.pk2)
      when matched then update
      set
           a.date1 = greatest(nvl(a.date1, TO_DATE('01011950','MMDDYYY')),
                                         nvl(b.date1, TO_DATE('01011950','MMDDYYYY')));
           a.date2 = greatest(nvl(a.date2, TO_DATE('01011950','MMDDYYY')),
                                         nvl(b.date2, TO_DATE('01011950','MMDDYYYY')));
      

       

      I'm not sure the best way to go about accomplishing this.

       

      Thanks
      Steven

        • 1. Re: Update 2 tables in APEX on Submit
          vincent_deelen

          How about placing your code in a page submit process?

           

          Kind regards,

          Vincent Deelen

          vincentdeelen.blogspot.com

          • 2. Re: Update 2 tables in APEX on Submit
            DTSIguy

            Use a package...

             

            CJ

            • 3. Re: Update 2 tables in APEX on Submit
              Doolius

              Vincent,

               

              WHen the user clicks submit, it re-directs to a URL which calls javascript to validate the values in the form.

              Is there a way to have that process submit only if everything passes validation?

              and also, would I have to write it in PL/SQL or would the way it's written now work?

               

              Steven

              • 4. Re: Update 2 tables in APEX on Submit
                Doolius

                CJ,

                 

                Are you talking about an database package or is there a package option in APEX?

                I had the code in an database package as a function that ran every 6 hours, but the customer wants the data transfered over as soon as the user clicks submit.

                Is there a way to call that function within APEX?

                Or maybe call it in the update trigger?

                 

                Steven

                • 5. Re: Update 2 tables in APEX on Submit
                  DTSIguy

                  Database package called from page process that fires upon Page Submit (whatever button you're using). 

                  • 6. Re: Update 2 tables in APEX on Submit
                    Doolius

                    Ok. So a pl/sql page process with the code

                     

                    begin
                    package_name.update_function_name();
                    end;
                    

                     

                    I'm not very familiar with PL/SQL so I don't know if that's the correct format to call the fucntion?

                    • 7. Re: Update 2 tables in APEX on Submit
                      HarryF

                      That format works if the package is in the same schema that the APEX project is pointing to.

                      • 8. Re: Update 2 tables in APEX on Submit
                        Doolius

                        It is.

                        Would it be better to put the code into a procedure instaed of a function inside of the package?

                        Or would it not matter?

                        • 9. Re: Update 2 tables in APEX on Submit
                          DTSIguy

                          That's generally a preference issue.  IMO, a package gives more flexibility for future development.  So put the function in a package or put a procedure in the package.

                           

                          CJ

                          • 10. Re: Update 2 tables in APEX on Submit
                            DTSIguy

                            If you get Oracle Magazine, Steven Feuerstein has some really good articles on APEX coding of SQL and PL/SQL.  See the current issue and the one before.

                            • 11. Re: Update 2 tables in APEX on Submit
                              Doolius

                              I created the procedure inside my package (the package is stored in my APEX schema and I copied the format of another procedure)

                               

                              procedure update_records is
                              
                              begin
                              
                              merge into A a  
                              using( 
                              select distinct  
                                   pk1, 
                                   pk2, 
                                   date1, 
                                   date2 
                              from B) b  
                              on(a.pk1 = b.pk1 and a.pk2 = b.pk2)  
                              when matched then update  
                              set  
                                   a.date1 = greatest(nvl(a.date1, TO_DATE('01011950','MMDDYYY')),  
                                                                 nvl(b.date1, TO_DATE('01011950','MMDDYYYY')));  
                                   a.date2 = greatest(nvl(a.date2, TO_DATE('01011950','MMDDYYY')),  
                                                                 nvl(b.date2, TO_DATE('01011950','MMDDYYYY'))); 
                              COMMIT;
                              exception
                                   when OTHERS then
                                   null;
                              end update_records
                              

                               

                              When I create the pl/sql process on page submit and enter the code

                              begin
                              apex_package.update_records;
                              end;
                              

                              I get the error that component update_records must be declared

                              • 12. Re: Update 2 tables in APEX on Submit
                                Doolius

                                I created the procedure as a stand alone within apex, and the process compiled correctly.

                                begin
                                update_records;
                                end;
                                

                                Hopefully that will work and it won't run the procedure until the javascript validations have all passed

                                • 13. Re: Update 2 tables in APEX on Submit
                                  DTSIguy

                                  As you indicate unfamiliarity w/ PL/SQL...you can do the following from a SQL session, from APEX directly or from any SQL tool (like SQL Developer)...

                                   

                                  create or replace package <your package> is

                                      procedure update_records();

                                  end <your package>;

                                   

                                  create or replace package body <your package> is

                                     procedure update_records() is

                                        begin

                                        ....<code>

                                        end;

                                  end <your package>;

                                   

                                  So your call from APEX would be <your package>.update_records;

                                   

                                  As you have the procedure working, up to you whether to move to a package.

                                   

                                  HTH

                                   

                                  CJ

                                  • 14. Re: Update 2 tables in APEX on Submit
                                    Doolius

                                    I initially had the procedure in my package, but when I made my call:

                                    begin
                                    package_name.update_records;
                                    end;
                                    

                                    I was tring to call an individual procedure within the package body. (not referenced in the initial package)

                                    Right now the package only calls one procedure, that then calls everything else within the package body.

                                    as in:

                                     

                                    create package pkg1 is
                                    procedure run_everything;
                                    end pkg1;
                                    create package body pkg1_body is
                                    procedure proc_one is
                                    .........
                                    end;
                                    procedure proc_two is
                                    .........
                                    end;
                                    procedure run_everything is
                                    begin
                                    proc_one;
                                    proc_two;
                                    end;
                                    

                                     

                                    say I added in my update_records procedure. Would it work if I changed the initial package to:

                                    create package pkg1 is
                                    procedure run_everything;
                                    procedure update_records;
                                    end pkg1;
                                    

                                    or can there only be one procedure call in the initial package?

                                    1 2 Previous Next