end0tknr's kipple - 新web写経開発

http://d.hatena.ne.jp/end0tknr/ から移転しました

perl DBD::Oracle for centos 7.4 によるoracle dbへの接続 - 2018年版

2013年に同様のエントリを記載していますが、 先日、試したところ、手こずったので、2018年版メモ。

https://end0tknr.hateblo.jp/entry/20131122/1385095932

oracleクライアントツール群のinstall

http://www.oracle.com/technetwork/jp/topics/linuxsoft-083388-ja.html

↑こちらからダウンロードし、rpmでinstall. この部分は、ほぼ前回同様。違う点といえば、clientのversion程度.(12.1->12.2)

$ sudo rpm -ivh oracle-instantclient12.2-basic-12.2.0.1.0-1.x86_64.rpm
$ sudo rpm -ivh oracle-instantclient12.2-devel-12.2.0.1.0-1.x86_64.rpm
$ sudo rpm -ivh oracle-instantclient12.2-sqlplus-12.2.0.1.0-1.x86_64.rpm

oracleクライアントツール用の環境変数設定

この部分も、ほぼ前回同様。違う点といえば、clientのversionと、64bitによるpath.

ちなみに sqlplus のコマンド名も64bitで、 sqlplus64 になっていました。

$ sudo vi /etc/profile.d/oracle.sh
export ORACLE_HOME='/usr/lib/oracle/12.2/client64'
export C_INCLUDE_PATH='/usr/include/oracle/12.2/client64'
export LD_LIBRARY_PATH='/usr/lib/oracle/12.2/client64/lib'

DBD::Oracle のinstall

いつものように、「perl Makefile.PL」すると、以下のようにエラー

$ curl -LO https://cpan.metacpan.org/authors/id/P/PY/PYTHIAN/DBD-Oracle-1.74.tar.gz
$ tar -xvf DBD-Oracle-1.74.tar.gz
$ cd DBD-Oracle-1.74
$ perl Makefile.PL 
Using DBI 1.641 (for perl 5.026002 on x86_64-linux-thread-multi) installed in /usr/local/lib/perl5/site_perl/5.26.2/x86_64-linux-thread-multi/auto/DBI/

Configuring DBD::Oracle for perl 5.026002 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#3.10
Using Oracle in /usr/lib/oracle/12.2/client64
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
Oracle version 12.2.0.1 (12.2)

    Unable to locate an oracle.mk or other suitable *.mk
    file in your Oracle installation.  (I looked in
    /usr/lib/oracle/12.2/client64/rdbms/demo/demo_xe.mk /usr/lib/oracle/12.2/client64/rdbms/lib/oracle.mk /usr/lib/oracle/12.2/client64/rdbms/demo/oracle.mk /usr/lib/oracle/12.2/client64/rdbms/demo/demo_rdbms.mk /usr/lib/oracle/12.2/client64/rdbms/demo/demo_rdbms64.mk /usr/lib/oracle/12.2/client64/rdbms/lib/ins_rdbms.mk /usr/share/oracle/12.2/client64/demo.mk under /usr/lib/oracle/12.2/client64)

    The oracle.mk (or demo_rdbms.mk) file is part of the Oracle
    RDBMS product.  You need to build DBD::Oracle on a
    system which has one of these Oracle components installed.
    (Other *.mk files such as the env_*.mk files will not work.)
    Alternatively you can use Oracle Instant Client.

    In the unlikely event that a suitable *.mk file is installed
    somewhere non-standard you can specify where it is using the -m option:
        perl Makefile.PL -m /path/to/your.mk

    See the appropriate README file for your OS for more information and some alternatives.

なので、「-m /usr/share/oracle/12.2/client64/demo/demo.mk」オプションを追加し、 再度「perl Makefile.PL」を実行。

※ /usr/share/oracle/12.2/client64/demo/demo.mk は、先程のrpmでinstallされます

$ perl Makefile.PL -m /usr/share/oracle/12.2/client64/demo/demo.mk
$ make
$ make test
$ sudo make install

perlによるサンプルコード

前回2013年の接続方法では、接続できなかった為、connect()で与える引数を "(DESCRIPTION=(ADDRESS_LIST=..."に変更しました。

#!/usr/local/bin/perl
use strict;
use utf8;
use DBI;        #DBD::Oracle
use DBD::Oracle qw(:ora_types);
use Data::Dumper;

my $DBH;
my $DB_CONF =>
    {id_pw =>'user_id/user_pw',
     oracle=>
     join('',
          '(DESCRIPTION=',
          '(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.61.52.???)(PORT=1521)))',
          '(CONNECT_DATA=(SERVICE_NAME=???.sexy.co.jp)(SERVER=DEDICATED)))'),
    };

# $ENV{'ORACLE_HOME'} =   '/usr/lib/oracle/12.2/client64';
# $ENV{'LD_LIBRARY_PATH'}='/usr/include/oracle/12.2/client64';
# $ENV{'C_INCLUDE_PATH'}= '/usr/lib/oracle/12.2/client64/lib';
# $ENV{'nls_lang'}="JA16SJISTILDE";
# $ENV{'nls_lang'}="JA16SJIS";
# $ENV{'nls_lang'}="JAPANESE_JAPAN.UTF8"

main();

sub main {
    $DBH = connect_db();
    chk_sql_1();
    $DBH->disconnect();
}

sub chk_sql_1 {
    my $sql =<<EOF;
select * from XXXXXX_y where C_XXXXXX=?
EOF
    my $sth = $DBH->prepare($sql);
    $sth->execute('XXXXXX');
    while( my $row = $sth->fetchrow_hashref){
        print STDERR Dumper($row);
    }
}

sub connect_db {
    my $dbh = DBI->connect('dbi:Oracle:',
                           $DB_CONF->{id_pw}.'@'.$DB_CONF->{oracle},
                           '');
    return $dbh;
}