This content has been marked as final. Show 4 replies
Hi,1 person found this helpful
To use single quotes with the DDL statement, you need to double quote the entire parameter.
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.
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
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
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.
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');