5 Replies Latest reply: Aug 11, 2010 1:08 PM by Ben Speckhard-Oracle RSS

    BeginDDL causes ora-00942 table or view does not exist

    763539
      Hello!

      When a I'm trying to add a new column to a version enabled table, the beginddl fails with ora-00942, says the table not exists. It is really strange because I have done a lot of alterations that way before on that table. Now, non of the version enabled tables could be altered, beginddl always returns with 00942. I have checked the ALL_WM_VERSIONED_TABLES, all of my verison enabled tables are there. The schema has WM_ADMIN_ROLE and has CREATE TABLE grant, and has priviliges to access any versioned table (view). Also got the synonyms.
      The last succesful alter with beginddl was happened two weeks ago. My guess is that something in the environment has changed, because it is a global problem. Don't know what else to look after, and the other people who could have altered the system are on holyday sadly. Please help!

      Regards,
      Viktor
        • 1. Re: BeginDDL causes ora-00942 table or view does not exist
          Stefan Jager
          Have you checked ALL_WM_VT_ERRORS?
          If so, does it say anything?
          And what does it say?
          • 2. Re: BeginDDL causes ora-00942 table or view does not exist
            763539
            Hy!

            I have found the problem yesterday night. By checking the ALL_WM_VT_ERRORS I found that one of the triggers on the table had became unfunctional, beacuse one of it's code's row which was originaly commented out by -- sign became uncommented in the code that the workspace manager had genereated from it. I only can guess that the problem with that row of code was mainly that it has the original table name in it, which could make the code generator of the workspace manager fail. I don't mark the question answerered yet, I would like to find out more about that issue. Should I use /* */ always for commenting?
            Btw, I had to disable the versioning of the table to fix the problem, using both disableversioning('TABLE_NAME', true) to force disable, and disableversioning('TABLE_NAME', ignore_last_error=>true). After deleting the commented line and recompile the trigger it was all ok.

            Viktor
            • 3. Re: BeginDDL causes ora-00942 table or view does not exist
              Ben Speckhard-Oracle
              Hi,

              What version of Workspace Manager are you using?

              You should be able to use either type of comment within triggers. Was there a quote within the comment? If so, you may have run into a bug that has since been fixed. Upgrading to the latest version should solve the problem. If not, I would be interested in seeing the trigger body.

              Regards,
              Ben
              • 4. Re: BeginDDL causes ora-00942 table or view does not exist
                763539
                Hi Ben,

                We are using 11.1.0.7.0.

                I'm including the whole script we run to create the trigger:

                --exec dbms_wm.beginddl('VEZSZAKOK');

                create or replace trigger TRG_VEZSZAKOK_AUR
                -- after update on VEZSZAKOK_LTS for each row
                after insert or update on VEZSZAKOK for each row
                begin
                PKG_MIR_GRAPH.P_GRAFIKA_FRISSITO(:NEW.MSLINK);
                end;
                /

                --exec dbms_wm.commitddl('VEZSZAKOK');

                As you can see we have kept the beginddl and commitddl rows just like the row with the _LTS table commented out, if we would like to run it on the table after version enabling it.

                Regards,
                Viktor
                • 5. Re: BeginDDL causes ora-00942 table or view does not exist
                  Ben Speckhard-Oracle
                  Hi Viktor,

                  This does appear to be a bug. The comment in the description part of the trigger definition isn't expected. As a result, the schema name is appended to the wrong table name. It would have worked if the comment came after the actual description. Also, this affects both types of comments.

                  If you need a fix for 11.1.0.7, I would suggest filing a SR.

                  Regards,
                  Ben