Change table's column name using stored procedure in Oracle PL/SQL

Question

I'm new to Oracle PL/SQL. I got a table name EMP_1812057(SAL, HIRDATE). How to change its column's names by creating a stored procedure? Can i do something like passing new name as a parameter of the procedure and then it will change the column name to that new name? Can you give me some examples, please? Thanks anyway.

Answer 1

I'm not sure what would be that procedure's real purpose (we don't modify column names that frequently, and we rarely do it in a procedure), but - you'd use dynamic SQL with appropriate ALTER TABLE statement.

For example, rename SAL column to SALARY:

SQL> desc emp_1812057
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SAL                                                NUMBER
 HIREDATE                                           DATE

SQL> create or replace procedure p_emp is
  2  begin
  3    execute immediate 'alter table emp_1812057 rename column sal to salary';
  4  end;
  5  /

Procedure created.

SQL> exec p_emp

PL/SQL procedure successfully completed.

SQL> desc emp_1812057
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SALARY                                             NUMBER
 HIREDATE                                           DATE

SQL>