A Week with Oracle

This story is related to something happened to me few weeks ago.

Its a monday, On mid march while I was went out for an urgent matter while working, I got a call from my buddy aka saying that I need to be in XYZ company immediately for an urgent matter.

First I was confused what da heck is going on................ and couple minutes later one of our team guy came to pick me and I got a lift to that XYZ company. (Truely saying this is a telecom operator).

After I went there my buddy told me bla bla stuff regarding an IVR (Interactive Voice Response) system that those ppl were struggling to launch.

Mmmmmmmmmmmmmmmmmm.. I think I'm still confused with what the situation is all about. I ask 'em couple times to explain the scenario without confusing me.

They were really messed up @ that point and telling so many bla bla stuff to me.

After about half an hour I realize what they need and what the heck wrong with 'em.

Ok ..................... This is the story. They need an IVR application to be develop to handle their ABC tariff package which is currently doing in a manual manner.

So first important thing to me was to figureout a way to connect with their backend database engine. It was an Oracle database. OOOOoooooooppppzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz........

Fortunately I never had came a cross with such a situation. Most of the time we use MySql as our backend database, so this is completely new and existing and I was also struggling to connect an Oracle database with perl from a long time ago ( about 3 months, fortunatley I didnt had a good server with some RAM to install an oracle database server)

I thought this will be a great chance for me to play around with Oracle and perl. Now you might have what the heck is perl is doing here. Whatz the use of perl in this story.

perllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll MMMMMMMMmmmmmmmmmmmm
I always use perl as our AGI (Asterisk Gateway Interface) scripting language. AGI is an API provided by asterisk server for programmers to do wonders with asterisk dialplan. We can write simple, smart, complex, long IVR applications using asterisks AGI API.

Ok hope now you got an idea about whatz perl is doing in this story.

Now I'm back on the story ok ?????. First I try to build perls database connector for oracle with following packages. I needed oracle client utility rpms for this. I'm using an CentOS box for this application.

I got these rpms from oracle site after registering with their services.

1. http://download.oracle.com/otn/linux/instantclient/111070/oracle-instantclient11.1-basic-11.1.0.7.0-1.i386.rpm
2. http://download.oracle.com/otn/linux/instantclient/111070/oracle-instantclient11.1-sqlplus-11.1.0.7.0-1.i386.rpm
3. http://download.oracle.com/otn/linux/instantclient/111070/oracle-instantclient11.1-devel-11.1.0.7.0-1.i386.rpm
4. http://download.oracle.com/otn/linux/instantclient/111070/oracle-instantclient11.1-odbc-11.1.0.7.0-1.i386.rpm
5. http://download.oracle.com/otn/linux/instantclient/111070/oracle-instantclient11.1-tools-11.1.0.7.0-1.i386.rpm

Then I installed the above rpms and build the Oracle-DBD module for perl.

For this we need to have perls DBI module and the DBD-Oracle module installed. So I got those from here,

1. http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBI-1.50.tar.gz
2. http://search.cpan.org/CPAN/authors/id/T/TI/TIMB/DBD-Oracle-1.16.tar.gz


We can use following line to check whether our Oracle module has been successfully installed.

perl -e 'use DBD::Oracle; print $DBD::Oracle::VERSION,"\n";'

It should give something like follwoing,

1.17

After successfully compiling the two modules. I try to connect their backup database with a simple script as follow,

............................................................................................................................

While I was writing this post last night C.E.B again played with me. They cut down the electricity again.

I started to continue writing today morning.

Ok i'll continue the story,

After I installed the oracle module for perl I use the following code snippet to connect to their oracle server.

#!/usr/bin/perl

use DBI;

$ora_host = "10.76.100.47";
$ora_user = "xxxx";
$ora_passwd = "xxxxx";
$ora_sid = "xxxx";


$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");
print "Connected to Oracle succeeded !\n";


When I run this script for the first time I was not able to connect to the database. I got an error saying that theres no listeners for the particular database I was trying to connect.

Then I complain about that to my co programmer who was handling the database. But He didn't accept that and keep on saying that Theres nothing wrong with their database server and some of their applications are using it @ that time.

Unfortunately I got access to their database server via a NATed IP. I never thought this could be the reason why my script does n't work.

After struggling about a day I gave up connecting with oracle , coz so many ppl told hell of a lot of assumptions about this and @ the end of the day I got the blame saying my code is nt correct and there must be something else to be done in order to connect oracle.

Next day When I go there (XYZ company). I suggested them to connect my client machine directly to the network that their Oracle server reside, for that we need to carry our (*) box to 1 of their highly secured area.

After we connect our (*) box there I ran the same script I have pasted above.
Here we gooooooooooooooooooooooooooooooooooooooooooooooooooooo

I got the following line in my terminal,
Connected to Oracle succeeded !

I said,
Hooooooooooooooooooooooooooooooooooooooooooooooooooray !!!!!!!!!!!!!

Then I told that I got able to connect with the server and they to realize and understood that there was a problem with their NAT network.

After I was able to connect with the oracle server I went there (XYZ company ) thru out that week and finally able to finish the IVR application that those ppl were struggling to finish.

Ok Thatz the story about Oracle that I had to said, I thought to write it down here coz I feel someday someone can get an idea about the same topic, and will help my post for 'em.

Ok I'm gonna stop for today coz got lot of work to finish @ office.



Thanks

Pasan