Quick and Dirty Hacks, 2

• Again, from the junk code directory, suppose you want to produce:

insert into table_a (col_a, col_b)
select 1, 'foo' from dual union all
select 1, 'bar' from dual union all
select 4, 'baz' from dual

#!perl
use DBI;
$table = shift or die;
$dbh = DBI->connect("dbi:Oracle:sid","user","pass");
$sth = $dbh->column_info(undef, undef, $table, "%");
$sth->execute();
while ($h = $sth->fetchrow_hashref) {
  $column_name = $h->{COLUMN_NAME};
  push @cols, $column_name;
  if ($h->{TYPE_NAME} =~ /CHAR/) {
    push @data, "''''||$column_name||''''";
  } else {
    push @data, $column_name;
  }
}
## data now contains, for example, qw(col_a ''''||col_b||'''')
## and this will return "col_a||','||''''||col_b||''''"
$select = "select " . join("||','||", @data) . " from $table";
print "insert into $table (" . join(",", @cols) . ")\n";
$r = $dbh->selectcol_arrayref($select);
$s = join "\n", map { 
          "select $_ from dual union all"
        } @$r;
$s =~ s/union all$//;
print "$s\n";