Converting control files for Oracle SQLLoader

---

Converting control files for Oracle SQL*Loader

This Perl script generates a DB2 load command based on an Oracle control file. The script is tested with:

• GNU Perl v5.8 on Windows 2000

• GNU Perl v5.6 on AIX 5.1

GNU Perl v5.8.0 on Linux has a known bug with the split function used in function parse_field_list().

Example 9-41: Conversion of Oracle control file to DB2 load command

 #!/usr/bin/perl # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #  Script:      conv_ctl.pl #  Author:      Stefan Hummel #  Date:        01/08/2002 # #  Syntax:      perl conv_ctl.pl -c <controlfile> [options] #               -t LOAD | INPORT #               -m INSERT | REPLACE #               -f ASC | IXF # #  Description: Conversion of Oracle control file (*.ctl) to DB2 load file # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # use Text::ParseWords; # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # initialization # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # $infile_name = ''; $table_name  = ''; $mode        = ''; $i           = 0; $column      = 0; $delimiter   = ';'; $method      = 'L'; $type     = 'LOAD'; $mode     = 'INSERT'; $filetype = 'ASC'; # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # functions # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # sub usage {   printf("USAGE: perl conv_ctl.pl -c controlfile [options]\n");   printf("\t-t LOAD | INPORT\n");   printf("\t-m INSERT | REPLACE\n");   printf("\t-f IXF | ASC\n");   exit; } sub read_controlfile {   $delim = '\s+';   $i     = 1;   open(CTLFILE, $_[0]);   @ctl_lines = <CTLFILE>;   foreach (@ctl_lines) {      @words = &parse_line($delim, 1, $_);      foreach (@words) {         if ($_ ne '","') {            @parts = split (/([,])/, $_);            $n = 0;            foreach (@parts) {               $array[$i++] = $parts[$n++];            }         }         else {            $array[$i++] = $_ ;         }      }   }   $max_line = $i-1;   close(CTLFILE); } sub parse_load { } sub parse_data { } sub parse_into {    $i++;    $i++;    $table_name = $array[$i]; } sub parse_characterset { } sub parse_infile {    $i++;    $infile_name = $array[$i]; } sub parse_delimiter {    $i++; $i++; $i++;    $delimiter = substr($array[$i],1,1);    $method = 'P'; } sub set_mode {    if ('' eq $mode) {       $mode = $_[0];    } } sub parse_field_list {    while (")" ne $array[$i]) {       $column++;       $i++;       $field_list[$column][1] = $array[$i];          # 1. column name       $i++;       @position = split (/([:()])/, $array[$i]);       $field_list[$column][2] = $position[2];        # 2. from column       $field_list[$column][3] = $position[4];        # 3. up to column       while (("," ne $array[$i]) && (")" ne $array[$i])) {          $i++;       }    } } # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # main # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # read command parameters while ($parameter=shift(@ARGV)) {    if ("-c" eq $parameter) {       $filename = shift @ARGV;    }    elsif ("-t" eq $parameter) {       $type = shift @ARGV    }    elsif ("-m" eq $parameter) {       $mode = shift @ARGV    }    elsif ("-f" eq $parameter) {       $filetype = shift @ARGV    }; }; if ('' eq $filename) {    &usage; } # read the control file, save each word in array &read_controlfile($filename); # read array and parse commands $i = 1; while ($i <= $max_line) {    if ("LOAD" eq uc($array[$i])) {       &parse_load($i);    }    elsif ("DATA" eq uc($array[$i])) {       &parse_data;    }    elsif ("INTO" eq uc($array[$i])) {       &parse_into;    }    elsif ("CHARACTERSET" eq uc($array[$i])) {       &parse_characterset;    }    elsif ("INFILE" eq uc($array[$i])) {       &parse_infile;    }    elsif ("INSERT" eq uc($array[$i])) {       &set_mode('INSERT');    }    elsif ("APPEND" eq uc($array[$i])) {       &set_mode('APPEND');    }    elsif ("REPLACE" eq uc($array[$i])) {       &set_mode('REPLACE');    }    elsif ("TRUNCATE" eq uc($array[$i])) {       &set_mode('TRUNCATE');    }    elsif ("FIELDS" eq uc($array[$i])) {       &parse_delimiter;    }    elsif ("(" eq uc($array[$i])) {       &parse_field_list;    }    ;    $i++; }; # generate DB2 Load File printf("%s FROM %s of %s\n", $type, $infile_name, $filetype); if ($method eq 'L') {    printf("METHOD %s \n\t( ", $method);    for ($c=1;$c<$column;$c++) {       if (1 < $c) {          printf("\n\t ,");       }       printf("%s %s", $field_list[$c][2]                     , $field_list[$c][3]);    }    printf(")\n"); }; if ($method eq 'P') {    printf("MODIFIED BY COLDEL%s \n", $delimiter);    printf("METHOD %s (", $method);    for ($c=1;$c<$column;$c++) {       if (1 < $c) {          printf(", ");       }       printf("%d", $c);    }    printf(")\n"); }; printf("%s INTO %s \n\t( ", $mode, $table_name); for ($c=1;$c<$column;$c++) {    if (1 < $c) {       printf("\n\t ,");    }    printf("%s", $field_list[$c][1]); } printf(" )!\n"); 

---