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;

Oracle - join 2 selects side by side?

Hi, I'm not an SQL guru and this seemingly simple problem made me quite much thinking and searching. That's why I decided to share it.

The problem: I have a table (eg foo) with many columns, for me only two were important:

• name (varchar2, unique)
• code (varchar2, unique)

and I had two selects:

• select name, code from foo order by name
• select code, name from foo order by code

Got it? The only difference is the sorting! Now I needed to join (NOT union) these two selects in a way to prefer the order of both selects. Easy? Give it a try... :)

The solution was Oracle's 'rownum':

SELECT c1_name, c1_code, c2_code, c2_name
   FROM
    (SELECT ROWNUM AS s, c1_name, c1_code
       FROM (SELECT name AS c1_name, code_id AS c1_code 
               FROM foo ORDER BY c1_name)) t1
LEFT JOIN
    (SELECT ROWNUM AS s, c2_code, c2_name
       FROM (SELECT code AS c2_code,name AS c2_name 
               FROM foo ORDER BY c2_code)) t2
ON t1.s = t2.s

I had to wrap the two selects with another select with rownum, when rownum was directly in the first select, the ordering scrambled it. From here you need to only join the two tables using rownums, do it as you wish, I used left join but probably there could be better ways.