CODEWITHSUNDEEP
sqloracle-databaseoracle11g
RRUZ
RRUZ

Reputation: 136391

Normalize columns names in ORACLE 11g

I need remove the quotes from the names of the columns in many tables in my schema. there is any way to automate this process?, any function in oracle or some tool that allows me to change the names of the columns removing the quotes. I am using oracle 11g.

UPDATE

I'm sorry, I had to rephrase my question.

thanks in advance.

Upvotes: 2

Views: 4168

Answers (2)

Justin Cave
Justin Cave

Reputation: 231651

If you just want to get rid of all the case sensitive column names

SQL> create table foo ( "x" number );

Table created.

SQL> ed
Wrote file afiedt.buf

  1  begin
  2    for x in (select *
  3                from user_tab_cols
  4               where column_name != UPPER(column_name))
  5    loop
  6      execute immediate 'ALTER TABLE ' || x.table_name ||
  7        ' RENAME column "' || x.column_name || '"' ||
  8        ' TO ' || upper(x.column_name);
  9    end loop;
 10* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> desc foo
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 X                                                  NUMBER

Upvotes: 4

dcp
dcp

Reputation: 55434

I assume here by "fields" you mean "column names".

Keep in mind that column names in Oracle are not case sensitive unless you put them in quotes when creating the table. It's generally not a good idea to use quotes around the column names when creating the table. In other words, if you create the table like this:

CREATE TABLE FOO (
  colUMN1 varchar2(10),
  CoLumn2 number(38)
)

Then you can still run select statements like this:

SELECT column1, column2 FROM FOO

You can also do this:

SELECT COLUMN1, COLUMN2 FROM FOO

Also note that if you run this query, you'll see that Oracle stored the column names as uppercase in the data dictionary:

SELECT * FROM ALL_TAB_COLUMNS WHERE TABLE_NAME = 'FOO'

So there's no need to rename these columns to all uppercase. The queries you write can use all uppercase column names (assuming the tables weren't created using quotes around the column names) and they'll work fine. It's generally a bad idea to try to force them to be case sensitive.

Upvotes: 6

Related Questions