Spotwise

In the previous post I described how to install the Oracle Database 10g Express Edition on Ubuntu and to add some data. In this article I will show how to access that data from a Perl script.

I assume you already have Perl installed, together with the DBI framework for generic database support. We will need to add DBD::Oracle which is available from CPAN. To install this module, run this as root:

# perl -MCPAN -e shell
cpan> install DBD::Oracle

Two things are worth noting. First, the root user must have the same Oracle environment set as described in the previous article. Just issue ‘source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh’ to fix that. The second thing to note is that you will most likely get errors during testing of the newly built module which will prevent it from being installed. To override that you will need to add ‘force’ before the command, i.e.:

cpan> force install DBD::Oracle

Now, the groundwork is done and we just need to write the script. Save this in oracle_read.pl and make it executable.

#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect( 'dbi:Oracle:xe',
      'scott',
      'tiger',
      ) || die "Database connection not made: $DBI::errstr";

my $sql = qq{ SELECT id,name,age FROM persons };
my $sth = $dbh->prepare($sql);
$sth->execute();
my($id, $name, $age);
$sth->bind_columns(\$id, \$name, \$age);

print "List of persons:\n";
while( $sth->fetch() ) {
    print "$name [$age]\n";
}
$sth->finish();
$dbh->disconnect;

I am a rather proficient user of MySQL but I recently needed to set up an Oracle database to test against. Since I haven’t worked with Oracle databases in more than ten years it took literally hours to get something up and running. What follows is a description on how to install Oracle XE on Ubuntu 8.10, add a user, create a table and then drop the created schema. Everything will be done from the command line.

Note that this installs the Oracle Database 10g Express Edition. Although free it comes with similar constraints as the Microsoft SQL Server 2008 Express server – maximum 4 GB data, use maximum 1 GB of RAM and run on maximum 1 CPU.

First, add the Oracle repositories by appending the following two lines to /etc/apt/sources.lst

# Oracle Repository

deb http://oss.oracle.com/debian unstable main non-free

Then, we must add the Oracle key to avoid warnings:

wget http://oss.oracle.com/el4/RPM-GPG-KEY-oracle -O- | sudo apt-key add -

Now we are ready to install by using apt-get (either run the following as root or prepend every line with sudo)

# apt-get update
# apt-get install oracle-xe oracle-xe-client
# /etc/init.d/oracle-xe configure

The last command will present options to change some default settings. you can probably use most of the defaults. I just changed the HTTP port from 8080 since I already had another service running on that port. Note that the configuration script takes a very long time to finish.

When the script is finished it is possible to access the Oracle web interface at http://127.0.0.1:8080/apex (or some other port if you changed the default). However, it will only be accessible on the local host. If you are installing on a headless remote server like me you can port-forward using SSH:

$ ssh -L 8080:127.0.0.1:8080 user@host

Or, you can remove this limitation as described further below.

Next step will be to add the Oracle environment to your shell. The installation will have added scripts to set up the environment under /usr/lib/oracle/xe/app/oracle/product/10.2.0/. There are actually two scripts: server/bin/oracle_env.sh and client/bin/oracle_env.sh. I don’t think it matters which one but it makes more sense to use the one under ‘server’.

Append this to your .bash_profile file:

. /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh

Then log out and back in – or run ‘source .bash_profile’.

Now it is time to log onto the system:

$ sqlplus system@localhost 

If you would have logged on remotely you would have written ‘sqlplus system@host’ – but we haven’t enabled remote access yet.

Type the password you selected during installation. At the SQL prompt, enter:

> EXEC DBMS_XDB.SETLISTENERLOCALACCESS(FALSE);
> EXIT;

Now it is time to add the first user. Save the following to a file (oracle_create.sql).

-- oracle_create.sql
CREATE USER scott IDENTIFIED BY tiger 
    DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp 
QUOTA UNLIMITED ON users;
CREATE ROLE myrole;
GRANT CREATE session, CREATE table, CREATE view, 
    CREATE procedure, CREATE synonym TO myrole;
GRANT myrole TO scott;
-- Switch user
CONNECT scott@localhost/tiger;
--
CREATE TABLE persons (
	id int,
	name varchar2(32),
	age number
);
--
INSERT INTO persons
	(id, name, age)
	VALUES (1, 'Joe', 35);
INSERT INTO persons
	(id, name, age)
	VALUES (2, 'Mary', 32);

You can then create the user and populate the table by running:

$ sqlplus system@localhost
> @oracle_create
> SELECT * FROM persons;
> EXIT

The ampersand indicates that SQL statements should be read from a file. Since the file ends with .sql the extension does not have to be stated. Please note the connect statement in the SQL file. This means that after that point we will be running as the user scott.

To drop everything we have created run the following:

$ sqlplus system@localhost
> drop user scott cascade;
> drop role myrole;