CODEWITHSUNDEEP
sqlplsqlsyntax-error
JohnD
JohnD

Reputation: 353

How to create index through a dynamic sql?

I need a create index through a dynamic sql with input parameters index name, table name, and column name.

I was able to compile the below procedure that contains the dynamic sql but when I tried to execute it, it gave me "invalid CREATE command"

    create or replace procedure cr_ind
(p_index in varchar2, 
p_table in varchar2, 
p_column in varchar2)

as
  stmt_txt varchar2(200);
Begin  
  stmt_txt:= 'create index'
             ||p_index
             ||'on'
             ||p_table
             ||'('
             ||p_column
             ||','
             ||p_column2
             ||',' 
             ||p_column3
             ||')';
execute immediate stmt_txt;
    end;
    /
execute cr_ind('name_inx','clone_dummy2','dummy_name');

How do I make it work or is it even possible to create indexes dynamically?

Thanks

Upvotes: 0

Views: 2778

Answers (1)

Andrew
Andrew

Reputation: 27294

p_column2, p_column3 are not being passed in so its going to generate an invalid piece of SQL. I'm surprised the procedure is not refusing to compile because of that.

Given your exec, the stmt_txt would be:

create index name_inx on clone_dummy2 (dummy_name,,)

Can it be done - yes, but you have to still make sure that the SQL being generated to be executed is valid and you capture the error scenarios and deal with them. I would also take a step back and ask - why am I doing indexes on the fly in this manner, there are scenarios that it makes sense, but I would want to validate that before doing it.

Upvotes: 1

Related Questions