Migrate Oracle backup script to H2(mode=ORACLE) script

oracle_h2_migrator.rb

def migrate_script ( input_file_name , output_file_name )
  input_file = File.open( input_file_name , 'r')   
  loc = 0
  File.open(output_file_name, 'w') do | output_file | 
    input_file.each do | line |
      output_file.puts clean_line(line)
      loc += 1
    end
  end  
  puts "#{input_file_name} -> #{output_file_name} : #{loc} lines migrated"
end

def clean_line( line )
  clean_line = line.gsub       "DROP SEQUENCE", "-- DROP SEQUENCE"
  clean_line = clean_line.gsub "CREATE SEQUENCE", "-- CREATE SEQUENCE"
  clean_line = clean_line.gsub "cascade constraints", "IF EXISTS cascade constraints"
  clean_line = clean_line.gsub "REM", "-- REM"
  clean_line = clean_line.gsub "ALTER TABLE", "-- ALTER TABLE"
  clean_line = clean_line.gsub "REFERENCES", "-- REFERENCES"
  return clean_dates(clean_line)
end

def clean_dates( line )
  clean_line = line.gsub       "to_timestamp","PARSEDATETIME"
  clean_line = clean_line.gsub "'DD-MON-RR HH.MI.SS.FF AM'","'dd-MM-yy HH.mm.ss.SS a'"
  clean_line = clean_line.gsub "-JAN-","-01-"
  clean_line = clean_line.gsub "-FEB-","-02-"
  clean_line = clean_line.gsub "-MAR-","-03-"
  clean_line = clean_line.gsub "-APR-","-04-"
  clean_line = clean_line.gsub "-MAY-","-05-"
  clean_line = clean_line.gsub "-JUN-","-06-"
  clean_line = clean_line.gsub "-JUL-","-07-"
  clean_line = clean_line.gsub "-AUG-","-08-"
  clean_line = clean_line.gsub "-SEP-","-09-"
  clean_line = clean_line.gsub "-OCT-","-10-"
  clean_line = clean_line.gsub "-NOV-","-11-"
  clean_line = clean_line.gsub "-DEC-","-12-"
  return clean_line
end

Nice!

You can also use

    public static Timestamp to_timestamp(String dateText, String oraclePattern) throws Exception {
        String javaDatePattern = null;
        if (oraclePattern.equals("DD-MON-RR HH.MI.SS.FF AM")) {
            javaDatePattern = "dd-MMM-yy HH.mm.ss.SS a";
        }
        if (javaDatePattern==null) {
            throw new RuntimeException("Unhandled oracle date pattern '" + oraclePattern + "'");
        }

        SimpleDateFormat sdf = new SimpleDateFormat(javaDatePattern);
        Date date= sdf.parse(dateText);
        return new Timestamp(date.getTime());
    }

and bind it in H2:

CREATE ALIAS to_timestamp FOR "MyClass.to_timestamp";

commit;

for the date conversions.

The MMM part of the date pattern string handles JAN, FEB, etc.

Hi! yes I could have done that (and as you can see, I'm still a ruby newbie) but I needed to do it outside a java-context, just migrate and run h2 as it is.

greetings :-)

This just helped me immensely! Thank you so much.