cleansing table and column names

What kind of joker thought "BASE POC'S" would make a good column name? Sheesh, MS Access. (Not to mention apostrophe abuse. See Bob's Quick Guide to the Apostrophe, You Idiots.)

To my surprise, I googled "script rename invalid columns oracle" and got nothing useful. So here's a chance to play with Oracle regular expressions.

You may be thinking, "If they're illegal column names, how can they get in the database in the first place?" With "quotation marks", that's how. In fact, you can

CREATE TABLE "!!!My Rockin' Table!!!"
  ( "*^@(@  @!!!" VARCHAR2(30) 
                  CONSTRAINT " -- d00d --" PRIMARY KEY,
    "miXiNG CASE is TEH KEWList" VARCHAR2(30) ); 

and, not only will all your hideous names be used verbatim, but they will be case-sensitive. If you take advantage of this feature, you should be slapped. I guess the reason Oracle makes this possible in the first place is so that an import can be accepted from a source - like MS Access - regardless of how awful its names are.

Anyway, here's a PL/SQL cure for bad table and column names.
(link to code)

/*
Renames tables and columns to be Oracle-legal.

SET LINESIZE 200 before running to avoid unwanted page breaks.
Run from an account with privileges to
  - access v$reserved words 
  - alter the appropriate tables

Thanks to Eddie Awad for his description of v$reserved words:
http://awads.net/wp/2007/01/10/what-does-reserved-y-really-mean/

Catherine Devlin, catherinedevlin.blogspot.com, 11 May 2007, v0.1
*/
CREATE OR REPLACE PROCEDURE cleanse_names IS
  new_obj_name VARCHAR2(30);
  CURSOR tbl_names IS
    SELECT owner,
           table_name
    FROM   all_tables
    WHERE  dropped = 'NO';
  CURSOR col_names IS
    SELECT owner,
           table_name,
           column_name
    FROM   all_tab_columns
    JOIN   all_tables USING (table_name, owner)
    WHERE  dropped = 'NO';

  PROCEDURE do(this IN VARCHAR2) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(this || ';');
    -- Uncomment if you're feeling decisive
    -- EXECUTE IMMEDIATE(this);
  END do;
  FUNCTION legalize(name IN VARCHAR2, replacement_letter IN VARCHAR2)
  RETURN varchar2
  IS
    new_name VARCHAR2(30);
    is_reserved NUMBER(11,0);
  BEGIN    
    -- must begin with a letter
    new_name := REGEXP_REPLACE(name,'^[^[:alpha:]]',replacement_letter);
    -- and contain only letters, numbers, #,_,$
    new_name := REGEXP_REPLACE(new_name,'[^[:alnum:]#_$]','_');
    -- and mixing case is evil
    new_name := UPPER(new_name);
    -- and we must respect Oracle reserved words
    SELECT count(*)
    INTO   is_reserved
    FROM   v$reserved_words
    WHERE  keyword=new_name
    AND    (reserved='Y' OR res_semi='Y');
    IF is_reserved > 0
    THEN
      new_name := SUBSTR(new_name, 1, 29) || '_';
    END IF;
    RETURN new_name;
  END legalize;
BEGIN
    DBMS_OUTPUT.PUT_LINE('set scan off');
    FOR col IN col_names
    LOOP
      new_obj_name := legalize(col.column_name, 'C');
      IF new_obj_name != col.column_name
      THEN
        do('ALTER TABLE ' || col.owner ||
           '."' || col.table_name ||
           '" RENAME COLUMN "' || col.column_name ||
           '" TO ' || new_obj_name);
      END IF;
    END LOOP;
    FOR tbl IN tbl_names
    LOOP
      new_obj_name := legalize(tbl.table_name, 'T');
      IF new_obj_name != tbl.table_name
      THEN
        do('ALTER TABLE ' || tbl.owner ||
           '."' || tbl.table_name ||
           '" RENAME TO ' || new_obj_name);
      END IF;
    END LOOP;
END cleanse_names;