6 Replies Latest reply: Apr 22, 2010 7:15 AM by Ben Speckhard-Oracle RSS

    dbms_wm.RollbackTable - invalid column name error

    user2555615
      I can't figure out what's wrong.


      SQL> exec DBMS_WM.RollbackTable('workspace_name', 'test_table', '', 'pk_column = 333');

      BEGIN DBMS_WM.RollbackTable('workspace_name', 'test_table', '', 'pk_column = 333'); END;

      *
      ERROR at line 1:
      ORA-20081: invalid column name specified in the where-clause
      ORA-06512: at "WMSYS.LT", line 11718
      ORA-06512: at line 1




      test_table is a version-enabled table, and pk_column is the primary key for test_table. Any ideas what would cause this?

      Thanks,

      Bill

      Edited by: user2555615 on Apr 14, 2010 4:52 PM
        • 1. Re: dbms_wm.RollbackTable - invalid column name error
          Ben Speckhard-Oracle
          Hi Bill,

          What version of OWM are you using?

          It's hard to say without a trace. If you are able to get a trace of the error, knowing the last query on the test_table_lt table would be helpful.

          Regards,
          Ben
          • 2. Re: dbms_wm.RollbackTable - invalid column name error
            user2555615
            I'm using OWM 10.2.0.4.4. The database is at 10.2.0.3. This function worked on OWM 10.2.0.3.1.

            Here's the part of the trace that fails. I used an example to start this thread. But, this is the actual error. The versioned table is GND1.GEONAME_NOTE_LT and NOTE_GID is the primary key on GEONAME_NOTE. If you need more of the trace, let me know.



            PARSING IN CURSOR #17 len=154 dep=1 uid=49 oct=3 lid=49 tim=1241885901356630 hv=69836998 ad='33796f38'
            select count(*)
            from GND1.GEONAME_NOTE_LT
            where 1 = 2 and '0'='2010-apr-19 15:32:42' and (NOTE_GID = '840FD40A0331EBD2E040A8C0371205D0')
            END OF STMT
            PARSE #17:c=4000,e=4438,p=0,cr=3,cu=0,mis=1,r=0,dep=1,og=1,tim=1241885901356626
            BINDS #14:
            kkscoacd
            Bind#0
            oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
            oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
            kxsbbbfp=2a973091f0 bln=22 avl=03 flg=09
            value=142
            Bind#1
            oacdty=01 mxl=128(79) mxlc=00 mal=00 scl=00 pre=00
            oacflg=13 fl2=206001 frm=01 csi=873 siz=128 off=0
            kxsbbbfp=2a973d79c8 bln=128 avl=79 flg=09
            value="select count(*)%from%gnd1.geoname_note_lt%where 1 = 2 and%2010-apr-19 15:32:42%"
            • 3. Re: dbms_wm.RollbackTable - invalid column name error
              user2555615
              I "fixed" it. Setting cursor_sharing=similar fixes the problem and I'm able to rollback the table. Do you have any idea why that would be a factor at all?

              Also - I had cursor_sharing=exact because of bug 5155885. So, I need to apply the patch for 5155885 for it to be completely "fixed". But, it scares me that changing the cursor_sharing parameter had an effect. It should work regardless of the cursor_sharing parameter.
              • 4. Re: dbms_wm.RollbackTable - invalid column name error
                Ben Speckhard-Oracle
                Hi,

                The error is being raised based on a query that is looking within the cursor for a dependent function or procedure which we do not allow. Is NOTE_GID defined as a function or procedure somewhere else?

                It's hard to say why cursor_sharing=exact would be causing this. Was it happening on all tables or just this one? When using cursor_sharing=exact, I have had no problems with this procedure or any other ones that use a user-defined where clause. It should only change the execution plan, not the end result.

                Regards,
                Ben
                • 5. Re: dbms_wm.RollbackTable - invalid column name error
                  user2555615
                  Ben,

                  NOTE_GID is the primary key column on the table and not defined anywhere else. It's happening on all my versioned tables.

                  What's a "user-defined" where clause? The where clause is generated by Oracle if that helps. This query is executed somewhere within the Rollbacktable procedure.

                  when it works (with cursor_sharing=SIMILAR), the trace looks like this:

                  select count(*)
                  from GEONAME_NOTE_LT
                  where :"SYS_B_0" = :"SYS_B_1" and :"SYS_B_2" = :"SYS_B_3" and (NOTE_GID = :"SYS_B_4" )

                  when it doesn’t work (cursor_sharing=EXACT), the trace looks like this:

                  select count(*)
                  from GND1.GEONAME_NOTE_LT
                  where 1 = 2 and '0'='2010-apr-19 15:32:42' and (NOTE_GID = '840FD40A0331EBD2E040A8C0371205D0')


                  Does that help you at all?

                  Thanks,

                  Bill
                  • 6. Re: dbms_wm.RollbackTable - invalid column name error
                    Ben Speckhard-Oracle
                    I was referring to any of the procedures that have a where_clause parameter. It is specified by the user, and then OWM does a number of checks to make sure it is something that we can use. This is where the error is being raised.

                    Also, the error isn't being raised when executing that query. According to the part of the trace that you posted, that is working as expected. However, immediately afterward there is a query using v$dependent_objects and a couple of other views, and that is where the error is occurring.

                    Anyway, I would recommend filing a SR if you need cursor_sharing=exact in the future. This might be a database bug related to cursor_sharing.

                    Regards,
                    Ben