Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Rename a Table Column Without Any Data Manipulation-3 : Page 3

Step 2: Rename the Column Directly in the Oracle Dictionary
Use the following code to find the user_id for the SCOTT user:

select user_id from dba_users where username = 'SCOTT';

The output for the above query is:

USER_ID --------- 35

Next, use the USER_ID you just retrieved to find the OBJ# for the EMPTEST table from the OBJ$ Oracle dictionary. The following SQL statement will do the trick:

select obj#, name from obj$ where name = 'EMPTEST' and owner# = 35;

The output of the above query is:

OBJ# NAME ---------- ------------------- 24862 EMPTEST

Using OBJ# (24862) for EMPTEST table, you could drill down to find the COL# of the SAL column:

select col#, name from col$ where obj# = 24862 and name = 'SAL';

The output of the above query is:

COL# NAME --------- ------------------- 6 SAL

Now, you are ready to update the row in the COL# Oracle dictionary, which corresponds to the SAL column (COL# = 6) of the EMPTEST table (OBJ# = 24862) in the SCOTT schema:

update col$ set name = 'SALARY' where obj# = 24862 and col# = 6; commit;

Step 3: Package All the Code in the RenameColumn Procedure
Obviously, remembering all the names with # and $, as well as all the commands you issued in Step 2 is a lot to handle. Moreover, since you update the Oracle dictionary directly, you have no room for error in this process. So putting all that code inside one procedure that has proper error handling and optimized SQL makes a lot of sense. With the RenameColumn procedure, all four SQL statements in Step 2 can be combined into one UPDATE statement.

Listing 3 shows all the code for the RenameColumn procedure.

The RenameColumn procedure accepts four self-descriptive parameters:

  • pUser
  • pTableName
  • pOldColumnName
  • pNewColumnName

    The first part of the procedure performs the following five checks:

  • Whether procedure is being run by SYS user
  • Whether pUserName parameter is a valid database user
  • Whether pTableName parameter is a valid table for that user
  • Whether pOldColumnName parameter is an existing column of that table
  • Whether pNewColumnName parameter is a column that does not exist in that table yet

    If any of these checks fail, the corresponding exception is raised. Otherwise, the procedure updates the row in the col$ Oracle dictionary using the UPDATE statement with multiple nested subqueries.

    Once that's done, you are ready to change the column name from SAL to SALARY using the RenameColumn procedure:

    begin RenameColumn('SCOTT', 'EMPTEST', 'SAL', 'SALARY'); end;

  • Comment and Contribute






    (Maximum characters: 1200). You have 1200 characters left.



    Thanks for your registration, follow us on our social networks to keep up-to-date