asked on

sql or stored procedure to alter multiple column names of a table

Hi,

I am creating a datamart from a sofwtare tool that is creating a table with random column names (I have no control over it)

their technical support says that after the datamart creation is successful they offer a way to issue a post sql statement via which I can modify the column names.

The problem I have is I need to modify three column names from one sql

alter table random_table_name RENAME COLUMN abc to 123, def to 456, ghi to 789

If I do that then I am getting odbc errors from oracle.

How can i do this ? If sql doesn't allow it then can you write a stored procedure for me which I can then compile on my database and then call the procedure from the post sql statement

Lee Wadwell

Column names (unless quoted) must start with an alpha ... so 123 is not allowed, X123 is.
I think you can only rename one column at a time ... try 3 ALTER statements to do your example.

Amit

ASKER

Hi Lwadwell,

This was just an example. My column names are only alpha :-) I could do three alter statemets like this

alter table MSTR_MD_PRD.dm_reports_smoke_test rename column wjxbfs1 to total_reports_execution;
alter table MSTR_MD_PRD.dm_reports_smoke_test rename column wjxbfs2 to succeful_reports_execution;
alter table MSTR_MD_PRD.dm_reports_smoke_test rename column wjxbfs3 to failed_reports_execution;
alter table MSTR_MD_PRD.dm_reports_smoke_test rename column custcol to day_id;

Can you write a stored procedure for this as I don't know how to do that :-)

SOLUTION

Naveen Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Lee Wadwell

Why do you need a stored procedure for this? Why do you need to "modify three column names from one sql"? This should only be a once-off process and creating an object in the database that will only be executed once seems a bit overboard to me. You could run the ALTER statements in any of the many Oracle SQL front-ends ... e.g. SQL*Plus, SQL Developer, TOAD, ... etc.

Amit

ASKER

I need this stored procedure as the table gets rewritten everyday from the front end tool and as a last step of that I want to rename the columns by executing the stored procedure. If it was a one of thing then I will never bother the experts :-)

ASKER CERTIFIED SOLUTION

Lee Wadwell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Naveen Kumar

Please help to have an exception handling section to the code block to find/handle if any errors otherwise it would be difficult to understand which statement is erroring out and why especially in dynamic sql.