4 Replies Latest reply: Aug 29, 2012 10:16 AM by ⁸⁷Rb-⁸⁷Sr RSS

    Using quotes in dbms_wm.AlterVersionedTable

    ⁸⁷Rb-⁸⁷Sr
      Hi,
      I try to create custom spatial index in LT table, but get the error:
      exec dbms_wm.AlterVersionedTable('USER.TABLE','DDL', 'ddl=create index  user.TABLE_IN on  user.TABLE_LT (geom) indextype is mdsys.spatial_index PARAMETERS(''layer_gtype=POINT  sdo_non_leaf_tbl=TRUE tablespace=custom_tbs'') parallel, force=true', true);}
      
      14:39:40 Error: ORA-20171: WM error: invalid parameter_options string specified /* by quotes??
      ORA-06512: at "WMSYS.LT", line 14673
      ORA-06512: at line 1
      The simple index creation works fine:
      exec dbms_wm.AlterVersionedTable('USER.TABLE','DDL', 'ddl=create index  user.TABLE_IN on  user.TABLE_LT (geom) indextype is mdsys.spatial_index, force=true', true);
      Can I create a custom indexes in above procedure? Or I must use use only dbms_wm.BeginDDL/CommitDDL ?

      Oracle 10.2.0.5
        • 1. Re: Using quotes in dbms_wm.AlterVersionedTable
          Ben Speckhard-Oracle
          Hi,

          To use single quotes with the DDL statement, you need to double quote the entire parameter.

          For example,
          exec dbms_wm.AlterVersionedTable('USER.TABLE','DDL', 'ddl="create index user.TABLE_IN on user.TABLE_LT (geom) indextype is mdsys.spatial_index PARAMETERS(''layer_gtype=POINT sdo_non_leaf_tbl=TRUE tablespace=custom_tbs'') parallel", force=true', true);

          However, adding/dropping spatial indexes is supported by beginDDL/commitDDL and so is the recommended way to do it.

          Regards,
          Ben
          • 2. Re: Using quotes in dbms_wm.AlterVersionedTable
            ⁸⁷Rb-⁸⁷Sr
            When I remove ignore_last_error=>true, I get another error in this case:

            10:33:05 Error: ORA-06550: line 2, column 158:
            PLS-00103: Encountered the symbol "LAYER_GTYPE" when expecting one of the following:
            * & = - + ; < / > at in is mod remainder not rem return
            returning <an exponent (**)> <> or != or ~= >= <= <> and or
            like LIKE2_ LIKE4_ LIKEC_ between into using || multiset bulk
            member SUBMULTISET_
            ORA-06512: at "WMSYS.LT", line 14673
            ORA-06512: at line 1

            If I not remove ignore_last_error=>true, the procedure is performed but does nothing

            Edited by: ⁸⁷Rb-⁸⁷Sr on 22.08.2012 23:37
            • 3. Re: Using quotes in dbms_wm.AlterVersionedTable
              Ben Speckhard-Oracle
              Hi,

              That is because the SQL statement is being ignored when using the parameter. Not sure about the error. I would trace the session and see exactly what create index statement is being executed.

              Regards,
              Ben
              • 4. Re: Using quotes in dbms_wm.AlterVersionedTable
                ⁸⁷Rb-⁸⁷Sr
                Hi Ben!

                Thank you. The trace file shows that correct SQL should be the
                exec dbms_wm.AlterVersionedTable('USER.TABLE','DDL', 'ddl="create index user.TABLE_IN on user.TABLE_LT (geom) indextype is mdsys.spatial_index PARAMETERS(''''layer_gtype=POINT sdo_non_leaf_tbl=TRUE tablespace=custom_tbs'''') parallel", force=true');