• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1165
  • Last Modified:

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
0
anshuma
Asked:
anshuma
  • 3
  • 2
  • 2
2 Solutions
 
lwadwellCommented:
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.
0
 
anshumaEngineeringAuthor Commented:
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 :-)
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
Just query the dba_tab_cols with the where clause accordingly to find all the columns which you want to rename and they use dynamic sql to execute 'alter table .. rename column ... '

Have a look at the dynamic sql examples and also browse through the other links present in the below url which should help you to understand how dynamic sql can be used to do this.

http://www.oracle-base.com/articles/8i/native-dynamic-sql.php
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
lwadwellCommented:
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.
0
 
anshumaEngineeringAuthor Commented:
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 :-)
0
 
lwadwellCommented:
Doing the ALTER statements one a time from you code might be simpler ... but a stored procedure is
CREATE OR REPLACE STORED PROCEDURE renamecolumns AS
BEGIN
    execute immediate 'alter table MSTR_MD_PRD.dm_reports_smoke_test rename column wjxbfs1 to total_reports_execution';
    execute immediate 'alter table MSTR_MD_PRD.dm_reports_smoke_test rename column wjxbfs2 to succeful_reports_execution';
    execute immediate 'alter table MSTR_MD_PRD.dm_reports_smoke_test rename column wjxbfs3 to failed_reports_execution';
    execute immediate 'alter table MSTR_MD_PRD.dm_reports_smoke_test rename column custcol to day_id';
END';

Open in new window

Permissions and stored proc's can be tricky.  Which user/schema would you add this to and would you run as the same user?  If in MSTR_MD_PRD ... you should be OK I think.
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
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.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now