10 Replies Latest reply: Feb 4, 2013 12:16 PM by tillie RSS

    Refreshing Materialzed Views from APEX not working

    tillie
      We have a stored procedure which is being executed from APEX, the stored procedure refreshes a couple materialized views. When we execute the stored procedure from SQL Developer, as the schema owner, the materialized views are updated correctly. Correct data and refresh datetime change as expected. Also all is correct if we refresh each materialized view manually.

      Here is the weird thing that happens, when we execute that same stored procedure, but this time from APEX, it appears that the materialized view is refreshed, but now it has records missing. The refresh datetime is correct, but the data is wrong.

      We are using APEX 4.1.1.00.23, and an 11.2.0.3.0 database.

      Any suggestions of things to try would be great.
        • 1. Re: Refreshing Materialzed Views from APEX not working
          TexasApexDeveloper
          Can you instead of using APEX to refresh the views, use a DBMS_SCHEDULER job to refresh them? We do that here at my office and it works quite well..

          Thank you,

          Tony Miller
          Ruckersville, VA
          • 2. Re: Refreshing Materialzed Views from APEX not working
            tillie
            unfortunately, our users will be updating data and need to be able to refresh the mv's whenever they get the data updated to their liking.

            The procedure will also be a scheduled job, which will run nightly.
            • 3. Re: Refreshing Materialzed Views from APEX not working
              riedelme
              tillie wrote:
              We have a stored procedure which is being executed from APEX, the stored procedure refreshes a couple materialized views. When we execute the stored procedure from SQL Developer, as the schema owner, the materialized views are updated correctly. Correct data and refresh datetime change as expected. Also all is correct if we refresh each materialized view manually.

              Here is the weird thing that happens, when we execute that same stored procedure, but this time from APEX, it appears that the materialized view is refreshed, but now it has records missing. The refresh datetime is correct, but the data is wrong.
              Might be a privielge issue - remember that Apex accesses PL/SQL as ... I suddenly forget who (early in the morning!) but probably not the user you might think. You can figure it out by using SELECT USER FROM DUAL in the SQL utility after logging in.

              If it is a privilege issue you can solive it several ways. First, grant the refresh privs to the Apex user. Second, as suggested create a job to do the refresh (which also might need privs)
              • 4. Re: Refreshing Materialzed Views from APEX not working
                928468
                Joel explained this very well in a blog post:

                http://joelkallman.blogspot.de/2010/08/application-express-and-parsing-of-sql.html

                I think that's the reason for this issue.
                Creating a job, like suggested, should do the trick.

                Thanks
                Sandro
                • 5. Re: Refreshing Materialzed Views from APEX not working
                  tillie
                  The stored procedure is executing and the mvs are refreshed, there is just wrong data when executed from APEX.

                  And as I mentioned, one of our requirements for the system is to have "as needed" refresh of the materialized views for reporting. So just scheduling the job will not solve our problem.

                  The materialized views we need to refresh must be refreshed in a specific order. They are sequetial, the second (MV_b), depends on the first (MV_a), the third (MV_c) depends on the MV_a and MV_b.

                  What appears to be happening is the mvs that are dependent on others are starting before the MV they are dependent on is complete.

                  Example:
                  MV_a needs to execute first
                  MV_b is dependent on MV_a
                  MV_c is dependent on MV_a and MV_b

                  MV_a starts at 9:46:37 ends at 9:46:38
                  MV_b starts at 9:46:37 ends at 9:46:37
                  MV_c starts at 9:46:37 ends at 9:46:37

                  So a question: is there a way force the dependent refresh to not start until the first is complete?

                  And I really don't understand why this only seems to surface when the stored procedure is executed from APEX.

                  Edited by: tillie on Jan 31, 2013 10:36 AM
                  • 6. Re: Refreshing Materialzed Views from APEX not working
                    riedelme
                    tillie wrote:
                    The stored procedure is executing and the mvs are refreshed, there is just wrong data when executed from APEX.

                    And as I mentioned, one of our requirements for the system is to have "as needed" refresh of the materialized views for reporting. So just scheduling the job will not solve our problem.

                    The materialized views we need to refresh must be refreshed in a specific order. They are sequetial, the second (MV_b), depends on the first (MV_a), the third (MV_c) depends on the MV_a and MV_b.
                    Have you created materialized view groups?

                    Alternately, can you set up a procedure to manually perform the refreshes in the order you want using DBMS_MVIEW.REFRESH?
                    • 7. Re: Refreshing Materialzed Views from APEX not working
                      tillie
                      We have never used MV groups, but we will look into them. Will report back what we find.

                      We call a stored procedure that calls each to the refresh statements out individually. That is the procedure that works when we run it from anywhere except APEX.

                      Edited by: tillie on Jan 31, 2013 11:11 AM
                      • 8. Re: Refreshing Materialzed Views from APEX not working
                        tillie
                        Ok, so we set up a Refresh Group, (Materialized view group is a completely different thing).

                        We added all of our materialized views to the Refresh Group and are now refreshing using that group.

                        Problem still happens, it is really strange when we execute the refresh from SQL Developer, all of the MVs are correct, when we execute from APEX the results sets for the MVs are different. Exact same results as when we refreshed individually.

                        How can a query give a different answer based on where it is executed from? That seems to be the question.

                        I'm going to go back to the start and work thru the SQL Workshop in APEX and recheck each query that is used to built the Materialized views. I'm sure that we did this at the beginning of this, but maybe we missed something.
                        • 9. Re: Refreshing Materialzed Views from APEX not working
                          TexasApexDeveloper
                          You do understand, when you run your update MV routine in SQL Developer you are logged in as one user and when you execute the same code you are logged into the database as a different user who MIGHT have different rights...


                          Thank you,

                          Tony Miller
                          Ruckersville, VA
                          • 10. Re: Refreshing Materialzed Views from APEX not working
                            tillie
                            I do understand that when we execute from the 2 different places that we are executing as different users.

                            We believe that we have found the problem.

                            It appears that in one of our Materialized Views we had a line in our where clause that was executing differently from the 2 tools.

                            We have a system parameter table were we store some baseline values for our app. In this table the "param_value" field is a Varchar2, but in this case contains a Date.

                            In our MV we have a where clause that compares the "Status_Date" (a date field) with the "param_value" from our parameter table.

                            Looked like:
                            and status_date > param_value

                            Looks like when we run from SQL Developer , the tool performed the proper translation of the "param_value" a date, (I know dangerous), but when we ran it from APEX it did not appear to make the Date translation.

                            We have changed the where clause to look like:
                            and status_date > to_date(param_value, 'dd-mon-rrrr')

                            Now all seems to be working as expected....

                            Thanks for all of your help, we did learn something new (Refresh Groups) and implemented it, so our silly mistake was not a complete waste of time.