2 Replies Latest reply: Aug 12, 2010 12:45 PM by Barbara Boehmer RSS

    Change table names to uppercase

    689418
      How to change all the tablenames to uppercase in oracle database.
        • 1. Re: Change table names to uppercase
          Ananthram
          Hi,
          I believe Oracle stores the Table name only in uppercase, even if you use lower cases during the CREATE TABLE statement.
          So when you query for the table name from USER_TABLES you will get the table name in UPPERCASE itself.
          This small example below should be helpful:


          SQL> create table lower
          2 ( id number,
          3 name varchar2(10)
          4 )
          5 /

          Table created.

          SQL> select table_name from user_tables where table_name ='lower';

          no rows selected

          --where as the below query will fetch us our table_name.

          SQL> select table_name from user_tables where table_name = 'LOWER';

          TABLE_NAME
          ------------------------------
          LOWER
          Please let me know if this is what you were looking for.

          So I don't see a point in worrying over the case of the table names.

          Cheers!!!
          Ananthram
          • 2. Re: Change table names to uppercase
            Barbara Boehmer
            When you create tables using double quotes around the table name, then the case is preserved and all references to them must also be in double qutoes. You can change all the table names by looping through a cursor that selects from the user_tables data dictionary and dynamically changing them. However, you should be aware that this will invalidate any procedures and such that reference the old names in double quotes. Please see the demo below in which I have create a "Test" table and a procedure that references it, then changed the table_name to upper case, then shown that the procedure is invalid even after recompiling. Also, this question really should have been in the SQL and PL/SQL forum, since it has nothing specifically to do with Objects.
            SCOTT@orcl_11gR2> -- with table and references to it
            SCOTT@orcl_11gR2> -- in  mixed case in double quotes:
            SCOTT@orcl_11gR2> CREATE TABLE "Test" AS
              2  SELECT * FROM dept
              3  /
            
            Table created.
            
            SCOTT@orcl_11gR2> SELECT table_name
              2  FROM   user_tables
              3  WHERE  table_name = 'Test'
              4  /
            
            TABLE_NAME
            ------------------------------
            Test
            
            1 row selected.
            
            SCOTT@orcl_11gR2> SELECT table_name
              2  FROM   user_tables
              3  WHERE  table_name = 'TEST'
              4  /
            
            no rows selected
            
            SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE test_proc
              2    (p_ref OUT SYS_REFCURSOR)
              3  AS
              4  BEGIN
              5    OPEN p_ref FOR
              6    SELECT * FROM "Test";
              7  END test_proc;
              8  /
            
            Procedure created.
            
            SCOTT@orcl_11gR2> SHOW ERRORS
            No errors.
            SCOTT@orcl_11gR2> VARIABLE g_ref REFCURSOR
            SCOTT@orcl_11gR2> SET AUTOPRINT ON
            SCOTT@orcl_11gR2> EXEC test_proc (:g_ref)
            
            PL/SQL procedure successfully completed.
            
            
                DEPTNO DNAME          LOC
            ---------- -------------- -------------
                    10 ACCOUNTING     NEW YORK
                    20 RESEARCH       DALLAS
                    30 SALES          CHICAGO
                    40 OPERATIONS     BOSTON
            
            4 rows selected.
            
            SCOTT@orcl_11gR2> -- change tables to upper case:
            SCOTT@orcl_11gR2> BEGIN
              2    FOR r IN
              3        (SELECT table_name
              4         FROM      user_tables
              5         -- remove where clause for all tables:
              6         WHERE  table_name = 'Test')
              7    LOOP
              8        EXECUTE IMMEDIATE
              9          'ALTER TABLE "' || r.table_name || '"
             10           RENAME TO ' || r.table_name;
             11    END LOOP;
             12  END;
             13  /
            
            PL/SQL procedure successfully completed.
            
            SCOTT@orcl_11gR2> -- results:
            SCOTT@orcl_11gR2> SELECT table_name
              2  FROM   user_tables
              3  WHERE  table_name = 'Test'
              4  /
            
            no rows selected
            
            SCOTT@orcl_11gR2> SELECT table_name
              2  FROM   user_tables
              3  WHERE  table_name = 'TEST'
              4  /
            
            TABLE_NAME
            ------------------------------
            TEST
            
            1 row selected.
            
            SCOTT@orcl_11gR2> EXEC test_proc (:g_ref)
            BEGIN test_proc (:g_ref); END;
            
                  *
            ERROR at line 1:
            ORA-06550: line 1, column 7:
            PLS-00905: object SCOTT.TEST_PROC is invalid
            ORA-06550: line 1, column 7:
            PL/SQL: Statement ignored
            
            
            ERROR:
            ORA-24338: statement handle not executed
            
            
            SP2-0625: Error printing variable "g_ref"
            SCOTT@orcl_11gR2> ALTER PROCEDURE test_proc COMPILE
              2  /
            
            Warning: Procedure altered with compilation errors.
            
            SCOTT@orcl_11gR2> EXEC test_proc (:g_ref)
            BEGIN test_proc (:g_ref); END;
            
                  *
            ERROR at line 1:
            ORA-06550: line 1, column 7:
            PLS-00905: object SCOTT.TEST_PROC is invalid
            ORA-06550: line 1, column 7:
            PL/SQL: Statement ignored
            
            
            ERROR:
            ORA-24338: statement handle not executed
            
            
            SP2-0625: Error printing variable "g_ref"
            SCOTT@orcl_11gR2>