use Perl

• URL (Score:1)

  by melopt (2533)

  So where do we download this? ;)

  --
  life is too short
  • Re:URL (Score:2)

    by Ovid (2709) on 2004.07.09 17:27 (#32329) Homepage Journal

    It still has work a fair amount of work that it needs and it's specific to Oracle, but if you want to see the core of it (fair warning, it's a sloppy hack):

    use Text::CSV_XS;;
    
    my $dbh = DBI->connect(...);
    my $sql = @ARGV > 1
        ? join ' ' => @ARGV
        : shift;
    
    $sql = munge_sql($dbh, $sql) || die "No sql supplied";
    
    if ($sql =~ /^\s*desc\s*(\w+)/) {
        desc_tables_and_exit($dbh, $sql, $1);
    }
    
    print "Executing ($sql)\n" if $ENV{DEBUG};
    my $sth;
    {
        local $^W; # stop some silly internal warning
        $sth = $dbh->prepare($sql);
    }
    
    $sth->execute;
                                                                                                                                                                 my $csv = Text::CSV_XS->new
    while (my @data = $sth->fetchrow_array) {
        my $status = $csv->combine(@data);
        print $csv->string(), "\n";
    }
    
    $dbh->disconnect;
    
    sub desc_tables_and_exit {
        my ($dbh, $sql, $table) = @_;
        my $sth = $dbh->column_info({TABLE_NAME => uc($1)});
    
        my $format = "$table.%-30s %-8s %-20s\n";
        my $description = '';
        while (my $data = $sth->fetchrow_arrayref) {
            my ($name, $type, $length1, $length2, $null) = @{$data}[3,5,6,7,17];
            $null = $null eq 'YES' ? 'NULL' : 'NOT NULL';
            $type =  "$type($length1)" if $length1 == $length2;
            $description .= sprintf $format => $name, $null, $type;
        }
        print $description;
        exit;
    }
    
    sub munge_sql {
        my $dbh = shift;
        my $sql = shift;
        if ($sql =~ /^\s*tabs?\s+(\S+)/) {
            my $table_name = $1;
            $table_name =~ s/\W//g;
            $sql = "SELECT table_name FROM tabs WHERE table_name LIKE upper('%$table_name%')";
        }
        return $sql;
    }

    Then it's a simple matter of writing shell utilities around it. I call the above program qsql, so my desc and tabs (table names) utilities are:

    #/bin/sh
    $HOME/bin/qsql desc $1

    and

    #/bin/sh
    $HOME/bin/qsql tabs $1

    You can also write simple select statements from the command line with my sel utility:

    sel name from users where login_id = \'ovid\'

    Implemented with:

    #/bin/sh
    $HOME/bin/qsql "select $*"

    Again, everything is very specific to Oracle and it's not rocket science, but it's very, very useful to me. Note that the desc utility will only describe tables, not views. That can be confusing if you don't know what you're looking for isn't a real table.

    Reply to This

    Parent

    • Re:URL (Score:1)

      by runrig (3385)

      This doesn't need to be Oracle specific if you use the 'table_info' method. In fact, you're calling 'column_info' in a deprecated style (though in past docs, I can only tell that the style was once valid for 'table_info'). Since the 'table_info' and 'column_info' methods allow wildcards for the table name, you could do all the tricky parts with just those methods.

      it's a sloppy hack...

      Yeah, there is a bit of slop in that you pass the table name to the desc_tables_and_exit function, but use the captured m
      • Re:URL (Score:1)

        by runrig (3385)

        I should also admit that I've never actually used the table_info OR the column_info methods :) But then, I do most all my SQL from vi(m)? [perlmonks.org] anyway.
      • Re:URL (Score:2)

        by Ovid (2709)

        Yeah, there is a bit of slop in that you pass the table name to the desc_tables_and_exit function, but use the captured match from the previous function instead of the passed arg...

        Well, that bit of dreck can easily be explained away by the fact that I was busy doing a refactor I later abandoned and when I posted here I noticed I was using $1 so I tried to fix that on the fly and failed miserably :)
      • Re:URL (Score:2)

        by Ovid (2709)

        Ack. I hit submit instead of preview. Sigh.

        I was trying to thank you for the information, but I didn't get that far :)