This discussion is archived
4 Replies Latest reply: Aug 29, 2012 8:16 AM by ⁸⁷Rb-⁸⁷Sr RSS

Using quotes in dbms_wm.AlterVersionedTable

⁸⁷Rb-⁸⁷Sr Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Pro
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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');

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points