I am trying to rename a column of multiple tables in Oracle using a cursor over a table that has all the information of other table.

In SQL Server I was able to do it correctly using sp_rename. I am not able to find an equivalent in Oracle.

I tried with alter table but my table names are in variable so I am not able to do it. I am using:

execute immediate alter table var_table_name rename var_col to var_new_col

Can anyone provide a method that is similar to sp_rename in Oracle or any other method?


1 Answer 1


Community Wiki answer containing the solution given by Justin Cave in a question comment

execute immediate takes a string. The snippet you posted has no strings in it. You'd need to assemble the appropriate string and then pass that to execute immediate. Something like:

execute immediate 'alter table ' || var_table_name || ' rename ' || 
    var_col || ' to ' || var_new_col`.

Of course, you probably want to put that in a local variable so that if/when it fails you can log the actual SQL statement.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.