I am calling a shell script from a perl script and the shell script
invokes an sql script. I would like to invoke the sql script direct
from perl.
How can I do it?
Perl script :
-----------------------
system("call_shell_script 'input_file' ");
-----------------------
Contents of shell script 'call_shell_script' :
------------
#!/bin/ksh
LOGSQL=rep.log
sqlplus -s << EOH 2>gen_rep.errlog
userid/passwd
spool ${LOGSQL}
@call_sql_script.sql
spool off
EOH
------------
Can I simply combine the two steps above with these lines in perl
script :
(I don't have access to perl environment today, so can't try this
out.)
------------
system("sqlplus -s << EOH 2>gen_rep.errlog");
userid/passwd
spool ${LOGSQL}
@call_sql_script.sql
spool off
EOH
I have run into the problem you describe several times, and think I
may have a solution for you (at least within the UNIX environment, and
connecting to the an Oracle database).
The problem is that the SQL*PLUS environment needs to be set before
you can call SQL*PLUS from within a perl program (doing a system
callout doesn't work alone, because SQL*PLUS doesn't inherently know
it's own environment, or at least that's what my DBA tells me).
As an aside, I use a class that stores this info. in one of my
directories, and by using that class, I only need one line to actually
call SQL*PLUS within my perl scripts (my actual SQL*PLUS statement),
using OO notation (if you want a copy of the class, I'll mail it to
you; it's very simple and also something you could easily write on
your own, with just a basic understanding of OO programming in
Perl-I'm certainly no expert). Errors and valid data sets are
returned in an array as they would otherwise appear within SQL*PLUS.
If you can't install DBI (where I work, DBI is installed on some of
our UNIX machines, but not others, and I'm not allowed to install it
where it doesn't exist. What a pain), I suggest doing something like
rolling your own class and using the internals of the class to handle
your connections. This will make easier the process of handling
Oracle version changes (you'll only have to change one line in one
class, as opposed to changing myriad lines in many scripts).
Anyway, the below code should get you started (all you need to do is
just change the connect, settings, query and maybe the environment).
Good luck.
#!/perl -w
use strict;
use diagnostics;
my $rec;
$rec->{ENVIRONMENT} = '9.2.0';
@{$rec->{PATH}} = `set path=\$PATH`;
$rec->{CONNECT} = 'jroland/jroland@devlp';
@{$rec->{SETTINGS}} = 'set wrap off pagesize 0';
@{$rec->{QUERY}} = 'select table_name from user_tables;';
my $script = "SQL_OUTFILE" . time . ".SQL";
my $sfile = "$script" . "_OUTFILE";
open OUTFILE, ">$script" or die "Cannot open $script: $!";
print OUTFILE join "\n", @{$rec->{SETTINGS}}, "spool $sfile",
@{$rec->{QUERY}}, "spool off", "exit;";
close OUTFILE or die "Cannot close $script: $!";
`@{$rec->{PATH}} sqlplus $rec->{CONNECT} \@$script`;
open CLASS_SPOOLFILE, "$sfile" or die "Cannot open $sfile: $!";
while(<CLASS_SPOOLFILE>) {
chomp;
push @{$rec->{QUERY_RETURNS}}, $_;
}
close CLASS_SPOOLFILE or die "Cannot close $sfile: $!";
`rm $script $sfile`;
print "$_\n" for (@{$rec->{QUERY_RETURNS}});
