Username Password
Bookmark and Share

Oracle and SQL-reports

This worked for me for connecting to an Oracle database:

First install the Oracle client for your system (

Install DBI and DBD::Oracle
Easy way:
Login to your server

  • sudo su -
  • . /data/wre/sbin/

Start CPAN:

  • /data/wre/prereqs/bin/cpan

Within CPAN:

  • install DBI
  • install DBD::Oracle

For me installing DBD::Oracle failed, so I compiled it by hand:

Find your cpan build directory (for me that was /root/.cpan/build)
Within that directory find the DBD-Oracle-1.xx-xxx directory and cd to it,

  • i.e. cd /root/.cpan/build/DBD-Oracle-1.24-9gtLgB/


  • /data/wre/prereqs/bin/perl Makefile.PL

For me that failed since it couldn't find a .mk file. This is somewhere in the dir of your Oracle client software. If you have slocate installed: updatedb and locate *.mk

For me the right one was: /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/demo/

then just:

  • /data/wre/prereqs/bin/perl Makefile.PL -m /usr/lib/oracle/xe/app/oracle/product/10.2.0/client/rdbms/demo/


  • make
  • make install

Test your install with this little script (adapt variables according to your db and usercredentials):


use DBI;

#definition of variables
$db="DATABASENAME";  # for me required capitals!

#connect to Oracle database

my $dbh=DBI->connect("DBI:Oracle:host=$host;sid=$db",$user,$password)

or die "Can't connect to database: $DBI::errstr\n";

my $sql = qq{ SELECT TNAME, TABTYPE FROM TAB };    # Prepare and execute SELECT
my $sth = $dbh->prepare($sql);

my($tname, $tabtype);                     # Declare columns
$sth->bind_columns(undef, \$tname, \$tabtype);

print "List of tables:\n\n";              # Fetch rows from DB
while( $sth->fetch() ) {
    print "Object: $tname, type: $tabtype\n";
$sth->finish();                           # Close cursor

#disconnect from database
$dbh->disconnect or warn "Disconnection error: $DBI::errstr\n";


If that returns a list of tables your good to go.

Restart your mod_perl server! When the testscript is ok, mod_perl needs to be reloaded.

Then add the databaselink in WebGUI
Admin Console -> Databases -> add link
DSN: DBI:Oracle:host=hostname;sid=DATABASENAME

Then the link should read ok.

Now you can add your sqlreport and select the just created link.

Note: If your queries fail, I've noticed that my queries from toad often had a semicolon (;) sign at the end that were problematic. First look for those.

I now can easily build interfaces to Oracle Databases


Search | Most Popular | Recent Changes | Wiki Home
© 2018 Plain Black Corporation | All Rights Reserved