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;
