| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Perl and SQL+ and svgmgrl
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);
$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);
print $elog_handle "$date_string $time_string : ";
print $elog_handle "Can't open $jlog_fname: $!\n";
close($elog_handle);
$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);
print $elog_handle "$date_string $time_string : "; print $elog_handle "Can't connect to $dbstring: $DBI::errstr\n"; close($elog_handle);
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);
$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);
# 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);
$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);
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: AGUERRA_at_amfam.com 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: ListGuru_at_fatcity.com (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: Gerardo.Molina_at_schwab.com 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: ListGuru_at_fatcity.com (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).Received on Fri Jun 29 2001 - 17:17:17 CDT
 |
 |