Wednesday, 21 May 2008

Using Regular Expression To Resolve Multiple Issues

One way to resolve issues which occur too many times to fix by hand in the generation script is to use a regular expression to search and replace some problematic code.

Shiva Ramakrishnan provided these regular expressions
\(['][A-Za-z_ ]*\)& ----------------à \1'||chr(38)||'


\(['][A-Za-z_ ]*\)& \([A-Za-z_ ]*[']\)--------------------------à \1'||chr(38)||'\2


\(raw(\)\([0-9]*\)\() default 0\) ----------------------------------à \1\2) default '0'


\('12/31/9999'\) -------------------------------------------à to_date(\1,'mm/dd/yyyy')


CAST('9999-12-31' AS DATE) ----------àto_date(‘12/31/9999’,’mm/dd/yyyy’)

  • First one is replacing ‘&’ with CHR(38). SQLPLUS provides mechanism to overlook this. So this is not a major concern.
  • The second one is where RAW datatypes are assigned DEFAULT 0. I am reassigning with DEFAULT ‘0’
  • The third is a case of DATE Fields assigned as is. I think NLS_DATE parameters can be SET to overlook this. I am doing a global replacement.
Hope to have these automatically fixed soon.