[SOLVED] Oracle how to uppercase columns name

Issue

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

Solution

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;
/

Answered By – MT0

Answer Checked By – David Marino (BugsFixing Volunteer)