เขียน Perl ติดต่อฐานข้อมูล Oracle #1

หลายท่านทำงานกับฐานข้อมูล Oracle บางท่านอาจจำเป็นต้องเขียนโปรแกรมเพื่อติดต่อกับ Oracle Database ผมเองก็สนใจอยู่หลายๆ โปรแกรม แต่ในบทความนี้ขอเริ่มด้วย PERL ละกัน
สิ่งที่ต้องมีก็คือ Perl program และ Perl Module ผมจะเน้นไปที่ Perl module (DBD::Oracle) การที่เราจะติดตั้ง DBD::Oracle module ได้นั้น เราต้องมี DBI module เสียก่อน แต่ที่ขาดไม่ได้เลยคือ Oracle client library
ในตัวอย่างต้องขออภัยที่ต้องใช้ oracle xe เพราะสะดวกผมในการติดตั้งและทดสอบ ในกรณีที่ท่านมี Oracle Database แยกคนละเครื่อง ท่านสามารถติดตั้ง instantclient ก็เพียงพอแล้ว

$  ldd /usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so | grep oracle
        libclntsh.so.11.1 => /u01/app/oracle/product/11.2.0/xe/lib/libclntsh.so.11.1 (0x00007f1994c89000)
        libnnz11.so => /u01/app/oracle/product/11.2.0/xe/lib/libnnz11.so (0x00007f19942d9000)

นั่นคือตัวอย่าง แสดงให้เห็นว่า DBD::Oracle นั้นต้องการ Library อะไรจาก Oracle HOME

การติดตั้ง Oracle XE นั้นง่ายมาก แค่ download และใช้คำสั่่ง rpm 

[root@fedora ~]# unzip oracle-xe-11.2.0-1.0.x86_64.rpm.zip
Archive:  oracle-xe-11.2.0-1.0.x86_64.rpm.zip
   creating: Disk1/
   creating: Disk1/upgrade/
  inflating: Disk1/upgrade/gen_inst.sql
   creating: Disk1/response/
  inflating: Disk1/response/xe.rsp
  inflating: Disk1/oracle-xe-11.2.0-1.0.x86_64.rpm
[root@fedora ~]# cd Disk1/
[root@fedora Disk1]# ls
oracle-xe-11.2.0-1.0.x86_64.rpm  response  upgrade
[root@fedora Disk1]# rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
error: Failed dependencies:
        libaio >= 0.3.104 is needed by oracle-xe-11.2.0-1.0.x86_64
[root@fedora Disk1]# yum install libaio
Loaded plugins: langpacks, presto, refresh-packagekit
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package libaio.x86_64 0:0.3.109-4.fc16 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
================================================================================================================================================
 Package                         Arch                            Version                                  Repository                       Size
================================================================================================================================================
Installing:
 libaio                          x86_64                          0.3.109-4.fc16                           fedora                           21 k
Transaction Summary
================================================================================================================================================
Install       1 Package
Total download size: 21 k
Installed size: 21 k
Is this ok [y/N]: y
Downloading Packages:
libaio-0.3.109-4.fc16.x86_64.rpm                                                                                         |  21 kB     00:00
Running Transaction Check
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : libaio-0.3.109-4.fc16.x86_64                                                                                                 1/1
Installed:
  libaio.x86_64 0:0.3.109-4.fc16
Complete!
[root@fedora Disk1]# rpm -ivh oracle-xe-11.2.0-1.0.x86_64.rpm
Preparing...                ########################################### [100%]
   1:oracle-xe              ########################################### [100%]
Executing post-install steps...
sh: -c: line 0: syntax error near unexpected token `('
sh: -c: line 0: `echo ~(unknown)'
You must run '/etc/init.d/oracle-xe configure' as the root user to configure the database.
[root@fedora Disk1]# /etc/init.d/oracle-xe configure
Oracle Database 11g Express Edition Configuration
-------------------------------------------------
This will configure on-boot properties of Oracle Database 11g Express
Edition.  The following questions will determine whether the database should
be starting upon system boot, the ports it will use, and the passwords that
will be used for database accounts.  Press to accept the defaults.
Ctrl-C will abort.
Specify the HTTP port that will be used for Oracle Application Express [8080]:
Specify a port that will be used for the database listener [1521]:
Specify a password to be used for database accounts.  Note that the same
password will be used for SYS and SYSTEM.  Oracle recommends the use of
different passwords for each database account.  This can be done after
initial configuration:
Password can't be null. Enter password:
Confirm the password:
Do you want Oracle Database 11g Express Edition to be started on boot (y/n) [y]:n
Starting Oracle Net Listener...Done
Configuring database...
Done
Starting Oracle Database 11g Express Edition instance...Done
Installation completed successfully.
[root@fedora Disk1]# ps -aef | grep pmon
oracle    4166     1  0 00:39 ?        00:00:00 xe_pmon_XE

หลังจากติดตั้ง Oracle XE ก็ได้เวลาในการติดตั้ง Perl module - DBD::Oracle 

สิ่งแรกที่ต้องทำคือ download mudule จาก cpan.org หลังจากนั้น ลองมาติดตั้งกัน...

[root@fedora ~]# tar zxf DBD-Oracle-1.46.tar.gz
[root@fedora ~]# cd DBD-Oracle-1.46/
[root@fedora DBD-Oracle-1.46]# ls
Changes     examples  LICENSE      META.yml  ocitrace.h  Oraperl.pm        README.clients.txt  README.java.txt    README.win32.txt  Todo
dbdimp.c    hints     Makefile.PL  mkta.pl   Oracle.h    README            README-files        README.macosx.txt  README.win64.txt  typemap
dbdimp.h    INSTALL   MANIFEST     oci8.c    Oracle.xs   README.64bit.txt  README.help.txt     README.mkdn        t
dbivport.h  lib       META.json    oci.def   oraperl.ph  README.aix.txt    README.hpux.txt     README.sec.txt     test.pl
[root@fedora DBD-Oracle-1.46]# ls README.64bit.txt
README.64bit.txt
[root@fedora DBD-Oracle-1.46]# less  README.64bit.txt
[root@fedora DBD-Oracle-1.46]# export ORACLE_HOME=/u01/app/oracle/product/11.2.0/xe
[root@fedora DBD-Oracle-1.46]# 

[root@fedora DBD-Oracle-1.46]# perl Makefile.PL
Can't locate DBI.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at Makefile.PL line 21.
[root@fedora DBD-Oracle-1.46]# yum install perl-DBI.x86_64
[root@fedora DBD-Oracle-1.46]# perl Makefile.PL
Using DBI 1.616 (for perl 5.014002 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/
Configuring DBD::Oracle for perl 5.014002 on linux (x86_64-linux-thread-multi)
Remember to actually *READ* the README file! Especially if you have any problems.
Installing on a linux, Ver#2.6
Using Oracle in /u01/app/oracle/product/11.2.0/xe
DEFINE _SQLPLUS_RELEASE = "1102000200" (CHAR)
Oracle version 11.2.0.2 (11.2)
Found /u01/app/oracle/product/11.2.0/xe/rdbms/demo/demo_xe.mk
Using /u01/app/oracle/product/11.2.0/xe/rdbms/demo/demo_xe.mk
Looks like Oracle XE (/u01/app/oracle/product/11.2.0/xe/rdbms/demo/demo_xe.mk)
Reading /u01/app/oracle/product/11.2.0/xe/rdbms/demo/demo_xe.mk
Your LD_LIBRARY_PATH env var is set to ''
WARNING: Your LD_LIBRARY_PATH env var doesn't include '/u01/app/oracle/product/11.2.0/xe/lib' but probably needs to.
client_version=11.2

DEFINE= -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"11.2.0.2\" -DORA_OCI_102 -DORA_OCI_112

Checking for functioning wait.ph

System: perl5.014002 linux x86-17.phx2.fedoraproject.org 2.6.32-220.4.1.el6.x86_64 #1 smp thu jan 19 14:50:54 est 2012 x86_64 x86_64 x86_64 gnulinux
Compiler:   gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64
Linker:     not found
Sysliblist: -ldl -lm -lpthread -lnsl -lirc
Oracle makefiles would have used these definitions but we override them:
  CC:       /usr/bin/gcc
  LDFLAGS:  -g
           [-g]
Linking with -L/u01/app/oracle/product/11.2.0/xe/lib/ -lclntsh -lpthread
Checking if your kit is complete...
Looks good
LD_RUN_PATH=/u01/app/oracle/product/11.2.0/xe/lib
Using DBD::Oracle 1.46.
Using DBD::Oracle 1.46.
Using DBI 1.616 (for perl 5.014002 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/
Writing Makefile for DBD::Oracle
Writing MYMETA.yml
***  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?)
[root@fedora DBD-Oracle-1.46]# make
gcc -c  -I/usr/lib64/perl5/vendor_perl/auto/DBI -I/u01/app/oracle/product/11.2.0/xe/rdbms/public/ -DLINUX -D_GNU_SOURCE -D_REENTRANT -g -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic   -DVERSION=\"1.46\" -DXS_VERSION=\"1.46\" -fPIC "-I/usr/lib64/perl5/CORE"  -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"11.2.0.2\" -DORA_OCI_102 -DORA_OCI_112 Oracle.c
gcc -c  -I/usr/lib64/perl5/vendor_perl/auto/DBI -I/u01/app/oracle/product/11.2.0/xe/rdbms/public/ -DLINUX -D_GNU_SOURCE -D_REENTRANT -g -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic   -DVERSION=\"1.46\" -DXS_VERSION=\"1.46\" -fPIC "-I/usr/lib64/perl5/CORE"  -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"11.2.0.2\" -DORA_OCI_102 -DORA_OCI_112 dbdimp.c
dbdimp.c: In function ‘ora_db_login6’:
dbdimp.c:776:5: warning: format ‘%d’ expects argument of type ‘int’, but argument 12 has type ‘size_t’ [-Wformat]
dbdimp.c:776:5: warning: format ‘%d’ expects argument of type ‘int’, but argument 14 has type ‘size_t’ [-Wformat]
gcc -c  -I/usr/lib64/perl5/vendor_perl/auto/DBI -I/u01/app/oracle/product/11.2.0/xe/rdbms/public/ -DLINUX -D_GNU_SOURCE -D_REENTRANT -g -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic   -DVERSION=\"1.46\" -DXS_VERSION=\"1.46\" -fPIC "-I/usr/lib64/perl5/CORE"  -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"11.2.0.2\" -DORA_OCI_102 -DORA_OCI_112 oci8.c
oci8.c: In function ‘ora_blob_read_mb_piece’:
oci8.c:1849:4: warning: format ‘%lu’ expects argument of type ‘long unsigned int’, but argument 6 has type ‘ub4’ [-Wformat]
Running Mkbootstrap for DBD::Oracle ()
chmod 644 Oracle.bs
rm -f blib/arch/auto/DBD/Oracle/Oracle.so
gcc  -shared -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector --param=ssp-buffer-size=4 -m64 -mtune=generic Oracle.o dbdimp.o oci8.o  -o blib/arch/auto/DBD/Oracle/Oracle.so   \
   -L/u01/app/oracle/product/11.2.0/xe/lib/ -lclntsh -lpthread          \
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
Manifying blib/man3/DBD::Oracle::Troubleshooting.3pm
Manifying blib/man3/DBD::Oracle.3pm
Manifying blib/man3/DBD::Oracle::Object.3pm
Manifying blib/man3/DBD::Oraperl.3pm
Manifying blib/man3/DBD::Oracle::GetInfo.3pm
[root@fedora DBD-Oracle-1.46]# make test
[root@fedora DBD-Oracle-1.46]# make install
Files found in blib/arch: installing files in blib/lib into architecture dependent library tree
Installing /usr/local/lib64/perl5/auto/DBD/Oracle/dbdimp.h
Installing /usr/local/lib64/perl5/auto/DBD/Oracle/ocitrace.h
Installing /usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.bs
Installing /usr/local/lib64/perl5/auto/DBD/Oracle/mk.pm
Installing /usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.so
Installing /usr/local/lib64/perl5/auto/DBD/Oracle/Oracle.h
Installing /usr/local/lib64/perl5/oraperl.ph
Installing /usr/local/lib64/perl5/Oraperl.pm
Installing /usr/local/lib64/perl5/DBD/Oracle.pm
Installing /usr/local/lib64/perl5/DBD/Oracle/Object.pm
Installing /usr/local/lib64/perl5/DBD/Oracle/GetInfo.pm
Installing /usr/local/lib64/perl5/DBD/Oracle/Troubleshooting.pm
Installing /usr/local/share/man/man3/DBD::Oraperl.3pm
Installing /usr/local/share/man/man3/DBD::Oracle.3pm
Installing /usr/local/share/man/man3/DBD::Oracle::GetInfo.3pm
Installing /usr/local/share/man/man3/DBD::Oracle::Object.3pm
Installing /usr/local/share/man/man3/DBD::Oracle::Troubleshooting.3pm
Appending installation info to /usr/lib64/perl5/perllocal.pod

จากการติดตั้งจะเห็นว่าต้องการ DBI module ก่อน
เมื่อเราติดตั้ง DBI::Oracle เรียบร้อยลองมาเขียน Perl โปรแกรมติดต่อฐานข้อมูล Oracle สักนิด

[opun@fedora PERL_PRACTICE]$ cat test-connect.pl
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $result;
my $db = DBI->connect( "dbi:Oracle:XE", "demo", "demo",{AutoCommit=>0, RaiseError=>1} )
or die "Couldn't connect to database: " . DBI->errstr;
my $sth = $db->prepare("SELECT SYS_CONTEXT (\'USERENV\', \'SESSION_USER\') FROM DUAL ")
        or die "Couldn't prepare statement: " . $db->errstr;
$sth->execute()
or die "Couldn't execute statement: " . $sth->errstr;
while ( my @data = $sth->fetchrow_array() )
{
    $result = $data[0];
}
$sth->finish;
$db->disconnect;
print "Result: $result \n\n";
[opun@fedora PERL_PRACTICE]$ perl test-connect.pl
Result: DEMO

นั่นทำรู้ว่า DBI::Oracle ใช้งานได้ บทความนี้เป็นการเริ่มต้น สำหรับคนที่สนใจ Perl - DBI::Oracle module ติดต่อฐานข้อมูล Oracle