CODEWITHSUNDEEP
oracle-databaseplsqlalter
Nguyen Bot
Nguyen Bot

Reputation: 23

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

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.

Upvotes: 0

Views: 959

Answers (1)

Littlefoot
Littlefoot

Reputation: 143083

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>

Upvotes: 1

Related Questions