Oracle 11g – Equivalent of sp_rename in SQL Server

oracleoracle-11g

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?

Best Answer

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.

Related Question