Query to rebuilt domain index

Question

Create or replace procedure REBUILD_X_TEXT
IS
Begin

 
EXECUTE IMMEDIATE 'ALTER INDEX Schema.table_name rebuild parameters('REPLACE metadata sync(every "SYSDATE+15/1440")')'; 
EXECUTE IMMEDIATE 'ALTER INDEX Schema.table_name NOPARALLEL';

End REBUILD_X_TEXT;

It's giving an error: PLS-00103: Encountered the symbol "REPLACE" 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

Answer 1

If you have single quotes in your EXECUTE IMMEDIATE statement, they need to be escaped by using two single quotes in a row like this:

CREATE OR REPLACE PROCEDURE REBUILD_X_TEXT
IS
BEGIN
    EXECUTE IMMEDIATE 'ALTER INDEX Schema.table_name rebuild parameters(''REPLACE metadata sync(every "SYSDATE+15/1440")'')';

    EXECUTE IMMEDIATE 'ALTER INDEX Schema.table_name NOPARALLEL';
END REBUILD_X_TEXT;

Answer 2

Sometimes it's easier to use q-literals for such things:

EXECUTE IMMEDIATE q'[
   ALTER INDEX Schema.table_name 
   rebuild parameters('REPLACE metadata 
                       sync(every "SYSDATE+15/1440")
                       '
                     )
   ]'; 
EXECUTE IMMEDIATE 'ALTER INDEX Schema.table_name NOPARALLEL';