Programming for Oracle on Linux, Part I

Interaction with a legacy Oracle database can be done simply in only a few hundred lines of code, using Perl, DBI and DBD::Oracle.

By far, the most common RDBMS platforms for Linux-based systems are MySQL and PostgreSQL, due to their inclusion in most major Linux distributions. Quite a few shops still run legacy databases, however, using commercial database servers such as Sybase or Oracle. Even Linux Journal gave its 2001 Editors' Choice Award for Best Database to Oracle. As such, it is not uncommon for a programmer to be asked to put together an application to interact with these legacy databases.

Perl, the duct tape of the Internet, is well suited for use in CGI applications. Thanks to the Perl DBI module, available on CPAN, database interaction with Perl is quite simple.

Prerequisites

To start, you need to install the Oracle client software with at least the development libraries. Installation of the Oracle client software is outside the scope of this article, but a plethora of information is available on-line about installing Oracle on Linux. Don't worry about the database server-specific instructions and kernel tuning; all we need is the development environment.

Once the Oracle client has been installed, it is time to install the Perl DBI and DBD::Oracle modules. Also, if you do not have it installed already, you need to install the Getopt::Long Perl module. The easiest way to install these modules is through CPAN. As root, run the following command:


	[root@localhost root]# export ORACLE_HOME=/path/to/oracle
	[root@localhost root]# perl -MCPAN -e shell
	
	cpan shell -- CPAN exploration and modules installation (v1.61)
	ReadLine support enabled
	
	cpan> install DBI
	
	...
	
	cpan> install DBD::Oracle

	...
	
	cpan> install Getopt::Long

With our Perl modules and the Oracle client installed, we now can start writing our code.

Code Examination

For this program, we want to be able to query some data from a database containing some customer information. For purposes of the example, our customer_data table contains the username, first and last name, address, city, state, zip code, phone number, age and account status code. An example of the table definition is shown in Listing 1.

Listing 1. Sample Table Definition


 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(16)
 FIRST_NAME                                NOT NULL VARCHAR2(255)
 LAST_NAME                                 NOT NULL VARCHAR2(255)
 ADDRESS_LINE1                             NOT NULL VARCHAR2(255)
 ADDRESS_LINE2                                      VARCHAR2(255)
 CITY                                      NOT NULL VARCHAR2(255)
 STATE                                     NOT NULL VARCHAR2(2)
 ZIPCODE                                   NOT NULL VARCHAR2(255)
 PHONE                                              VARCHAR2(12)
 AGE                                                NUMBER
 STATUS                                             NUMBER

The Perl code for a simple utility either to query or add a new record to our customer_data table is shown in Listing 2.

Listing 2. Script to Query or Add a Record


#!/usr/bin/perl
#############################################################################

use strict;
use warnings;
use DBI;
use Getopt::Long;

#############################################################################
# Configuration section
#############################################################################

# Database type (oracle or mysql)
#my $db_type = "mysql";
my $db_type = "oracle";

# Database information for mysql db_type
my $mysql_host = "localhost";
my $mysql_user = "sqluser";
my $mysql_passwd = "sqlpass";
my $mysql_db = "testdb";

# Database variables for oracle db_type
my $ora_host = "localhost";
my $ora_user = "sqluser";
my $ora_passwd = "sqlpass";
my $ora_sid = "testdb";


#############################################################################
# Variables
#############################################################################

use vars qw ( $dbh $create_mode $query_mode $username $first_name $last_name $addr1 $addr2 $city $state $zipcode $phone $age $verbose $print_help );


#############################################################################
# Start of program execution 
#############################################################################

# Check command line options
&check_options();

# Connect to the database
&database_connect();

# Perform the requested action
if ($query_mode) {
   &query_table();
}
elsif ($create_mode) {
   &add_record();
}

# Disconnect from the database
&database_disconnect();

# Exit
exit(0);



#############################################################################
#
# Subroutine: database_connect
# Description:
#
#    Connect to the database
# 
#############################################################################

sub database_connect {

   if ($db_type eq "mysql") {
      $dbh = DBI->connect("DBI:mysql:database=$mysql_db;host=$mysql_host",
                          $mysql_user, $mysql_passwd)
         or &db_error("Error: Cannot connect to $mysql_db db on $mysql_host");
   }
   elsif ($db_type eq "oracle") {
      $dbh = DBI->connect("DBI:Oracle:host=$ora_host;sid=$ora_sid",
                          $ora_user, $ora_passwd)
         or &db_error("Error: Cannot connect to $ora_sid schema on $ora_host");
   }
   else {
      print "Error: unsupported database type: $db_type\n";
      exit(1);
   }
}

#############################################################################
#
# Subroutine: database_disconnect
# Description:
#
#   Disconnect from the database
# 
#############################################################################

sub database_disconnect {
   if ($dbh) {
      $dbh->disconnect;
   }
}

#############################################################################
#
# Subroutine: db_error
# Description:
# 
#    Print any database errors, if they occur.
#
#############################################################################

sub db_error {
   my $error_string	= $_[0];
   my $sql_string	= $_[1];

   print STDERR "A database error has occurred:\n";
   print STDERR "   $error_string\n";
   if ($sql_string) {
      print STDERR "   SQL: $sql_string\n";
   }
}

#############################################################################
#
# Subroutine: add_record
# Description:
# 
#    Add a new user to the database
#
#############################################################################


sub add_record {
   my $sth; my $error;

   $sth = $dbh->prepare("INSERT into customer_data (username, first_name, last_name, address_line1, address_line2, city, state, zipcode, phone, age, status) VALUES (\'$username\', \'$first_name\', \'$last_name\', \'$addr1\', \'$addr2\', \'$city\', \'$state\', \'$zipcode\', \'$phone\', $age, 1)");
   if (!$sth) {
      $error = $dbh->errstr;
      &db_error("Error: $error");
   }
   if (!$sth->execute) {
      $error = $sth->errstr;
      $sth->finish;
      &db_error("Error: $error");
   }
   $sth->finish;

   print "Added $username successfully to the database.\n";
}

#############################################################################
#
# Subroutine: query_table
# Description:
# 
#    Query the database, returning information for all users
#
#############################################################################


sub query_table {
   my $sth; my $error;

   $sth = $dbh->prepare("SELECT username, first_name, last_name, address_line1, address_line2, city, state, zipcode, phone, age FROM customer_data WHERE status=1");
   if (!$sth) {
      $error = $dbh->errstr;
      &db_error("Error: $error");
   }
   if (!$sth->execute) {
      $error = $sth->errstr;
      $sth->finish;
      &db_error("Error: $error");
   }
   my $rows = $sth->rows;
   if ($rows eq 0) {
      print "No users exist.\n";
      return;
   }


   my $numFields = $sth->{'NUM_OF_FIELDS'};
   while (my $ref = $sth->fetchrow_arrayref) {
      for (my $i = 0 ; $i < $numFields;  $i++) {
         if ($i eq 0) {
            print "Username: $$ref[$i]\n";
         }
         elsif ($i eq 1) {
            print "Name:     $$ref[$i] ";
         }
         elsif ($i eq 2) {
            print "$$ref[$i]\n";
         }
         elsif ($i eq 3) {
            print "Address:  $$ref[$i]\n";
         }
         elsif ($i eq 4) {
            print "          $$ref[$i]\n";
         }
         elsif ($i eq 5) {
            print "City:     $$ref[$i]\n";
         }
         elsif ($i eq 6) {
            print "State:    $$ref[$i]\n";
         }
         elsif ($i eq 7) {
            print "Zip Code: $$ref[$i]\n";
         }
         elsif ($i eq 8) {
            print "Phone:    $$ref[$i]\n";
         }
         elsif ($i eq 9) {
            print "Age:      $$ref[$i]\n";
         }
      }
      print "\n";
   }
   $sth->finish;
}




#############################################################################
#
# Subroutine: check_options
# Description:
#
#    Check the command line options and set the appropriate
#    variables accordingly.
#
#############################################################################
sub check_options {
   my $result;
   $result = GetOptions ("verbose"      => \$verbose,
                         "create"       => \$create_mode,
                         "query"        => \$query_mode,
                         "user=s"       => \$username,
                         "first=s"      => \$first_name,
                         "last=s"       => \$last_name,
                         "addr1=s"      => \$addr1,
                         "addr2=s"      => \$addr2,
                         "city=s"       => \$city,
                         "state=s"      => \$state,
                         "zip=s"        => \$zipcode,
                         "phone=s"      => \$phone,
                         "age=i"        => \$age,
                         "help"         => \$print_help);
			
   if (!$result) {
      &print_usage();
      exit(1);
   }

   if ($print_help) {
      &print_usage();
      exit(0);
   }

   if (!$query_mode && !$create_mode) {
      print STDERR "Please select either create or query mode.\n";
      &print_usage();
      exit(1);
   }
   elsif ($create_mode) {
      if (!$username||!$first_name||!$last_name||!$addr1||!$city||!$state||
          !$zipcode||!$age) {
         print STDERR "Missing required information. Any options not in square\n";
         print STDERR "   brackets are required for create mode.\n";
         &print_usage();
         exit(1);

      }
      if (!$addr2) {
         $addr2 = "";
      }
      if (!$phone) {
         $phone = "";
      }
   }

}

#############################################################################
#
# Subroutine: print_usage
# Description:
#
#    Print usage information
#
#############################################################################


sub print_usage {
   print "Usage: $0 --query\n";
   print "       $0 --create --user <user> --first <first name> \\\n";
   print "          --last <last name> --addr1 <address line 1> \\\n";
   print "          [--addr2 <address line 2>] --city <city> \\\n";
   print "          --state <state> --zip <zip code> [--phone <phone>] --age <age>\n";

}

The first thing we do, at line 6 of our program, is import the DBI database access module. This module allows us to use various DBI drivers, such as DBD::Oracle and DBD::mysql, to access a RDBMS with Perl.

Next, at line 7, we import the Getopt::Long module. Getopt::Long allows us to use the GetOptions() subroutine to parse GNU extended command line options, such as --verbose and --help.

At line 15, we set the type of database to which we are connecting. This is not related directly to the DBI routines, but it lets us specify to ourselves the type of database to which we are connecting, in the event that we need to talk to multiple RDBMS flavors.

With lines 18 through 27, we set the database connection related variables, both for Oracle and for MySQL. These are used by the database_connect() subroutine to create our database session.

Line 34 declares other various variables we will be using throughout the program.

Lines 42 through line 59 contain the main section of the program. At line 42, we execute the check_options() subroutine, declared later, to parse the command-line options passed to the program. At line 45, we execute the database_connect() subroutine, which connects us to our database of choice and creates our database handle, stored in $dbh.

Lines 48 through 53 determine whether we are using query mode to read customer data from the database or create mode to create a new customer data record in the database. If $query_mode is set, which is discussed later on in check_options, we then execute the query_table() subroutine. Otherwise, if $create_mode is set, we execute the add_record() subroutine to create a new record in customer data.

Finally, at line 56, we execute our database_disconnect() subroutine to disconnect from the database, and exit with a status of 0.

Our database_connect() subroutine starts at line 72. We check the contents of $db_type. For a MySQL database type, we use the DBD::mysql DBI driver at line 75 to connect to the database with a DBI->connect() call. If $db_type is set to Oracle, we use the DBD::Oracle DBI driver at line 80 to connect to the database with a similar but slightly different call to DBI->connect(). If the $db_type variable doesn't match any of those cases, an error is generated and we exit with an unsuccessful return code. You can use the DBI->available_drivers method to print a list of DBI database drivers installed on your system.

Each DBI driver defines its own syntax for the first argument to DBI->connect--the data source argument. For example, our program uses:


	$dbh = DBI->connect("DBI:Oracle:host=$ora_host;sid=$ora_sid",
                          $ora_user, $ora_passwd);

Another method determines the hostname of the Oracle database server using tnsnames:



	$dbh = DBI->connect("DBI:Oracle",
                          "$ora_user/$ora_passwd@$ora_sid", "");

For more information, consult the DBD::Oracle manual page.

Lines 99 through 103 declare the database_disconnect() subroutine, which simply checks for a valid database handle ($dbh) and executes the $dbh->disconnect method. if one exists.

Lines 114 through 123 define our db_error() subroutine, which is used throughout the database code to detect and report database errors.

Our next subroutine, add_record(), is defined from line 135 to line 151. This is the subroutine called when the program is called in --create mode. At line 138, we use the $dbh->prepare() method to create a statement handle ($sth) that contains our SQL INSERT statement. This statement inserts a new row into customer_data with all of the data given on the command line, which we obtain later in check_options(). At line 143, after the statement handle has been validated, we call $sth->execute() to execute the SQL statement we prepared earlier. Lastly, we close the statement with $sth->finish() and print a message saying that we have created the new row successfully.

Lines 163 to 220 contain our largest subroutine, the query_table() subroutine. query_table() is called to obtain the contents of the customer_data table when the program is called in query mode. The query_table() subroutine is quite similar to add_record() but contains more code to read the data that we attempt to read from the database. Lines 166 through 175 are similar to those in the add_record() subroutine, but here, we instead use a SQL SELECT statement to read the data from the customer_data table. Lines 176 through 180 check the number of rows returned by our statement. If 0 results are returned, we simply print a message stating that no data exists and return to the main program. Next, we check the number of columns returned, stored in $sth->{'NUM_OF_FIELDS'} and store that in our own variable, $numFields.

Next, we cycle through each of the rows returned from the database with a while() statement, starting at line 184. For each cycle, we execute $sth->fetchrow_arrayref() to fetch an array reference containing our data and store it in a local variable, $ref. Starting at line 185, we cycle through each column returned in the row. Lines 186 through 215 determine which piece of data is given to us and prints it to standard output. Finally, at line 219 we call $sth->finish to complete our statement.

Our last major subroutine, check_options(), starts at line 234. check_options() wraps the Getopt::Long module's GetOptions() subroutine to parse the options passed to the program on the command line. Lines 236 through 249 contain the call to GetOptions, with a list of options to accept and variables to store the values of those options. At line 251, if GetOptions returns an error, we print our usage message with print_usage(), defined later, and exit unsuccessfully. At line 256, if the $print_help variable is defined with a --help option being passed on the command line, we print the usage message with print_usage() and exit successfully. Lines 261 through 281 determine whether --query or --create mode was passed on the command line. If --create mode was passed, it validates that all necessary information is given.

Finally, lines 295 through 302 define the print_usage() subroutine used by check_options() to print the usage message.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Programming for Oracle on Linux, Part I

Anonymous's picture

I found working with oracle to be quite powerful with Perl when I used it to process large amounts of scientific data. After working with it I even wrote a short tutorial..

http://www.kestersoftware.com/content/view/18/2/

Re: Programming for Oracle on Linux, Part I

Anonymous's picture

Glad someone said something about the author Oracle a "legacy" database. An ignorant/immature thing to say.
In fact, i lost interest in the article after I read that...bastard
-az

Re: Programming for Oracle on Linux, Part I

Anonymous's picture

You have to be an idiot to call Oracle RDBMS a legacy product. Do you actually know the meaning of the word, legacy? Oracle is the leader in Database technology. The standard by which the others are measured. It is what the others (including Open source databases) are trying to be.
Oracle8i is still far ahead of the others.

"Quite a few shops still run legacy databases, however, using commercial database servers such as Sybase or Oracle"

I wouldn't say "quite a few". The vast majority of Fortune 500
companies "swear" by Oracle.

IMHO, Linux reputation as a viable server OS is not connected to the fact reputable products like Oracle RDBMS have been ported to it.

Just take a look at what MySQL has to offer.
http://sql-info.de/mysql/gotchas.html

"Even Linux Journal gave its 2001 Editors' Choice Award for Best Database to Oracle."

Linux has established itself as serious contender in the server OS space. Did you expect that judges will simply turn a blind eye to true remarkable effort from Oracle to favor MySQL just because Linux and MySQL are open soure software?

Don't get me wrong, I am a big fan of open source. I love Linux, Apache, KDE, PHP, PostgreSQL (I think PostgresQL is the closest thing to free Oracle) etc.
However, if I find out that my bank has adopted MySQL as their enterprise database software, I would simply withdraw my balance and move to another bank.

Re: Programming for Oracle on Linux, Part I

ryanordway's picture

If you read my answer to a similar question, I was not referring to Oracle itself as a legacy database, I was referring to sites who primarily use another RDBMS but also have a "legacy" Oracle database, or an older database (the data, not the software) that is not their primary platform.

Re: Programming for Oracle on Linux, Part I

Anonymous's picture

Insert data into SQL-query is a vary bad practice, especially when you use Oracle. Use bind variables!

Re: Programming for Oracle on Linux, Part I

Anonymous's picture

Correction: -- should read

IMHO, Linux reputation as a viable server OS is not unconnected to the fact reputable products like Oracle RDBMS have been ported to it.

Re: Programming for Oracle on Linux, Part I(correction)

Anonymous's picture

should read:

IMHO, Linux reputation as a viable server OS is not unconnected to the fact reputable products like Oracle RDBMS have been ported to it.

Oracle just sucks

Anonymous's picture

I mean you can't even install their libraries and development files on, say, Slackware Linux. Compared to MySQL or PostgreSQL its just pain in the ...

Re: Oracle just sucks

Anonymous's picture

What do you mean? I've run all versions of Oracle since 8.0.5 on Slackware.

Re: Programming for Oracle on Linux, Part I

Anonymous's picture

What was the definition of 'legacy' for this article. It reads that legacy means database software you have to pay for ? In which case the commercial version of MySQL, V4, would be legacy. But then that would make the Open version of MySQL legacy but the article says that MySQL 3 isn't. Confusing.

Also I think you will probably find that Companies running massive databases require a highly scaleable and often commercial database.

I use both MySQL 3 and Oracle. I do have some legacy systems and they could be running on Oracle or MySQL and be upgrade to a new non-legacy system also running Oracle or MySQL . The system being legacy but not the software, although might be due for an upgrade.

Duncan

Re: Programming for Oracle on Linux, Part I

Anonymous's picture

What you've shown "in only a few hundred lines of code" could be achived with far less if you'd used Perl and the DBI in a more idiomatic style. There are many flaws in your example code at many levels. If you, and your editor, can't see them I suggest you post the code to the dbi-users@perl.org mailing list where you're sure to find help.

I'm delighted to see articles about the DBI but disapointed when they fail to show the elegance of expression that's possible.

I see this is "Part 1". Please don't post more example code in later parts without getting it reviewed by an experienced DBI user first. Both you and your Linux Journal readers will benefit.

Tim Bunce

Re: Programming for Oracle on Linux, Part I

Anonymous's picture

Yes, Oracle 8i is a legacy database. To help people, you really should post a link to installing Oracle 10g (or at least 9i)

Re: Programming for Oracle on Linux, Part I

Anonymous's picture

I don't think I'd call Oracle a "legacy" database system, just yet. Bound to get you more than your fair share of slings & arrows. Now if you want to call Ingres or Informix, or even HP Turbo Image legacy dbms's that's within reason.

Dick Goulet
Senior Oracle DBA/Oracle Certified DBA
rjgoulet@comcast.net

Re: Programming for Oracle on Linux, Part I

ryanordway's picture

Agreed. I mainly had meant "legacy" in the sense that many Linux based shops would be running primarily Open Source RDBMS such as MySQL and PostgreSQL. However, they may have a "legacy" Oracle database that they still need to be able to interact with.

I'm sure there are those who would still call Oracle a "legacy" product, but I wouldn't go so far as to say that yet. ;-)

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix