Question

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.

Answer 1

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

Answer 2

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.

友情链接