CODEWITHSUNDEEP
oracle-databasefunctionuppercase
user2998243
user2998243

Reputation: 181

Oracle how to uppercase columns name

I would like to create a function who uppercase all columns name, in a oracle db. I don't use oracle everyday, so I need help.

I would like this, but for Oracle :

CREATE OR REPLACE FUNCTION uppercase_fields(schemaname text) RETURNS void AS $$
DECLARE
  r RECORD;
  full_table text;
  geom_type_count integer;
BEGIN
  FOR r IN
    EXECUTE 'SELECT table_name, table_schema, column_name FROM information_schema.columns WHERE table_schema = $1 AND column_name <> upper(column_name)'
      USING schemaname
  LOOP
    EXECUTE 'ALTER TABLE "' || r.table_schema || '"."' || r.table_name || '" RENAME "' || r.column_name || '" to "' || upper(r.column_name) || '"';
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Thanks

Upvotes: 0

Views: 10304

Answers (2)

MT0
MT0

Reputation: 168613

Oracle, as the default functionality, will covert all unquoted table/column identifiers to upper case - so you do not need to use the UPPER function; just leave the identifiers unquoted.

To find the data you require you want the ALL_TAB_COLUMNS or USER_TAB_COLUMNS tables from the data dictionary:

BEGIN
  FOR r IN ( SELECT owner, table_name, column_name
             FROM   ALL_TAB_COLUMNS
             WHERE  owner IN ( 'your', 'list' , 'of', 'tablespaces' )
             AND    column_name <> UPPER( column_name )
           )
  LOOP
    EXECUTE 'ALTER TABLE "' || r.owner || '"."' || r.table_name
            || '" RENAME COLUMN "' || r.column_name || '" TO ' || r.column_name;
  END LOOP;
END;
/

If your column names are reserved words or otherwise cannot be in an unquoted identifier then you can use:

BEGIN
  FOR r IN ( SELECT owner, table_name, column_name
             FROM   ALL_TAB_COLUMNS
             WHERE  owner IN ( 'your', 'list' , 'of', 'tablespaces' )
             AND    column_name <> UPPER( column_name )
           )
  LOOP
    EXECUTE 'ALTER TABLE "' || r.owner || '"."' || r.table_name
            || '" RENAME COLUMN "' || r.column_name || '" TO "' || UPPER(r.column_name) || '"';
  END LOOP;
END;
/

Upvotes: 2

Jamshid Sabet جمشید ثابت
Jamshid Sabet جمشید ثابت

Reputation: 11

For change to uppercase all columns name in Oracle, use this solution:

DECLARE
  TARGET_TABLE_NAME VARCHAR2(31) := 'ENTER_YOUR_TARGET_TABLE_NAME_HERE';
BEGIN
    FOR I IN (
            SELECT
                    COLUMN_NAME
            FROM
                    ALL_TAB_COLUMNS
            WHERE
                    TABLE_NAME = TARGET_TABLE_NAME
            AND
                    COLUMN_NAME <> UPPER(COLUMN_NAME)
        )
    LOOP
        EXECUTE IMMEDIATE 'ALTER  TABLE ' ||
                                TARGET_TABLE_NAME ||
                          ' RENAME COLUMN "' ||
                                I.COLUMN_NAME ||
                          '" TO ' ||
                                UPPER(I.COLUMN_NAME);
    END LOOP;
END;
/

Just replace the name of your target table by ENTER_YOUR_TARGET_TABLE_NAME_HERE

Upvotes: 1

Related Questions