DBI was designed to do exactly what you are asking.
You do need DBI module plus DBD::Oracle module.
The syntax may be slightly different depending on the command you want to
execute.
For standard SQL (select, update, delete, insert) example, see code listing
below.
If you want to execute other types of commands, I suggest taking a look at
the DBI documentation.
$ perldoc DBI
Some experimentation is probably required to see if you can get your
particular command to work, assuming DBI interface can handle it.
HTH
Gerardo
#!/opt/perl/bin/perl
use strict;
use IO::File;
use DBI;
sub short_filename
{
my $full_path_name = shift;
my @pieces = split('/',$full_path_name);
my $short = pop(@pieces);
return $short;
}
my($user_id);
my($password);
my(@months);
my($sec,$min,$hour);
my($mday,$mon,$year);
my($wday,$yday,$isdst);
my($num_month);
my($date_string,$time_string);
my($jlog_fname,$elog_fname);
my($jlog_handle,$elog_handle);
my($dbh);
my($sth);
my($rc);
my($dbstring);
my(@data);
my($numeric_date);
my($num_sessions);
my($pgm_name);
my($service_name);
$pgm_name = short_filename($0);
@months = ('JAN','FEB','MAR','APR','MAY','JUN',
'JUL','AUG','SEP','OCT','NOV','DEC');
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
localtime(time);
$year += 1900;
$num_month = $mon + 1;
$date_string = sprintf("%2.2d-%s-%4.4d",
$mday,$months[$mon],$year);
$time_string = sprintf("%2.2d:%2.2d:%2.2d",$hour,$min,$sec);
$numeric_date = sprintf("%4.4d%2.2d%2.2d",
$year,
$num_month,
$mday);
# setup job log and error log files
$jlog_fname = $ENV{'HOME'} . '/perl_progs/dbi/demo/' . $pgm_name . '.' .
$numeric_date .
'.joblog';
$elog_fname = $ENV{'HOME'} . '/perl_progs/dbi/demo/' . $pgm_name . '.' .
$numeric_date .
'.errlog';
if ( ! -f $elog_fname )
{
$elog_handle = new IO::File "$elog_fname", "w";
if ( ! $elog_handle )
{
print "Can't open $elog_fname: $!\n";
die;
}
}
else # file exists already
{
# open it to append
$elog_handle = new IO::File "$elog_fname", "a";
if ( ! $elog_handle )
{
print "Can't open $elog_fname: $!\n";
die;
}
}
if ( ! -f $jlog_fname )
{
$jlog_handle = new IO::File "$jlog_fname", "w";
if ( ! $jlog_handle )
{
print $elog_handle "$date_string $time_string : ";
print $elog_handle "Can't open $jlog_fname: $!\n";
close($elog_handle);
die;
}
}
else # file exists already
{
# open it to append
$jlog_handle = new IO::File "$jlog_fname", "a";
if ( ! $jlog_handle )
{
print $elog_handle "$date_string $time_string : ";
print $elog_handle "Can't open $jlog_fname: $!\n";
close($elog_handle);
die;
}
}
$service_name = 'TESTDB';
$user_id = 'scott';
$password = 'tiger';
$dbstring = "dbi:Oracle:$service_name";
# query database for count of sessions
$dbh = DBI->connect( $dbstring,
$user_id,
$password);
if ( ! $dbh )
{
print $elog_handle "$date_string $time_string : ";
print $elog_handle "Can't connect to $dbstring: $DBI::errstr\n";
close($elog_handle);
die;
}
print "\n\tconnected...";
print $jlog_handle "\n\tconnected...";
$sth = $dbh->prepare( 'select count(*) ' .
'from v$session ' );
if ( ! $sth )
{
print $elog_handle "$date_string $time_string : ";
print $elog_handle "Can't prepare statement: $DBI::errstr\n";
close($elog_handle);
die;
}
$rc = $sth->execute;
if ( $rc ne '0E0' )
{
print $elog_handle "$date_string $time_string : ";
print $elog_handle "Can't execute statement: $DBI::errstr\n";
close($elog_handle);
die;
}
# process data from query
while (@data = $sth->fetchrow_array )
{
$num_sessions = $data[0];
}
print "$date_string $time_string : ";
print "num_sessions [$num_sessions]\n";
print $jlog_handle "$date_string $time_string : ";
print $jlog_handle "num_sessions [$num_sessions]\n";
$rc = $sth->finish;
if ( ! $rc )
{
print $elog_handle "$date_string $time_string : ";
print $elog_handle "Can't finish statement: $DBI::errstr\n";
close($elog_handle);
die;
}
$rc = $dbh->disconnect;
if ( ! $rc )
{
print $elog_handle "$date_string $time_string : ";
print $elog_handle "Can't disconnect from $dbstring: $DBI::errstr\n";
close($elog_handle);
die;
}
print "\n\tdisconnected.\n\n";
print $jlog_handle "\n\tdisconnected.\n\n";
exit;
-----Original Message-----
Sent: Friday, June 29, 2001 11:26 AM
To: Multiple recipients of list ORACLE-L
Greetings members,
Can I use commands like the one below in perl or I needs to use the DBI
for any database use? I would like to migrate some shell scripts into
perl, but I do not see if it would be convenient or feasible...
sqlplus / -s <EOF
comands in here
EOF
Samples would be great!
Thanks.
Abraham J. Guerra
Oracle DBA
American Family Insurance
(608) 242-4100 x32026
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Guerra, Abraham J
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Molina, Gerardo
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
