One of my goals in my recent experimentation with Oracle and Solaris was to write some programs that could query and modify the data in a database. In my time as a systems administrator, I have written a number of Perl scripts that query Oracle using the DBD::Oracle module. For my lab experimenting, I wanted to be able to write scripts that could run against the late-90s Oracle 8i I installed on Solaris 2.6. Until now, my efforts at getting the DBD::Oracle module compiled on Solaris with the 8i client have failed. In this post I will write about how I finally got this to work.

Installing Solaris 2.6

As in my previous post, I installed Solaris 2.6 on the QEMU SPARC32 emulator. I built an initial base machine with a 10 GB disk using a manual layout like below:

I allocated 5 GB to /opt, as this will be the partition that the Oracle 8i software will be installed on, while allocating 2 GB to /usr/local, which will contain the Sun Freeware utilities I will need to add.

After installing Solaris and making sure that it booted, I shut it down and made a snapshot of the QCOW2 image to do my experimentation with:

qemu-img create -f qcow2 -b solaris26_clean.qcow2 -F qcow2 solaris26_oraperl.qcow2

I then started the machine using this cloned image:

qemu-system-sparc -nic bridge,mac=00:12:34:56:78:aa -m 256M -drive file=solaris26_oraperl.qcow2,if=scsi,bus=0,unit=0,media=disk

Installing Oracle

The steps I used for installing Oracle 8i were similar to what I performed in my previous post, with one exception: I used /opt/oracle for the ORACLE_BASE environment variable instead of /u01/app/oracle. The .profile for the oracle user had the below added to it:

ORACLE_BASE=/opt/oracle
export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/8.1.5
export ORACLE_HOME
PATH=/usr/local/bin:$PATH:$ORACLE_HOME/bin
export PATH
LD_LIBRARY_PATH=/usr/local/lib:/usr/lib:/usr/ucblib:$ORACLE_HOME/lib
export LD_LIBRARY_PATH

When installing Oracle 8i, you will need to select Enterprise Edition for the product to install, even if you don’t plan on running a database on the build server. This is because only the full server installation contains the C header files necessary to build DBD::Oracle. I found this out by accident: just when I was about to give up, I did a find for *.h files on a QEMU machine running Enterprise Edition and found the files in /opt/oracle/product/8.1.5/rdbms/demo. I had assumed that these were only available with another piece of software—one that I would be unable to obtain.

Prior to compiling Perl DBD::Oracle, it is a good idea to make sure that SQL*Plus works with sqlplus user@db.

Add Sun Freeware packages

In order to build the Perl Oracle module, you will need a C compiler and the make utility. These are not built into Solaris 2.6, as a C compiler was an add-on that you would have had to purchase from Sun (and not likely to be available in 2025). In addition, Solaris 2.6 is missing Perl itself. In the past it was possible to obtain these utilities and applications from a site called Sunfreeware, but this has transitioned to a fee-based service unfortunately. Luckily, thanks to Cathode Ray Dude’s work, I was able to obtain the packages I wanted from an archive that he created (there is also a mirror containing the packages located here). If you’re into playing around with this stuff like I am, I recommend saving off these packages before they get taken down.

I selected the following packages to install from this archive and copied them to the subdirectory sunfreeware on an NFS share on a Linux host:

bash-4.1-sol26-sparc-local.gz
gcc-3.4.6-sol26-sparc-local.gz
gzip-1.4-sol26-sparc-local.gz
less-436-sol26-sparc-local.gz
libgcc-3.4.6-sol26-sparc-local.gz
libiconv-1.13.1-sol26-sparc-local.gz
libintl-3.4.0-sol26-sparc-local.gz
make-3.75-sol26-sparc-local.gz
ncurses-5.7-sol26-sparc-local.gz
perl-5.6.1-sol26-sparc-local.gz
unzip-6.0-sol26-sparc-local.gz

While bash, gzip, less, and unzip aren’t needed for this particular exercise, they are nice to have. I unzipped each file and mounted the NFS share on the Solaris 2.6 host (it needs to be an NFSv3 share): mount IP:/share /mnt. cd to /mnt/sunfreeware and change to the C shell with csh. Then execute the below, hitting enter on each line and entering y if prompted:

foreach f (*) 
  pkgadd -d $f all
end

Compiling DBI and DBD::Oracle from source

The DBI module must be built and installed first before proceeding to the DBD::Oracle module, as the latter checks for the presence of the former. For the DBI version, I built version 1.28 from 2002. Other versions might work, but this happened to be the version I had downloaded. It can be downloaded from here. I copied it from my NFS share to /export/home/oracle, chown’d the DBI-1.28 directory to the oracle user, and cd’d into it (you don’t have to build this as the oracle user, but I did so because I already had the .profile set up for this account). Prior to building, ensure that /usr/local/bin is in your $PATH and /usr/local/lib is in your $LD_LIBRARY_PATH. Below are the commands to run to build the module:

cd DBI-1.28
perl Makefile.PL
make
make test
# Install as root, entering the root password when prompted
su -u root -c "cd /export/home/oracle/DBI-1.28 && /usr/local/bin/make install"
# Test. Invocation of the module should produce no output.
perl -e 'use DBI;'

Next I compiled the DBD::Oracle module. This module is very picky about version compatibility. I went through a lot of trial and error to find a version that was compatible with Oracle 8.1.5, and ended up at version 1.14. It can be downloaded here. As with the DBI module, I chose to build it with the oracle user, as this had the needed ORACLE_HOME variable set in .profile. I copied the extracted directory to /export/home/oracle and built it with the below commands. Make sure that perl Makefile.PL and make commands finish without any errors (warnings are OK) before proceeding to the next command. make test can probably be skipped, as it throws a bunch of errors about not being able to the database.

cd DBD-Oracle-1.14
perl Makefile.PL
make
# Test will show as failed from the ORA-07200 errors. 
# I ignored these. You can probably even skip this.
# Or get it to work if you so desire.
make test
# Install as root, entering the root password when prompted
su -u root -c "cd /export/home/oracle/DBD-Oracle-1.14 && /usr/local/bin/make install"
# Test. Invocation of the module should produce no output.
perl -e 'use DBD::Oracle;'

Finally, I downloaded a 2002 version of the Config::Tiny module. It provides a standardized means of parsing INI-style configuration files. It is optional and you can either put the database credentials in each script, or parse your configuration with your own code for portability. To install this module, I created the directory /usr/local/lib/perl5/5.6.1/sun4-solaris/Config and copied Tiny.pm from the archive to here. I then created a simple inventory.cfg file with my ultra secure user credentials:

db_name=ridpath
db_user=inventory
db_pass=abc1234

Now let’s write some simple Perl scripts!

Simple Oracle Perl Scripts

For these examples, I used the simple computer inventory database I created in my previous post on Solaris 2.6 and Oracle 8i.

A Perl script that does what SQL*Plus does

Years ago (possibly in 2014) I became fed up with how SQL*Plus outputs query results, when compared to MySQL or PostgreSQL. There are of course ways to fix the formatting of SQL*Plus, but for reasons lost to time I chose not to pursue this. Furthermore, I wanted to have a script with which I could test Oracle connectivity on systems that didn’t have SQL*Plus installed on them, but which had DBD::Oracle. In the end, I wrote a Perl script that was essentially my own implementation of SQL*Plus. The below code snippet is the script as I wrote it, with the exception of the Perl path changed to /usr/local/bin/perl and the Term::ReadKey lines commented out. Term::ReadKey allows reading of the password in silently. I was able to install and use version 2.21 of Term::ReadKey (found here), but it isn’t necessary to have.

#!/usr/local/bin/perl -w

use strict;
use DBI;
use Getopt::Long;
#use Term::ReadKey;

(my $db, my $username, my $pw);

my $arguments = GetOptions ('db=s'       => \$db,
                            'username=s' => \$username,
                            'pw=s'       => \$pw);
my $sql = $ARGV[0];

unless ($db) {
  print "Enter a DB to connect to: ";
  chomp($db = <STDIN>);
}

unless ($username) {
  print "Enter a username: ";
  chomp($username = <STDIN>);
}

unless ($pw) {
#  ReadMode 2;
  print "Enter the password for ${username}: ";
  chomp($pw = <STDIN>);
#  ReadMode 0;
  print "\n";
}

my $dbh = DBI->connect("dbi:Oracle:${db}", $username, $pw, { AutoCommit => 0 , RaiseError => 1}) || die( $DBI::errstr . "\n" );

sub select_sql() {
  my $sql = shift;

  my $sth = $dbh->prepare($sql) || die "Can't prepare statement: $DBI::errstr";
  $sth->execute();

  my %columns;

  my $col_index = 0;
  foreach my $column (@{$sth->{NAME}}) {
    $columns{$col_index} = $column;
    $col_index++;
  }

  while (my $array_ref = $sth->fetchrow_arrayref()) {
    my $col_index = 0;
    if (scalar(@{$array_ref}) > 1) {
      foreach my $col (@{$array_ref}) {
        if ($col) {
          print $columns{$col_index} . ": $col\n";
        }
        $col_index++;
      }
      print "\n";
    } else {
      print "$array_ref->[0]\n";
    }
  }
  $sth->finish();
}

sub exec_sql() {
   my $sql = shift;

  eval {
    $dbh->do($sql);
    $dbh->commit();
  };

  if ($@) {
    warn "Transaction aborted\n";
    eval { $dbh->rollback(); };
  }
}

sub action() {
  my $sql = shift;

  if ($sql =~ /^select|desc/i) {
    &select_sql($sql);
  } else {
    &exec_sql($sql);
  }
}

if ($sql) {
  &action($sql);
} else {
  while (1) {
    print "Enter your SQL statement, terminating it with ';', and hit enter:\n";
    my $sql = '';
    while (my $input = <STDIN>) {
      chomp($input);
      if ($input =~ /^exit|quit/i) {
        exit;
      }
      $sql .= " $input";
      last if ($input =~ /;$/);
    }
    $sql =~ s/;$//g;
    $sql =~ s/^\s+|\s+$//g;
    &action($sql);
    print "\n";
  }
}

$dbh->disconnect;

I realize that there is nothing interesting or special about this script, but it was fun for me to test out a script I wrote in the 2010s, on a late-90s OS and database with little modification.

A basic inventory report script

Next, I wrote a simple/ugly script that generates a CSV report of all the computers in the inventory database, for auditors, accountants, analysts—anyone whose primary job role is to make technical people stop their actual work and generate some report for them that they will constantly ask questions about (ask me how I know).

#!/usr/local/bin/perl -w

use strict;
use DBI;
use Config::Tiny;

my $Config = Config::Tiny->new();
$Config = Config::Tiny->read('inventory.cfg');

my $dbh = DBI->connect('dbi:Oracle:' . $Config->{_}->{db_name}, $Config->{_}->{db_user}, $Config->{_}->{db_pass}) || die( $DBI::errstr . "\n" );

my $sql = <<'SQL';
SELECT i.inventory_id,i.name,d.domain_name,l.location_name,h.hw_manufacturer||' '||h.hw_model,o.os_vendor||' '||o.os_name||' '||o.os_version,i.static_ip
FROM inventory i, domains d, locations l, hw_models h, operating_systems o
WHERE i.domain_id = d.domain_id
AND i.location_id = l.location_id
AND i.hw_model_id = h.hw_model_id
AND i.os_id = o.os_id
ORDER BY i.inventory_id
SQL

my $sth = $dbh->prepare($sql) || die "Can't prepare statement: $DBI::errstr";
$sth->execute || die "Can't execute statement: $DBI::errstr";

print "Asset Tag,Name,Domain,Location,HW Model,OS,IP\n";

while (my $array_ref = $sth->fetchrow_arrayref) {
  my $row = '';
  foreach my $r (@{$array_ref}) {
    if (defined($r)) {
      $row .= "${r},"
    } else {
      $row .= ',';
    }
  }
  $row = substr($row, 0, -1);
  print "$row\n";
}
$sth->finish();
$dbh->disconnect;

Creating a “menu-ed” inventory system

If you’re going to have an inventory management database, you’ll of course need a front end to manage it. For this exercise I coded a really basic set of scripts that do creation, updates, and deletion. First, I wrote a menu script that in turn calls the scripts do the actual work.

A disclaimer: I’m a systems administrator, not a software developer. The code shared in this blog is for demonstration purposes and not meant for production use. I wrote it to test out a historical database application on a historical OS. It’s probably not the best Perl around and I can’t guarantee it will work for you.

First, the initial menu. It just calls the other scripts that connect to the database and do the work:

#!/usr/local/bin/perl -w

use strict;

my $sub_scripts = {
  1 => {
    'description' => 'Add an inventory item',
    'script'      => 'inventory_add.pl'
  },
  2 => {
    'description' => 'Update an inventory item',
    'script'      => 'inventory_mod.pl'
  },
  3 => {
    'description' => 'Delete an inventory item',
    'script'      => 'inventory_del.pl'
  },
  4 => {
    'description' => 'Add a hardware type',
    'script'      => 'add_hw_type.pl'
  },
  5 => {
    'description' => 'Add a operating system',
    'script'      => 'add_os.pl'
  },
  6 => {
    'description' => 'Exit menu',
    'script'      => 'exit'
  }
};

system('clear');

print "Welcome to the machine inventory management front end!\n";
print "Please choose from one of the following:\n\n";

my $selection;

foreach my $key (sort keys %{$sub_scripts}) {
  print "$key: " . $sub_scripts->{$key}->{'description'} . "\n";
}
print "\n";
print 'Enter selection and hit enter: ';
chomp($selection = <STDIN>);

until (exists $sub_scripts->{$selection}) {
  print 'Selection invalid! Please try again: ';
  chomp($selection = <STDIN>);
}

if ($sub_scripts->{$selection}->{'script'} ne 'exit') {
  system('./' . $sub_scripts->{$selection}->{'script'});
}

Next up, the scripts that do inserts, updates, and deletes. I didn’t bother to write a script for adding a location, as facilities seldom change. Craig the accountant can reach out to Marge the DBA to add a new data center if needed.

#!/usr/local/bin/perl -w

use strict;
use DBI;
use Config::Tiny;

system('clear');

my $Config = Config::Tiny->new();
$Config = Config::Tiny->read('inventory.cfg');

my $dbh = DBI->connect('dbi:Oracle:' . $Config->{_}->{db_name}, $Config->{_}->{db_user}, $Config->{_}->{db_pass}, { AutoCommit => 0 , RaiseError => 1}) || die( $DBI::errstr . "\n" );

print "Enter a name for the machine: ";
chomp(my $name = <STDIN>);
my $name_found = 1;

while ($name_found > 0) {
  $name_found = $dbh->selectrow_array("SELECT COUNT(*) FROM inventory WHERE name
 = '$name'");
  if ($name_found > 0) {
    print "Name already in use! Please enter another: ";
    chomp($name = <STDIN>);
  }
}

my $sth = $dbh->prepare('SELECT * FROM domains') || die "Can't prepare statement: $DBI::errstr";
$sth->execute || die "Can't execute statement: $DBI::errstr";

my $index = 1;
my $domains;
print "Select from the following domains:\n";
while (my $array_ref = $sth->fetchrow_arrayref) {
  $domains->{$index}->{'id'} = $array_ref->[0];
  print "$index: " . $array_ref->[1] . "\n";
  $domains->{$index}->{'domain'} = $array_ref->[1];
  $index++;
}
$sth->finish();

print 'Enter selection and hit enter: ';
chomp(my $selection = <STDIN>);

until (exists $domains->{$selection}) {
  print 'Selection invalid. Please try again: ';
  chomp($selection = <STDIN>);
}

my $domain_id = $domains->{$selection}->{'id'};
my $domain_name = $domains->{$selection}->{'domain'};

$sth = $dbh->prepare('SELECT * FROM locations') || die "Can't prepare statement: $DBI::errstr";
$sth->execute || die "Can't execute statement: $DBI::errstr";

$index = 1;
my $locations;
print "\nSelect from the following locations:\n";
while (my $array_ref = $sth->fetchrow_arrayref) {
  $locations->{$index}->{'id'} = $array_ref->[0];
  print "$index: " . $array_ref->[1] . "\n";
  $locations->{$index}->{'name'} = $array_ref->[1];
  $index++;
}
$sth->finish();

print 'Enter selection and hit enter: ';
chomp($selection = <STDIN>);

until (exists $locations->{$selection}) {
  print 'Selection invalid. Please try again: ';
  chomp($selection = <STDIN>);
}

my $location_id = $locations->{$selection}->{'id'};
my $location = $locations->{$selection}->{'name'};

$sth = $dbh->prepare('SELECT * FROM hw_models') || die "Can't prepare statement: $DBI::errstr";
$sth->execute || die "Can't execute statement: $DBI::errstr";

$index = 1;
my $hw_models;
print "\nSelect from the following hardware models:\n";
while (my $array_ref = $sth->fetchrow_arrayref) {
  $hw_models->{$index}->{'id'} = $array_ref->[0];
  $hw_models->{$index}->{'model'} = $array_ref->[1] . ' ' . $array_ref->[2];
  print "$index: " . $hw_models->{$index}->{'model'} . "\n";
  $index++;
}
$sth->finish();

print 'Enter selection and hit enter: ';
chomp($selection = <STDIN>);

until (exists $hw_models->{$selection}) {
  print 'Selection invalid. Please try again: ';
  chomp($selection = <STDIN>);
}

my $hw_model_id = $hw_models->{$selection}->{'id'};
my $hw_model = $hw_models->{$selection}->{'model'};

$sth = $dbh->prepare('SELECT * FROM operating_systems') || die "Can't prepare statement: $DBI::errstr";
$sth->execute || die "Can't execute statement: $DBI::errstr";

$index = 1;
my $operating_systems;
print "\nSelect from the following operating systems:\n";
while (my $array_ref = $sth->fetchrow_arrayref) {
  $operating_systems->{$index}->{'id'} = $array_ref->[0];
  $operating_systems->{$index}->{'os'} = $array_ref->[1] . ' ' . $array_ref->[2] . ' ' . $array_ref->[3];
  print "$index: " . $operating_systems->{$index}->{'os'} . "\n";
  $index++;
}
$sth->finish();

print 'Enter selection and hit enter: ';
chomp($selection = <STDIN>);

until (exists $operating_systems->{$selection}) {
  print 'Selection invalid. Please try again: ';
  chomp($selection = <STDIN>);
}

my $os_id = $operating_systems->{$selection}->{'id'};
my $os = $operating_systems->{$selection}->{'os'};

print "Optionally, enter a static IP: ";
chomp($selection = <STDIN>);
my $static_ip = '';
if ($selection ne '') {
  until ($selection =~ /^([0-9]{1,3}\.){3}[0-9]{1,3}$/) {
    print 'Invalid IP address. Please try again: ';
    chomp($selection = <STDIN>);
  }
  $static_ip = $selection;
}

print "\nYou have made the following selections:\n";
print "Hostname: ${name}.${domain_name}\n";
print "Model: $hw_model\n";
print "OS: $os\n";
print "Location: $location\n";
print "\nProceed with adding? (y or n): ";
chomp($selection = <STDIN>);

if ($selection eq 'y') {
  eval {
    $dbh->do("INSERT INTO inventory (name,domain_id,location_id,hw_model_id,os_id,static_ip) VALUES ('$name',$domain_id,$location_id,$hw_model_id,$os_id,'$static_ip')");
    $dbh->commit();
  };

  if ($@) {
    warn "Transaction aborted\n";
    eval { $dbh->rollback(); };
  }

  my $asset_tag = $dbh->selectrow_array("SELECT inventory_id FROM inventory WHERE name = '$name'");
  print "The asset tag number for this machine is $asset_tag\n";  
}
$dbh->disconnect;

#!/usr/local/bin/perl -w

use strict;
use DBI;
use Config::Tiny;

system('clear');

my $Config = Config::Tiny->new();
$Config = Config::Tiny->read('inventory.cfg');

my $dbh = DBI->connect('dbi:Oracle:' . $Config->{_}->{db_name}, $Config->{_}->{db_user}, $Config->{_}->{db_pass}, { AutoCommit => 0 , RaiseError => 1}) || die( $DBI::errstr . "\n" );

print "Enter a name or asset tag number for the machine: ";
chomp(my $entry = <STDIN>);
my $query = 'SELECT inventory_id,name,domain_id,location_id,os_id,static_ip FROM inventory WHERE ';
if ($entry =~ /^[2-9][0-9]{5}$/) {
  $query .= "inventory_id = $entry";
} else {
  $query .= "name = '$entry'";
}

my $record = $dbh->selectrow_arrayref($query);

unless ($record) {
  print "Inventory record not found. Exiting.\n";
  $dbh->disconnect;
  exit;
}

my $inventory_id = $record->[0];
my $name = $record->[1];
my $domain_id = $record->[2];
my $location_id = $record->[3];
my $os_id = $record->[4];
my $static_ip = $record->[5] || '';
my $update_list = '';

print "\nUpdating asset ${name}\n";

my $sth = $dbh->prepare('SELECT * FROM domains') || die "Can't prepare statement: $DBI::errstr";
$sth->execute || die "Can't execute statement: $DBI::errstr";

my $index = 1;
my %domains;
print "\nSelect from the following domains:\n";
while (my $array_ref = $sth->fetchrow_arrayref) {
  $domains{$index} = $array_ref->[0];
  print "$index: " . $array_ref->[1];
  if ($array_ref->[0] == $domain_id) {
    print ' <- Current selection';
  }
  print "\n";
  $index++;
}
$sth->finish();

print 'Enter selection and hit enter (blank if leaving unchanged): ';
chomp(my $selection = <STDIN>);

if ($selection =~ /^\d+$/) {
  until (exists $domains{$selection}) {
    print 'Selection invalid. Please try again: ';
    chomp($selection = <STDIN>);
  }
  $update_list .= "domain_id = $domains{$selection}, ";
}

$sth = $dbh->prepare('SELECT * FROM locations') || die "Can't prepare statement: $DBI::errstr";
$sth->execute || die "Can't execute statement: $DBI::errstr";

$index = 1;
my %locations;
print "\nSelect from the following locations:\n";
while (my $array_ref = $sth->fetchrow_arrayref) {
  $locations{$index} = $array_ref->[0];
  print "$index: " . $array_ref->[1];
  if ($array_ref->[0] == $location_id) {
    print ' <- Current selection';
  }
  print "\n";
  $index++;
}
$sth->finish();

print 'Enter selection and hit enter (blank if leaving unchanged): ';
chomp($selection = <STDIN>);

if ($selection =~ /^\d+$/) {
  until (exists $locations{$selection}) {
    print 'Selection invalid. Please try again: ';
    chomp($selection = <STDIN>);
  }
  $update_list .= "location_id = $locations{$selection}, ";
}

$sth = $dbh->prepare('SELECT * FROM operating_systems') || die "Can't prepare statement: $DBI::errstr";
$sth->execute || die "Can't execute statement: $DBI::errstr";

$index = 1;
my %operating_systems;
print "\nSelect from the following operating systems:\n";
while (my $array_ref = $sth->fetchrow_arrayref) {
  $operating_systems{$index} = $array_ref->[0];
  print "$index: " . $array_ref->[1] . ' ' . $array_ref->[2] . ' ' . $array_ref->[3];
  if ($array_ref->[0] == $os_id) {
    print ' <- Current selection';
  }
  print "\n";
  $index++;
}
$sth->finish();

print 'Enter selection and hit enter (blank if leaving unchanged): ';
chomp($selection = <STDIN>);

if ($selection =~ /^\d+$/) {
  until (exists $operating_systems{$selection}) {
    print 'Selection invalid. Please try again: ';
    chomp($selection = <STDIN>);
  }
  $update_list .= "os_id = $operating_systems{$selection}, ";
}

print "\nUpdate or set static IP (current value $static_ip): ";
chomp($selection = <STDIN>);
if ($selection ne '') {
  until ($selection =~ /^([0-9]{1,3}\.){3}[0-9]{1,3}$/) {
    print 'Invalid IP address. Please try again: ';
    chomp($selection = <STDIN>);
  }
  $update_list .= "static_ip = '$selection', ";
}

$update_list = substr($update_list, 0, -2);
if ($update_list ne '') {
  print "\nProceed with changes? (y or n): ";
  chomp($selection = <STDIN>);
} else {
  print "\nNo changes were made. Exiting.\n";
  $dbh->disconnect;
  exit
}

if ($selection eq 'y') {
  my $sql = "UPDATE inventory SET $update_list WHERE inventory_id = $inventory_id";
  eval {
    $dbh->do($sql);
    $dbh->commit();
  };

  if ($@) {
    warn "Transaction aborted\n";
    eval { $dbh->rollback(); };
  }
}

$dbh->disconnect;

#!/usr/local/bin/perl -w

use strict;
use DBI;
use Config::Tiny;

system('clear');

my $Config = Config::Tiny->new();
$Config = Config::Tiny->read('inventory.cfg');

my $dbh = DBI->connect('dbi:Oracle:' . $Config->{_}->{db_name}, $Config->{_}->{db_user}, $Config->{_}->{db_pass}, { AutoCommit => 0 , RaiseError => 1}) || die( $DBI::errstr . "\n" );

print "Enter a name or asset tag number for the machine: ";
chomp(my $entry = <STDIN>);
my $query = 'SELECT inventory_id,name FROM inventory WHERE ';
if ($entry =~ /^[2-9][0-9]{5}$/) {
  $query .= "inventory_id = $entry";
} else {
  $query .= "name = '$entry'";
}

my $record = $dbh->selectrow_arrayref($query);

unless ($record) {
  print "Inventory record not found. Exiting.\n";
  $dbh->disconnect;
  exit;
}

my $inventory_id = $record->[0];
my $name = $record->[1];

print "\nProceed with deleting record $name/$inventory_id? (y or n): ";
chomp(my $selection = <STDIN>);

if ($selection eq 'y') {
  my $sql = "DELETE FROM inventory WHERE inventory_id = $inventory_id";
  eval {
    $dbh->do($sql);
    $dbh->commit();
  };

  if ($@) {
    warn "Transaction aborted\n";
    eval { $dbh->rollback(); };
  }
}

$dbh->disconnect;

#!/usr/local/bin/perl -w

use strict;
use DBI;
use Config::Tiny;

system('clear');

my $Config = Config::Tiny->new();
$Config = Config::Tiny->read('inventory.cfg');

my $dbh = DBI->connect('dbi:Oracle:' . $Config->{_}->{db_name}, $Config->{_}->{db_user}, $Config->{_}->{db_pass}, { AutoCommit => 0 , RaiseError => 1}) || die( $DBI::errstr . "\n" );

print "Enter a manufacturer for the machine type: ";
chomp(my $manufacturer = <STDIN>);

print "Enter a model: ";
chomp(my $model = <STDIN>);

print "\nProceed with adding? (y or n): ";
chomp(my $selection = <STDIN>);

if ($selection eq 'y') {
  eval {
    $dbh->do("INSERT INTO hw_models (hw_manufacturer,hw_model) VALUES ('$manufacturer','$model')");
    $dbh->commit();
  };

  if ($@) {
    warn "Transaction aborted\n";
    eval { $dbh->rollback(); };
  }
}
$dbh->disconnect;

#!/usr/local/bin/perl -w

use strict;
use DBI;
use Config::Tiny;

system('clear');

my $Config = Config::Tiny->new();
$Config = Config::Tiny->read('inventory.cfg');

my $dbh = DBI->connect('dbi:Oracle:' . $Config->{_}->{db_name}, $Config->{_}->{db_user}, $Config->{_}->{db_pass}, { AutoCommit => 0 , RaiseError => 1}) || die( $DBI::errstr . "\n" );

print "Enter a vendor for the operating system: ";
chomp(my $os_vendor = <STDIN>);

print "Enter an OS name: ";
chomp(my $os_name = <STDIN>);

print "Enter an OS version: ";
chomp(my $os_ver = <STDIN>);

print "\nProceed with adding? (y or n): ";
chomp(my $selection = <STDIN>);

if ($selection eq 'y') {
  eval {
    $dbh->do("INSERT INTO operating_systems (os_vendor,os_name,os_version) VALUES ('$os_vendor','$os_name','$os_ver')");
    $dbh->commit();
  };

  if ($@) {
    warn "Transaction aborted\n";
    eval { $dbh->rollback(); };
  }
}
$dbh->disconnect;

Again, if I were writing this for production use, I might do a better job with the design of the programs. I would consolidate the code more and break more things out into subroutines, possibly into a module. However, it works well enough for this demonstration.

For deleting an asset from the inventory, I decided to set up a recycling bin table in Oracle, along with an on-delete trigger. Whenever an object gets deleted from the inventory table, it gets inserted automatically into the recycling_bin table. The below SQL file creates the table and trigger:

CREATE TABLE recycling_bin (
    inventory_id NUMBER,
    name VARCHAR2(30) NOT NULL,
    domain_id NUMBER NOT NULL,
    location_id NUMBER NOT NULL,
    hw_model_id NUMBER NOT NULL,
    os_id NUMBER NOT NULL,
    static_ip VARCHAR2(15),
    delete_ts DATE,
    PRIMARY KEY (inventory_id),
    UNIQUE (name)
);

CREATE OR REPLACE TRIGGER inventory_on_delete
  BEFORE DELETE ON inventory
  FOR EACH ROW 
BEGIN
  INSERT INTO recycling_bin
  (inventory_id,
    name,
    domain_id,
    location_id,
    hw_model_id,
    os_id,
    static_ip,
    delete_ts
   )
  values(:old.inventory_id,
            :old.name,
            :old.domain_id,
            :old.location_id,
            :old.hw_model_id,
            :old.os_id,
            :old.static_ip,
            SYSDATE);
END;
/

Conclusion

This post was a lot of fun for me to create. I will admit, however, that it wasn’t an entirely authentic late 90s Solaris experience, as it used more recent versions of Perl, etc. Still, it was neat imagining someone writing a script like this in Emacs on an Ultra 5 in 1999. I really want to do more with the combination of Solaris 2.6, Oracle 8i, and Perl. One thing that might be interesting to do is write a script that synchronizes data from Oracle to MySQL. If I end up doing this, I will create another post for it.

As always, thank you for reading!

Matt Ridpath's Blog

A blog about my interests

Compiling Perl DBD::Oracle on Solaris 2.6 and writing some Perl scripts