Deploying Updates to Production Applications

While at ODTUG in June, I had the pleasure of attending John Scott's ApEx Best Practices session. For me that was a real treat as Steve Feuerstein's PL/SQL best practices is one of my all time favorites. One of things I remember most about the session, and I can only use my memory as I can't seem to find the presentation from the conference, was a tip on deploying updates to applications in production while minimizing down time - down to a few minutes.

The technique was simple enough and I'm a big fan of simple during deployments. The prerequisite was use of Apache over the EPG (Embedded PL/SQL Gateway) although there are some ways around that (more later). The steps involved went something like this (pulling from memory):

1. Deploy the updated application into production while leaving the current application in place. Yes, this means it will get a new APP_ID and the APP_ALIAS if not changed will get the APP_ID appended to it.
2. Get the updated application working. Easy enough right! Get it up while the other application breaths its last breaths.
3. When the updated application is up and running 100%, use an apache rewrite rule to redirect traffic from the old application to the new one.
4. Then, when the time is right, remove the old application and modify the updated application's APP_ALIAS to what it should be.

It is step 3 that requires Apache. However, if your users access your applications via a centralized link (on a portal) and that link uses the APP_ALIAS and not the APP_ID (as all your links do, right?), then users will be redirected after you've completed step 4 anyway. However, you might want to delay removing the old application and instead just alter its APP_ALIAS (so that you can use it in the updated app) and then set its status to "Unavailable" with a message that includes the correct link. Providing the correct link will help those that saved the link in their browser after it resolved to the APP_ID.

Now, why did I just reiterate all of that? Well for one, I think it's a great technique that may benefit a lot of people. But I remember thinking to myself and even asking the question, "What will happen to Interactive Reports saved in the application being replaced?" I thought I had issues with that and made a mental note to follow up on it later.

The fact is that if your users have saved Interactive Reports in the old application, then you'll need to add one more step to the migration process. Thanks to Brian, I now know the table holding the Interactive Report data is named WWV_FLOW_WORKSHEET_RPTS (in the FLOWS schema). In this table there are two columns that will need to be updated: WORKSHEET_ID and FLOW_ID. I created the following procedure to help with this process (note that if an interactive report was modified as part of the deployment or if the interactive report was moved to another page, a more manual approach will be required. Also, UPDATE will need to be granted on WWV_FLOW_WORKSHEET_RPTS to the executing schema.

create or replace
PROCEDURE transfer_apex_rpts (
   p_old_app_id IN NUMBER
 , p_new_app_id IN NUMBER
)

IS

   l_new_worksheet_id NUMBER;

   FUNCTION worksheet_id (
      p_flow_id IN NUMBER
    , p_page_id IN NUMBER
   )
   
     RETURN NUMBER
     
   IS 
   
      l_worksheet_id NUMBER;
   
   BEGIN
      
      SELECT id
      INTO l_worksheet_id
      FROM flows_030100.wwv_flow_worksheets
      WHERE flow_id = worksheet_id.p_flow_id
         AND page_id = worksheet_id.p_page_id;
         
      RETURN l_worksheet_id;
      
   END;

BEGIN

   IF p_old_app_id IS NULL
   THEN
      raise_application_error('-20001', 'p_old_app_id must be NOT NULL');
   END IF;

   IF p_new_app_id IS NULL
   THEN
      raise_application_error('-20002', 'p_new_app_id must be NOT NULL');
   END IF;
   
   FOR x IN (
      SELECT DISTINCT worksheet_id, page_id
      FROM flows_030100.wwv_flow_worksheet_rpts
      WHERE flow_id = p_old_app_id
   )
   LOOP
      l_new_worksheet_id := worksheet_id(p_new_app_id, x.page_id);
      
      UPDATE flows_030100.wwv_flow_worksheet_rpts
      SET worksheet_id = l_new_worksheet_id
      WHERE worksheet_id = x.worksheet_id;
   END LOOP;
   
   UPDATE flows_030100.wwv_flow_worksheet_rpts
   SET flow_id = p_new_app_id
   WHERE flow_id = p_old_app_id;

   COMMIT;
END;

If nothing else this procedure can provide some insight as to how this can be approached. As always, let me know if you have any questions. This is new to me, so I'm sure the procedure will evolve with time. As it does, I'll update it here.