How to rename multiple columns in oracle using one Alter table statement?

Question

The only thing I found is renaming one column at a time:

ALTER TABLE table_name
RENAME COLUMN old_name TO new_name;

I read Oracle documentations, and couldn't get the answer for many columns at a time .

Ref: https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqljrenamecolumnstatement.html

Solution 1

It is not possible to rename multiple table columns in a single command, as of Oracle 18c.

The Oracle 18c SQL Language Reference includes the below diagram to illustrate how the RENAME_COLUMN_CLAUSE of the ALTER TABLE command works. Unfortunately, almost every column property can be modified in groups, except for renaming.

Solution 2

You can use user_tab_columns dictionary view as a data source within a cursor for a loop statement

declare
  v_table_name varchar2(40):='mytable';
begin
  for c in ( select from user_tab_columns where table_name = upper(v_table_name) )
  loop
    execute immediate ('ALTER TABLE '||c.table_name||' RENAME COLUMN '||c.column_name
                                                   ||' TO new_'||c.column_name);
  end loop;
end;

Comments

[mathguy]

I suspect "old_name" and "new_name" in the OP's post were generic - he may want to rename "baseball" to "MLB" and "basketball" to "NBA" for example (using US names). The solution you propose can be adapted for this more generic understanding of the problem, but it WILL be more complicated.

[Barbaros Özhan]

@mathguy you may be right, but we have not enough clue about the renaming strategy.