Wednesday, September 24, 2008

Case sensitive table names in oracle - WTF

We imported an M$Sql database to Oracle and the table names were case sensitive, all like tWHATEVER, tUSERS. Our program was not working because of bad SQL commands. After renaming a table (all uppercase) it becomes case insensitive.

Solution 1: rename all tables by hand using a tool (like TOAD)
Solution 2: write a script that does it for you, renaming by hand hundreds or thousands of tables is not an option. Here's the script, it's not error prone but works.

Known bugs: if table name is already case insensitive, it fails on renaming.

DECLARE
string varchar2(200);
BEGIN
for tabs in (SELECT table_name, upper(table_name) as uppername FROM user_tables)
loop
string := 'alter table ' || tabs.table_name || ' rename to ' || tabs.uppername;
execute immediate string;
end loop;
END;

No comments: