Using LPAD with dbms_redefinition to add leading zeros

In the previous post I showed how to change a column definition in a table from varchar to num (or vice-versa) using dbms_redefinition


I had reason to need to pad out a converted varchar column with leading zeros.


begin
DBMS_REDEFINITION.START_REDEF_TABLE(
   uname       => 'MYSCHEMA',
   orig_table  => 'MYTABLE',
   int_table   => 'INT_MYTABLE',
   col_mapping => ' ORDERNUM     ORDERNUM  ,
                              LPAD (to_char (COLUMN_TO_BE CHANGED),3,''0'')  COLUMN_TO_BE CHANGED ,
                              LOAD   LOAD'  ,
options_flag => DBMS_REDEFINITION.CONS_USE_ROWID  );
end;
/

LPad will left pad a field for you, so here I am saying take my number column, turn it into a varchar (see post ) and stick leading zeros on the data that is already in it to make it 3 characters.

For the process you need to follow to actually make it all happen go to the previous post, but am I writing this here because I just know at some point I'm going to go "I know I've done that with redef before, but how the hell did I do it" .  So am I going to leave this here so I kick myself when my own post comes up in the google search that I do - you know that feeling too I'm sure !!!