Skip navigation

Tag Archives: Perl

I’ve been given the task of writing some perl scripts which will run on a Solaris 10 machine and communicate with an Oracle 10g XE database which in turn is living on a RHEL5 box.

While I have some experience installing/patching Oracle, I’ve never written a script which communicates directly with it – so this should be a good learning exercise.

There’s a simple table in the database called “SM_USER_CONFIG” which contains a few rows just for testing.

The first step I feel, is to get the machine communicating with the database via sqlplus.

To do this, we need to install “Instant Client” – We want “Basic” “Sqlplus” and “SDK”

(available from http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/solx86soft.html)

I extracted all of these to /opt/instantclient:

(TCSUNDEV01:/opt/instantclient) sysmgr > ls
classes12.jar  libclntsh.so.10.1*  libocci.so.10.1*  libsqlplus.so*    sdk/        tnsnames.ora
glogin.sql     libnnz10.so*        libociei.so*      libsqlplusic.so*  sqlnet.log
libclntsh.so@  libocci.so@         libocijdbc10.so*  ojdbc14.jar       sqlplus*

We need to create a /opt/instantclient/tnsnames.ora file like this:

XE.WORLD =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS =
          (COMMUNITY = tcp.world)
          (PROTOCOL = TCP)
          (Host = 10.17.58.22)
          (Port = 1521)
        )
    )
    (CONNECT_DATA = (SID = XE)
    )
  )

Lets try and connect with SQL plus:

(TCSUNDEV01:~) sysmgr > setenv TNS_ADMIN /opt/instantclient/
(TCSUNDEV01:~) sysmgr > /opt/instantclient/sqlplus username/password@XE.WORLD

SQL*Plus: Release 10.2.0.2.0 – Production on Thu Jun 25 19:16:00 2009
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

SQL> SELECT USERNAME
  2  FROM SM_USER_CONFIG
  3  /

USERNAME
——————————
HoustonN
UserTest
TestUser 

SQL>

Cool, it works.

Next up is perl. We will build DBD::Oracle for this. Head over to cpan and download the src. We’re using CSWperl from blastwave.

(TCSUNDEV01:/var/tmp) sysmgr > gzcat /tmp/DBD-Oracle-1.23.tar.gz | gtar -xf –
(TCSUNDEV01:/var/tmp) sysmgr >cd DBD-Oracle-1.23
(TCSUNDEV01:/var/tmp/DBD-Oracle-1.23) sysmgr > setenv TNS_ADMIN /opt/instantclient/
(TCSUNDEV01:/var/tmp/DBD-Oracle-1.23) sysmgr > setenv LD_LIBRARY_PATH /opt/instantclient/
(TCSUNDEV01:/var/tmp/DBD-Oracle-1.23) sysmgr > setenv ORACLE_SID XE
(TCSUNDEV01:/var/tmp/DBD-Oracle-1.23) sysmgr > perl Makefile.PL -V 10.2.0.1

Using DBI 1.604 (for perl 5.008008 on i86pc-solaris-thread-multi) installed in /opt/csw/lib/perl/csw/auto/DBI/

Configuring DBD::Oracle for perl 5.008008 on solaris (i86pc-solaris-thread-multi)

Remember to actually *READ* the README file! Especially if you have any problems.

Trying to find an ORACLE_HOME
Found /opt/instantclient/

WARNING: Setting ORACLE_HOME env var to /opt/instantclient/ for you.
WARNING: If these tests fail you may have to set ORACLE_HOME yourself!
Installing on a solaris, Ver#2.8
Using Oracle in /opt/instantclient/
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly

            If sqlplus failed due to a linker/symbol/relocation/library error or similar problem
            then it’s likely that you’ve not configured your environment correctly.
            Specifically, your LD_LIBRARY_PATH environment variable
            set to include the directory containing the Oracle libraries.

Forcing Oracle version to be treated as 10.2.0.1
Oracle version 10.2.0.1 (10.2)
Looks like an Instant Client installation, okay
Your LD_LIBRARY_PATH env var is set to ‘/opt/instantclient/’
Oracle sysliblist:
Found header files in /opt/instantclient//sdk/include.

Checking for functioning wait.ph
System: perl5.008008 sunos thor 5.8 generic_117351-51 i86pc i386 i86pc
Compiler:   cc -xO3 -xtarget=generic -xarch=generic -D_REENTRANT -xO3 -xtarget=generic -xarch=generic -I/opt/csw/bdb44/include -I/opt/csw/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64
Linker:     /usr/ccs/bin/ld
Sysliblist:
Linking with -lclntsh.

LD_RUN_PATH=/opt/instantclient
Using DBD::Oracle 1.23.
Using DBD::Oracle 1.23.
Using DBI 1.604 (for perl 5.008008 on i86pc-solaris-thread-multi) installed in /opt/csw/lib/perl/csw/auto/DBI/
Writing Makefile for DBD::Oracle

***  If you have problems…
     read all the log printed above, and the README and README.help.txt files.
     (Of course, you have read README by now anyway, haven’t you?)

Notice the warning about sqlplus failing above. As we’re building this on a CLIENT, when sqlplus is invoked it doesn’t specify @XE.WORLD (default is to connect to a local database)- We can just ignore this.

Go ahead and run “make install”

(TCSUNDEV01:/var/tmp/DBD-Oracle-1.23) sysmgr > pfexec gmake install
Skip blib/lib/DBD/Oracle.pm (unchanged)
Skip blib/lib/oraperl.ph (unchanged)
Skip blib/arch/auto/DBD/Oracle/dbdimp.h (unchanged)
Skip blib/arch/auto/DBD/Oracle/ocitrace.h (unchanged)
Skip blib/lib/Oraperl.pm (unchanged)
Skip blib/arch/auto/DBD/Oracle/Oracle.h (unchanged)
Skip blib/lib/DBD/Oracle/Object.pm (unchanged)
Skip blib/arch/auto/DBD/Oracle/mk.pm (unchanged)
Skip blib/lib/DBD/Oracle/GetInfo.pm (unchanged)
cc -c  -I/opt/instantclient//sdk/include -I/opt/csw/lib/perl/csw/auto/DBI -D_REENTRANT -xO3 -xtarget=generic -xarch=generic -I/opt/csw/bdb44/include -I/opt/csw/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -xO3 -xtarget=generic -xarch=generic   -DVERSION=\”1.23\” -DXS_VERSION=\”1.23\” -KPIC “-I/opt/csw/lib/perl/5.8.8/CORE”  -DUTF8_SUPPORT -DNEW_OCI_INIT -DORA_OCI_VERSION=\”10.2.0.1\” Oracle.c
cc -c  -I/opt/instantclient//sdk/include -I/opt/csw/lib/perl/csw/auto/DBI -D_REENTRANT -xO3 -xtarget=generic -xarch=generic -I/opt/csw/bdb44/include -I/opt/csw/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -xO3 -xtarget=generic -xarch=generic   -DVERSION=\”1.23\” -DXS_VERSION=\”1.23\” -KPIC “-I/opt/csw/lib/perl/5.8.8/CORE”  -DUTF8_SUPPORT -DNEW_OCI_INIT -DORA_OCI_VERSION=\”10.2.0.1\” dbdimp.c
cc -c  -I/opt/instantclient//sdk/include -I/opt/csw/lib/perl/csw/auto/DBI -D_REENTRANT -xO3 -xtarget=generic -xarch=generic -I/opt/csw/bdb44/include -I/opt/csw/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -xO3 -xtarget=generic -xarch=generic   -DVERSION=\”1.23\” -DXS_VERSION=\”1.23\” -KPIC “-I/opt/csw/lib/perl/5.8.8/CORE”  -DUTF8_SUPPORT -DNEW_OCI_INIT -DORA_OCI_VERSION=\”10.2.0.1\” oci8.c
Running Mkbootstrap for DBD::Oracle ()
chmod 644 Oracle.bs
rm -f blib/arch/auto/DBD/Oracle/Oracle.so
LD_RUN_PATH=”/opt/instantclient” cc  -G -L/opt/csw/bdb44/lib -L/opt/csw/lib -L/usr/lib -L/usr/ccs/lib -L/opt/SUNWspro/prod/lib -L/lib Oracle.o dbdimp.o oci8.o  -o blib/arch/auto/DBD/Oracle/Oracle.so  \
           -L/opt/instantclient/ -lclntsh       \

chmod 755 blib/arch/auto/DBD/Oracle/Oracle.so
cp Oracle.bs blib/arch/auto/DBD/Oracle/Oracle.bs
chmod 644 blib/arch/auto/DBD/Oracle/Oracle.bs
/opt/csw/bin/perl “-Iblib/arch” “-Iblib/lib” ora_explain.PL ora_explain
Extracted ora_explain from ora_explain.PL with variable substitutions.
cp ora_explain blib/script/ora_explain
/opt/csw/bin/perl “-MExtUtils::MY” -e “MY->fixin(shift)” blib/script/ora_explain
Manifying blib/man1/ora_explain.1
Manifying blib/man3/DBD::Oracle.3perl
Manifying blib/man3/DBD::Oraperl.3perl
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Writing /opt/csw/lib/perl/site_perl/auto/DBD/Oracle/.packlist
Appending installation info to /opt/csw/lib/perl/5.8.8/perllocal.pod
(TCSUNDEV01:/var/tmp/DBD-Oracle-1.23) sysmgr >

And test:

(TCSUNDEV01:/var/tmp/DBD-Oracle-1.23) sysmgr > perl -MDBD::Oracle -le ‘print $DBD::Oracle::VERSION;’
1.23

Finally, lets put togeather a little perl script to query our sample table:

 

use DBI;

$dbh = DBI->connect(‘dbi:Oracle:’, q{USER/PASSWORD@(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.17.58.22)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )},””) or die DBI::errstr;

$dbstr = $dbh->prepare(“SELECT id, username, hostname FROM sm_user_config”);
$dbstr->execute;
$~ = TABLEHEADER;
write;

while (($rdbid, $rdbu, $rdbv) = $dbstr->fetchrow_array()) {
        $~ = TABLEENTRY;
        write;
}

$~ = TABLEBOTTOM;
write;
print “\n”;

 

format TABLEHEADER =

+——–+—————–+—————-+
| ID     |   User          |  VMName        |
+——–+—————–+—————-+
.

format TABLEENTRY =
| @<<<<< | @<<<<<<<<<<<<   | @<<<<<<<<<<<<< |
 $rdbid,   $rdbu,            $rdbv
.

format TABLEBOTTOM =
+——–+—————–+—————-+
.

 

Running it, we get:

 


(TCSUNDEV01:~) sysmgr > perl ora2.pl

+--------+-----------------+---------+
| ID | User | VMName |
+--------+-----------------+---------+
| 1 | HoustonN | VXPTEST39 |
| 3 | UserTest | VXPTEST2 |
| 2 | TestUser | VXPTEST5 |
+--------+-------------+-------------+

 

(Formatting got a bit messed up when I pasted, but you get the idea =)
Party on!

Advertisements