Search code examples
oracle-databaseddl

Oracle rename a column with a selected value

I need to rename a table column giving it a new name that is selected from another table, like this (I'm using a fake code to let you understand):

Alter table CUSTOMERS
Rename column OCCUPATION to (select ITA_NAME from TAGS where ENG_NAME = 'occupation')

Is it possible?

Solution

  • It does not seem like a wise thing to want to do but you can use dynamic SQL

    declare
  l_new_column_name varchar2(30);
begin
  select ITA_NAME 
    into l_new_column_name
    from TAGS 
   where ENG_NAME = 'occupation';

  execute immediate 'Alter table CUSTOMERS ' ||
                    '  Rename column OCCUPATION to ' || l_new_column_name;
end;

    Note that this code does nothing to prevent SQL injection attacks. You'd probably want to at least use the dbms_assert package to ensure that l_new_column_name doesn't contain anything malicious.