CODEWITHSUNDEEP
oracle-databaseindexingoracle-sqldeveloperoracle12c
trinadh
trinadh

Reputation: 248

Oracle Create Index with DBMS_SQL.Parse

I am Trying to Create Index like this below:

DECLARE l_cursor NUMBER;
  l_return NUMBER;
  v_stmt CLOB;
BEGIN
  v_stmt:= TO_CLOB('CREATE INDEX IX_Job ON "JOBSTEP"("JOBID") TABLESPACE "USERS";');
  l_cursor  := DBMS_SQL.open_cursor;
  DBMS_SQL.parse(l_cursor, v_stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.CLOSE_CURSOR(l_cursor);  
END;

Please find the below error on executing :

ORA-02158: invalid CREATE INDEX option

ORA-06512: at "SYS.DBMS_SQL", line 1185 ORA-06512: at line 7 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.

If i execute CREATE INDEX IX_Job ON "JOBSTEP"("JOBID") TABLESPACE "USERS"; like this Index is getting created.

Using Oracle 12c, I tried all this using sql developer. Please someone help me to resolve this. Is there anything wrong with my code?

Upvotes: 1

Views: 3288

Answers (1)

trinadh
trinadh

Reputation: 248

The comment from the question helped by removing the semicolon from the v_stmt line.

Here's the updated code

DECLARE l_cursor NUMBER;
l_return NUMBER;
v_stmt CLOB;
BEGIN
  v_stmt:= TO_CLOB('CREATE INDEX IX_Job ON "JOBSTEP"("JOBID") TABLESPACE "USERS"');
  l_cursor  := DBMS_SQL.open_cursor;
  DBMS_SQL.parse(l_cursor, v_stmt, DBMS_SQL.NATIVE);
  DBMS_SQL.CLOSE_CURSOR(l_cursor);  
END;

Thanks for the help

Upvotes: 1

Related Questions