Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Friday July 09, 2004
04:36 PM

Bash database shell

[ #19779 ]

Sometimes it gets very frustrating searching through the metadata of Oracle to find what what I want, particularly since I don't have the shell handy. Getting tired of this, I wrote a simple Perl program, qsql which allows me to execute a lot of queries without using Oracle's pitiful SQLPlus. After fine-tuning it, I realized that most of the things I need to use it for are amenable to further tweaking. I can now do things like:

$ desc ti_releases | grep COUNTRY_ID
ti_releases.COUNTRY_ID                NOT NULL  VARCHAR2(5)

Want a quick description of every table whose name contains the word "product"?

$ for table in `tabs product`; do desc $table; done;

And then I can grep for the fields I am really looking for. It's the full power of bash with most of my most commonly needed database search tools.


List all Journal entries
The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.

Bash database shell 8 Comments More | Login | Reply /

 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • So where do we download this? ;)
    --
    life is too short

    • 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

      • 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

        • 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.
        • 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 :)

        • Ack. I hit submit instead of preview. Sigh.

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

  • What existing shells you ask? Well several co-workers use a hacked up version of piqt [sourceforge.net]. I suspect that dbishell [sourceforge.net] is going to provide more functionality out of the box. The latter page points at several more to look at.

    Perhaps another way to state this question is, "What features are you really looking for in a project like this?"

    • I didn't know about those others but it looks like they might provide what I was looking for. Basically, I wanted to interact with the database without leaving Bash. By building a few small utilities that interacted with one another, I have the database directly in the shell. If these other projects provide that as seamlessly, I'll probably go with one of them. Thanks for the heads up.

Stories, comments, journals, and other submissions on use Perl; are Copyright 1998-2006, their respective owners.