Re: how to query oracle database and send output to screen

In comp.lang.perl.misc Galen Boyer <galenboyer_at_yahoo.com> wrote:
>
> ,----
> | Hi,
> | I need to query my oracle database 8.1.6 on solaris 7 and send the
> | results to the screen. I might also need to send the output to a
> | file. I've looked into DBD and DBI but there are some modifications
> | that need to be done to some java oracle binaries to get it to work.
> | I'd rather not modify these files on my production server. I've done
> | this with informix:
> |
> | #!/usr/local/bin/perl
> | $ENV{INFORMIXSERVER} = "n_shm";
> | open DBACCESS, "| /u/informix/bin/dbaccess";
> | print DBACCESS qq{
> | database mydatabase\_at_n_shm;
> | UNLOAD TO '/u/myfile'
> | SELECT *
> | FROM a_table;
> | };
> | close DBACCESS;
> `----
> #!/bin/sh
> #THIS WILL SEND TO SCREEN
> sqlplus login/password_at_instance << EOF
> select ....
> ;
> EOF
>
> #!/bin/sh
> #THIS WILL SEND TO file
> sqlplus login/password_at_instance << EOF > file
> select ....
> ;
> EOF
>
> This is unix specific, not Oracle. Substitute dbaccess and this
> will work as well.
>
> I think Perl has this. I would be surprised if it didn't.

Well, yes of course it does, although you have to go that extra length if you want to do that shell redirect :)

You can use the 'backtick here document' like so :

$output =<<`EIEIO`;
sqlplus login/password_at_instance << EOFOO select blah from foo;
EOFOO
EIEIO where the output will go the variable $output, but I am sure we will all agree this is weird and a little confusing - most people would prefer to use IPC::Open2 to do this (and I couch this in Informix terms as that is all I have to test with right now) :

#!/usr/bin/perl -w

use strict;

use IO::Handle;
use IPC::Open2;

my $dbaccess = '/usr/local/informix/bin/dbaccess tdcusers - ';

my $readhandle = IO::Handle->new;
my $writehandle = IO::Handle->new;

open2($readhandle,$writehandle,$dbaccess);

$| = 1;

print $writehandle <<EOFOO;
select * from codes
EOFOO close $writehandle;

print while (<$readhandle>);

The same thing can be done with sqlplus ...

/J\

-- 
yapc::Europe in assocation with the Institute Of Contemporary Arts
   <http://www.yapc.org/Europe/>   <http://www.ica.org.uk>