SQL Developer is a pretty mouse-heavy tool. By that I mean that many of the functions are accessed via mouse-clicks. When browsing tables in the tree you may have noticed that you can expand a table node to display the table columns.
There are some pretty cool features available under the context menu when clicking on a table column:
Some of these actions are available under the table right-clicks, but a couple are not, ‘Encrypt’ and ‘Decrypt.’
One that you may find useful is a dynamic table refactoring feature called ‘Normalize.’
Normalize a Table Column
Sometimes you find a table that has all the data in it. Like a column called ‘Country.’ And instead of having a countryID which points to a child country table record via a foreign key constraint, they just have the text value as part of the ‘master’ record. This isn’t ideal for many situations but it can be a pain to fix after-the-fact.
This SQL Developer feature will ask you a few questions then generate a dynamic script to split that data out to a new table, and populate new IDs in the look-up table.
Wait, this won’t work! I’m trying to do this on a partitioned table. Lots of things you can’t do column wise when a table is partitioned. So let’s try again on another table – my iTunes table I created in my earlier ‘Free Beer’ post.
Here’s the script that SQL Developer generates:
declare l_sql varchar2(32767); l_col_size number; procedure run(p_sql varchar2) as begin execute immediate p_sql; end; begin for r in ( select data_type from sys.all_tab_cols where owner = 'HR' and table_name = 'ITUNES_MUSIC' and column_name = 'ARTIST' ) loop if ( r.data_type not in ('VARCHAR','VARCHAR2','CHAR','NCHAR','NVARCHAR','NVARCHAR2') ) then raise_application_error (-20001,'Normalize only supported on VARCHAR,VARCHAR2,CHAR,NCHAR,NVARCHAR,NVARCHAR2'); end if; end loop; select data_length into l_col_size from sys.all_tab_cols where owner = 'HR' and table_name = 'ITUNES_MUSIC' and column_name = 'ARTIST'; run('create table MUSIC_ARTISTS (ARTIST_ID number not null primary key, ARTIST varchar2('||l_col_size||') not null)'); run('create sequence ARTIST_SEQ'); run('create or replace trigger "T_ARTIST_ID_SEQ"'|| ' before insert or update on '|| ' MUSIC_ARTISTS for each row '|| ' begin '|| ' if inserting and :new.ARTIST_ID is null then '|| ' for c1 in (select ARTIST_SEQ.nextval nv from dual) loop '|| ' :new.ARTIST_ID := c1.nv; '|| ' end loop; '|| 'end if; '|| 'end;'); run('insert into MUSIC_ARTISTS( ARTIST ) select distinct ARTIST from "HR"."ITUNES_MUSIC" where ARTIST is not null'); run('alter table "ITUNES_MUSIC" add ARTIST_ID number'); run('update "ITUNES_MUSIC" x set ARTIST_ID = (select ARTIST_ID from MUSIC_ARTISTS where ARTIST = x.ARTIST)'); run('alter table "ITUNES_MUSIC" drop column ARTIST'); run('alter table "ITUNES_MUSIC" rename column ARTIST_ID to ARTIST'); run('alter table "ITUNES_MUSIC" add foreign key (ARTIST) references MUSIC_ARTISTS(ARTIST_ID)'); end;
Once I click ‘OK’ on the dialog, the script will run and I’ll have:
- A new table with the Artist column refactored and linked to the original table
- The existing table data has changed with the old column rebuilt and pointing to the new table
- A new sequence and trigger setup to handle new records on an INSERT
Here’s a picture of what happened (you can build your own model using the ctrl-drag-and-drop trick.)