1 2 Previous Next 17 Replies Latest reply: May 2, 2014 2:08 PM by Doolius Go to original post RSS
      • 15. Re: Update 2 tables in APEX on Submit
        Doolius

        Thanks for the help CJ.

        One question I do still have tho, when I run the code:

        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'))); 
        

        alone, i get an error that says invalid character.

        But if I run each "a.date = greatest....." separately in it's own merge statement it works fine.

        Any way around having to create multiple merge statements?

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

          Packages don't call anything...they're simply a container for functions, procedures and so forth. Within an APEX environment I will generally have a package spec and body dedicated to the application that I'm creating.  As my projects are generally small, this works out fairly well.  For an Oracle Forms project or a straight DB project my packages would increase in number and follow some naming standard, usually keyed on the event or business process.

           

          In your middle example above, the package body doesn't have the same name as the package spec so you have a problem there.  If that's corrected then a call to pkg1.run_everything will indeed execute the two procedures within the package.

           

          As for the last snippet, you can have multiple functions and procedures in a package.  That's why packages exist, so you can create the logical groupings of your processes.

           

          I would suggest you pick up a copy of "Oracle PL/SQL Programming" from O'Reilly publishing.  It's written by Steven Feuerstein w/ Bill Pribyl (5th edition anyway) and would go a long way to explaining all the concepts you're asking about.

           

          HTH

           

          CJ

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

            Thanks for all the help.

            I ended up adding my procedure along with the current procedure call in the initial package.

             

            I didn't want to add the update procedure to the current one because it also inserts/updates/closes records.

            I needed it to be able to be called individually.

             

            Everything works now.

             

            Thanks

            Steven

            1 2 Previous Next