The purpose of this post is to explain how to make a local connection using the DBD::Oracle Perl CPAN module which is linked with an Oracle instant client.
With this method it is possible to connect to multiple databases of different versions within the same program by using the oracle os authentication. This feature can be used for building utilities which perform a same task on several databases of different Oracle versions running on the same server.
The prerequisites are as follows:
- The Instant client is installed under the following path: /u00/oracle/orabase/product/instantclient_12.1.0.1
- DBD::Oracle was compiled, dynamically linked with the perl under the following path: /u00/oracle/orabase/local/perl
- The Oracle home is installed under the following path: /u00/oracle/orabase/product/12.1.0.2.3_a
- The database DNOV1 is created under the Oracle home above with the characterset AL32UTF8.
Create the program named ConnectDB.pl:
#!/u00/oracle/orabase/local/perl/bin/perl -w
use strict ;
use DBD::Oracle qw( :ora_session_modes :ora_types ) ;
$ENV{ORACLE_SID} = 'DNOV1' ;
$ENV{NLS_LANG} = 'AMERICAN_AMERICA.AL32UTF8';
$ENV{ORACLE_HOME} = '/u00/oracle/orabase/product/12.1.0.2.3_a' ;
my $dbh = DBI->connect(
'dbi:Oracle:' , '' , '' , { ora_session_mode => ORA_SYSDBA }
) ;
Prior to invoking perl, the following environment variables should be set:
export LD_LIBRARY_PATH=/u00/oracle/orabase/product/instantclient_12.1.0.1 export PERL5LIB=/u00/oracle/orabase/local/perl/lib/5.12.2
Program call:
/u00/oracle/orabase/local/perl/bin/perl ConnectDB.pl
In the example above we made a sysdba connection to the database. However, if you do not want to connect as sysdba just create the OS authenticated account in the database and omit the connection attribute ora_session_mode.
