plainblack.com
Username Password
search
Bookmark and Share
Subscribe

Oracle and SQL-reports


This worked for me for connecting to an Oracle database:

First install the Oracle client for your system (http://www.oracle.com/technetwork/database/express-edition/downloads/index.html)

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

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


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/demo_xe.mk

then just:

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

 

  • make
  • make install



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

#!/data/wre/prereqs/bin/perl

use DBI;

#definition of variables
$db="DATABASENAME";  # for me required capitals!
$host="hostname";
$user="dbusername";
$password="password";

#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);
$sth->execute();

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";

exit;


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

Keywords:

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