Example 1: Run a Simple SQL Command

This script runs a simple SQL command. If an Oracle error is thrown, it will be reported to the user.

#!/bin/perl
use strict;
use DBI;my $dbh = DBI->connect( 'dbi:Oracle:[enter_sidname_here]',
'[enter_oracle_username_here]',
'[enter_password]',
{
RaiseError => 1,
AutoCommit => 0
}
) || die "Database connection not made: $DBI::errstr";# Text of SQL Statement
my $sql = qq{ DROP TABLE EMPLOYEES };

# Run SQL Statement
$dbh->do( $sql );

# Disconnect
$dbh->disconnect() or warn "DB disconnection failed: $DBI::errstrn";

Example 2: Run a Query and Print Results

This script runs a query and scans through the results.

To add more fields to the query and output make sure to update the bind_columns function with the new fields.

#!/bin/perl
use strict;
use DBI;# Run a SELECT
my $dbh = DBI->connect( 'dbi:Oracle:[enter_sidname_here]',
'[enter_oracle_username_here]',
'[enter_password]',
{
RaiseError => 1,
AutoCommit => 0
}
) || die "Database connection not made: $DBI::errstr";

my $sql = qq{ SELECT TABLE_NAME FROM USER_TABLES };

# Prepare and execute SELECT
my $sth = $dbh->prepare($sql);
$sth->execute();

# Declare and Bind Column(s)
my($table_name);
$sth->bind_columns(undef, $table_name);

# Fetch rows from DB
print "List of tables:nn";
while( $sth->fetch() ) {
print "Object: $table_name";
}

# Close cursor
$sth->finish();
$dbh->disconnect() or warn "DB disconnection failed: $DBI::errstrn";

A collection of handy Perl scripts for use with Oracle.

# David Mann
# http://ba6.us

use strict;
use DBI;

# WatchInstanceLongops.pl
# This program runs continuously to show current Long Operations on
# an Oracle Instance.

die "\nWatchInstanceLongops.pl\n\n" .
"This procedure watches long operations on an instance.\n\n" .
"syntax: perl $0 [SCHEMA_NAME]/[SCHEMA_PASSWORD]@[TNSNAME]\n"
if @ARGV < 0;

# Get command line arguments
my ( $connect_string ) = @ARGV;

# Connect to DB
print "Connecting to DB...\n";
my $dbh = getDBConnection( $connect_string );

### SQL Query that drives this procedure ###

my $sql = qq{ select ROUND(sofar/(totalwork+1)*100,2) as pct,
elapsed_seconds,
time_remaining,
v\$session_longops.message
from v\$session_longops
where sofar<>totalwork
order by target, sid
};

### Main Program : Begin ###

my($percent, $secselap, $secsleft, $message);
my ($count);

my $sth = $dbh->prepare($sql);
while (1) {
writeHeader($connect_string);

# Prepare and execute SELECT
$sth->execute();

# Declare and Bind Columns
$sth->bind_columns(undef, \$percent, \$secselap, \$secsleft, \$message);

# Fetch rows from DB
while( $sth->fetch() ) {
writeOutputBlock($percent, $message, $secselap, $secsleft);
}

sleep(3);

}

print "\ndone\n";
$sth->finish(); # Close cursor

$dbh->disconnect() or warn "DB disconnection failed: $DBI::errstr\n";

close(OUT);

### Main Program : End ###

# Function: getDBConnection
# Parameters: $connString - connection string in format user/password@tnsname

sub getDBConnection {

my ($connectstring) = @_;

my $slashpos = index($connectstring,'/');
my $atpos = index($connectstring,'@');
die "missing /" if ($slashpos == -1);
die "missing @" if ($atpos == -1);

my $username = substr($connectstring, 0, $slashpos);
my $password = substr($connectstring, $slashpos+1, $atpos - $slashpos - 1);
my $tnsname = substr($connectstring, $atpos + 1);

return DBI->connect( "dbi:Oracle:".$tnsname,
$username,
$password,
{
RaiseError => 1,
AutoCommit => 0
}
) || die "Database connection not made: $DBI::errstr";

};

# Function: writeHeader
# Parameters: $connString - connection string in format user/password@tnsname

sub writeHeader {
my ($connectstring) = @_;

my $atpos = index($connectstring,'@');
die "missing @" if ($atpos == -1);

system "cls";
print "Monitoring Instance: " . substr($connectstring, $atpos + 1) . "\n\n";

};

# Function: writeOutputBlock
# Parameters: $percent - percent done (0 - 100)
# $message - Text message from v$session_longops
# $secselap - Seconds elapsed from v$session_longops
# $secsleft - Estimated seconds left from v$session_longops

sub writeOutputBlock {

my ($percent, $message, $secselap, $secsleft) = @_;

print substr($message,0,79) . "\n";
print "Percent : $percent ";
print "Minutes elapsed : " . int($secselap/60) . ":" . sprintf("%02s", $secselap % 60) . " ";
print "Est. Minutes left : " . int($secsleft/60) . ":" . sprintf("%02s", $secsleft % 60) . "\n";
&writeProgressBar($percent,80);
print "\n";

};

# Function: writeProgressBar
# Parameters: $perc - percent done (0 - 100)
# $width - Total width of the Progress Bar

sub writeProgressBar {

my ($perc, $width) = @_;

my $spaces = int($perc / 100 * ($width-2));

print "|";
for ($count = 1; $count <= ($width-2); $count++) {
if ($count < $spaces) {
print "X";
} else {
print "=";
}
};
print "|";

};

use strict;
use FileHandle;
use DBI;

# David Mann
# http://ba6.us

# read the compulsory arguments
die "\nOracle DB Connection Monitor\n\nsyntax: perl $0 user password instance\n" if @ARGV < 3;
my ( $user, $pass, $instance) = @ARGV;
my $filename=$ARGV[3];
my $output_to_file = 0;
my $OUT;

my $header = "TIMESTAMP,USER,MACHINE,SUM_PGAMEM,AVG_PGAMEM,COUNT\n";

# If output to file requested
if (@ARGV==4) {
print "Output to file " . $filename;
$output_to_file = 1;
open(OUT,">>$filename") or die "Cannot open $filename for writing.";
# $OUT->autoflush(0);
print OUT $header;
};

# Run a SELECT
my $dbh = DBI->connect( 'dbi:Oracle:' . $instance,
$user,
$pass,
{
RaiseError => 1,
AutoCommit => 0
}
) || die "Database connection not made: $DBI::errstr";

my $sql = qq{
SELECT TO_CHAR(sysdate, 'MM/DD/YYYY HH24:MI:SS') || ',' ||
username || ',' ||
UPPER(machine) || ',' ||
TRIM(TO_CHAR(SUM(value), '999999999999')) || ',' ||
TRIM(TO_CHAR(AVG(value), '999999999999')) || ',' ||
COUNT(*) as OutputText,
COUNT(*) as Count
FROM v\$statname n,
v\$sesstat s,
v\$session ss
WHERE n.STATISTIC# = s.STATISTIC#
AND s.sid = ss.sid
AND n.name like 'session pga memory'
GROUP BY USERNAME, MACHINE
ORDER BY USERNAME, MACHINE
};

# Prepare and execute SELECT
my $sth = $dbh->prepare($sql);

do {

$sth->execute();

my($OutputText,$Count); # Declare columns
$sth->bind_columns(undef, \$OutputText, \$Count);

print "\n" . $header;
while( $sth->fetch() ) {
print "$OutputText\n";

# Beep if count > 20
if ($Count > 20) {
print chr(07);
};

# Output to file if required
if ($output_to_file = 1) {
print OUT "$OutputText\n";
};

}

sleep(20);

} while 1;

$sth->finish(); # Close cursor

$dbh->disconnect() or warn "DB disconnection failed: $DBI::errstr\n";

close(OUT);

use strict;
use DBI;

# SimpleQuery.pl
# David Mann
# http://ba6.us

# Runs and outputs a simple query from an Oracle DB 

die "\n$0\n\n" . 
    "This procedure shows how to do a simple query and output the results to a file.\n\n" . 
    "syntax: perl $0 [SCHEMA_NAME] [SCHEMA_PASSWORD] [TNSNAME] [OUTPUT_FILENAME]\n"
    if @ARGV < 3;


# Get command line arguments
my ( $schema_name, $schema_password, $tnsname, $output_filename) = @ARGV;

# Connect to DB
print "Connecting to DB...\n";
my $dbh = DBI->connect( "dbi:Oracle:".$tnsname,
                        $schema_name,
                        $schema_password,
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      ) || die "Database connection not made: $DBI::errstr";

# Open output file
open(OUT,">$output_filename") || die "cannot create $output_filename $!";

print OUT "-- File generated for $schema_name \@ $tnsname on " . localtime() . "\n\n";

print OUT "SET SERVEROUTPUT ON;\n";


### SQL Query that drives this procedure ###

my $sql = qq{ 
select table_name, num_rows
        FROM user_tables
};


### Main ###

# Prepare and execute SELECT
print "Executing query...\n";
my $sth = $dbh->prepare($sql);
$sth->execute();

# Declare and Bind Columns
my($table_name,$num_rows);
$sth->bind_columns(undef, \$table_name, \$num_rows);

# Fetch rows from DB and process them
print "Processing.";
my $out_line;
while( $sth->fetch() ) {

    # Output status to screen
    print ".";

    # Output data to file
    print OUT $table_name . " " . $num_rows . "\n"; 
	    
}

# Output status to screen
print "\ndone\n";

$sth->finish();                           # Close cursor

$dbh->disconnect() or warn "DB disconnection failed: $DBI::errstr\n";

close(OUT);