[PL/SQL]EXECUTE IMMEDIATE CREATE INDEX is failing with invalid CREATE INDEX option

Question

For every table_name I need to create index based on index_required returned from specified query I wrote below PL/SQL Procedure:

DECLARE
  sIndexRequired  VARCHAR(50);
  sTableName      VARCHAR(50);
  cSQL             CLOB ;
BEGIN

  FOR r IN [...] --I'm getting index_required and table_name from here
    
  LOOP
    
        sIndexRequired := r.index_required;
        sTableName     := r.table_name;
        
       EXECUTE IMMEDIATE 'CREATE INDEX ' || sTableName || 
        CASE WHEN sIndexRequired = 'XXX'  THEN '_YYY' 
             [...] -- more case when cases
        END
        || ' ON ' || sTableName || ' (' || sIndexRequired || ') TABLESPACE xyz;';
        
  END LOOP;
  END;
/

But it's failing with

Error report -
ORA-02158: invalid CREATE INDEX option
ORA-06512: at line 46
ORA-06512: at line 46
02158. 00000 -  "invalid CREATE INDEX option"
*Cause:    An option other than COMPRESS, NOCOMPRESS, PCTFREE, INITRANS,
           MAXTRANS, STORAGE, TABLESPACE, PARALLEL, NOPARALLEL, RECOVERABLE,
           UNRECOVERABLE, LOGGING, NOLOGGING, LOCAL, or GLOBAL was specified.
*Action:   Choose one of the valid CREATE INDEX options.

Can anyone has idea how can I get this work?

Thanks, Michał

Answer 1

Currently, you're blind and have no idea what you are doing.

Instead of EXECUTE IMMEDIATE,

• declare local VARCHAR2 variable large enough to contain the whole CREATE INDEX statement
• compose the statement and put it into the variable
• display its contents on the screen (use DBMS_OUTPUT.PUT_LINE)
• copy/paste it and execute it
  • if it is not clear what's wrong at the first sight, Oracle will now tell you what went wrong. If you use SQL*Plus, an asterisk * will point exactly to the failing spot
• fix the error
• run the procedure again

Once you're sure that you did it right, remove print to screen and use EXECUTE IMMEDIATE.

---

As commented, it turns out that it was the trailing semi-colon within EXECUTE IMMEDIATE that caused problems.

No : || ') TABLESPACE xyz;';
Yes: || ') TABLESPACE xyz';