5 Replies Latest reply: Mar 16, 2010 10:15 AM by MRoche RSS

    Creating a workspace via a trigger?

    MRoche
      Hi All

      I am new to using Workspace Manager and Oracle in general within a development environment and I am working on a development where multiple workspaces need to be created whenever a developer wants to make a change to the database via a Java application.

      My ultimate goal is for a workspace to be created before an update is made.
      Can this be done in the form of a trigger that will version enable the tables, sets a workspace name (possibly using the developers OS username and date) and then upon committing the changes disable versioning tables (or will this part form part of an After Update trigger)?

      Alternatively could this be done using a PL/SQL Package or is this not a very wise thing to do at all?

      Regards

      Mark
        • 1. Re: Creating a workspace via a trigger?
          357282
          Hi Mark


          Let me try to explain a normal usage of OWM.

          One main workspace (its actually called LIVE) which is your ROOT and contains the actually committed data.

          Now in OWM if you wished you could create several users and each user can have their own workspace , usually they would be working with data, changing it and seeing what change has done to their model of some kind.
          Let's say at the end of the day they are told to merge their changes to root (live workspace), there is a command for that.

          OWM also supports resolving conflicts if the same data has been changed within two or more workspaces, but that's another topic.

          You see i have described its ideal usage.

          In your case it seems that you want to encapsulate a whole set of changes within "temporary" workspace.

          What might be best is not use OWM for this at all but use savepoints.
          You can use either OWM savepoints or normal Oracle RDBMS savepoints that live within a session (transaction).

          Example:

          I login ; create savepoints
          I start to make changes, update, delete, ....., for each one you can make a savepoints too.
          ... more savepoints .
          Then its time to commit the change or rollback to a given savepoint.

          For instance your Java application would have to be granted admin privileges to "enable versioning" and other operations which isn't a great idea perhaps.


          I hope this insight has given you some thought.
          What i am trying to say , be careful not to misuse OWM , it is a great powerful addon for Oracle , but it has a certain suggest way of using it.
          Otherwise normal Oracle can help you with transaction management, savepoints, rollbacks.

          Cheers

          Serge
          • 2. Re: Creating a workspace via a trigger?
            MRoche
            It might be best if I explain briefly what I am trying to achieve.

            Basically the application I am working on is a means of documenting the database for our main software application for in house analysis; in the same way oracles data dictionary documents the database, the application I am working on will document the data in the data dictionary. The idea being that we have an editable point of reference to what tables and columns are used the various modules of the main application.

            The reason I have been looking into OWM is because ultimately it will be the development team who will be responsible for updating the data in my application, but since the main application is on a constant development track I only want data changes made to my application to be committed at the same time as a software release.
            OWM seemed a logical tools for this.

            I hope this makes sense.

            Regards

            Mark
            • 3. Re: Creating a workspace via a trigger?
              357282
              Hi Mark,

              Take note of what i wrote in the first response in this thread. Whenever you allow your application ADMIN privileges , of creating workspaces ,etc there is always a risk associated with that.

              But this is how you grant those privileges :

              EXECUTE DBMS_WM.GrantSystemPriv ('ACCESS_ANY_WORKSPACE, MERGE_ANY_WORKSPACE,
              CREATE_ANY_WORKSPACE, REMOVE_ANY_WORKSPACE, ROLLBACK_ANY_WORKSPACE',
              'wm_developer', 'YES');




              Let me try to take a stab at your problem.

              Possible solution with the use of OWM would be this:

              Once , create a workspace , call it something meaningful , this workspace will represent your current release.

              In the application , to make change only in the workspace , you use a simply SQL command:
              EXECUTE DBMS_WM.CreateWorkspace ('myReleaseR123');

              at this point your session is working within workspace

              You use this for switching to workspace LIVE or any other:

              EXECUTE DBMS_WM.GotoWorkspace ('LIVE');



              And the final note, once you make all those changes within the R123 workspace , you are ready to click a button and "commit" those changes into the LIVE version of your data: simply make this call:

              EXECUTE DBMS_WM.MergeWorkspace ('myReleaseR123');


              As you can see your application shouldn't create workspaces on its own, that way you lack a lot of control and flexbility.

              I don't want to go into too much detail but simply take out some of the admin tasks from the application, it's a better design that way.

              I hope this is good for your beginning with OWM.

              Serge
              • 4. Re: Creating a workspace via a trigger?
                357282
                Hey Mark

                Let me know what you think about my response. I believe i've answered your question.
                Hopefully it's helpful and understandable to a beginner.
                • 5. Re: Creating a workspace via a trigger?
                  MRoche
                  Yeah, that was great.

                  Many Thanks

                  Mark