Oracle instantclient + ODBC on Debian GNU/Linux

I have finally managed to properly install Oracle's instantclient and ODBC drivers on my Debian GNU/Linux box. Here is my mini HOWTO. Lines that start with # are comments.

# Change to root (as usual)

su -

# Go to link bellow and register

http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html

# Download

Instant Client Package - Basic: All files required to run OCI, OCCI, and
JDBC-OCI applications

oracle-instantclient-basic-10.2.0.3-1.i386.rpm

Instant Client Package - SDK: Additional header files and an example makefile for developing Oracle applications with Instant Client

oracle-instantclient-devel-10.2.0.3-1.i386.rpm

Instant Client Package - JDBC Supplement: Additional support for XA,
Internationalization, and RowSet operations under JDBC

oracle-instantclient-jdbc-10.2.0.3-1.i386.rpm

Instant Client Package - SQL*Plus: Additional libraries and executable for
running SQL*Plus with Instant Client

oracle-instantclient-sqlplus-10.2.0.3-1.i386.rpm

Instant Client Package - ODBC: Additional libraries for enabling ODBC applications

instantclient-odbc-linux32-10.2.0.3-20061115.zip

# Convert rpm to deb

alien oracle-instantclient-*

# Install

dpkg -i oracle-instantclient-*deb

# Remove deb and rpm files

rm -f oracle-instantclient-*rpm oracle-instantclient-*deb

# Installed files are

dpkg -L oracle-instantclient-basic
/usr
/usr/share
/usr/share/doc
/usr/share/doc/oracle-instantclient-basic
/usr/share/doc/oracle-instantclient-basic/copyright
/usr/share/doc/oracle-instantclient-basic/changelog.Debian.gz
/usr/lib
/usr/lib/oracle
/usr/lib/oracle/10.2.0.3
/usr/lib/oracle/10.2.0.3/client
/usr/lib/oracle/10.2.0.3/client/bin
/usr/lib/oracle/10.2.0.3/client/bin/genezi
/usr/lib/oracle/10.2.0.3/client/lib
/usr/lib/oracle/10.2.0.3/client/lib/libclntsh.so.10.1
/usr/lib/oracle/10.2.0.3/client/lib/libnnz10.so
/usr/lib/oracle/10.2.0.3/client/lib/libocci.so.10.1
/usr/lib/oracle/10.2.0.3/client/lib/libociei.so
/usr/lib/oracle/10.2.0.3/client/lib/libocijdbc10.so
/usr/lib/oracle/10.2.0.3/client/lib/ojdbc14.jar

dpkg -L oracle-instantclient-devel
/usr
/usr/share
/usr/share/doc
/usr/share/doc/oracle-instantclient-devel
/usr/share/doc/oracle-instantclient-devel/copyright
/usr/share/doc/oracle-instantclient-devel/changelog.Debian.gz
/usr/share/oracle
/usr/share/oracle/10.2.0.3
/usr/share/oracle/10.2.0.3/client
/usr/share/oracle/10.2.0.3/client/cdemo81.c
/usr/share/oracle/10.2.0.3/client/demo.mk
/usr/share/oracle/10.2.0.3/client/occidemo.sql
/usr/share/oracle/10.2.0.3/client/occidemod.sql
/usr/share/oracle/10.2.0.3/client/occidml.cpp
/usr/include
/usr/include/oracle
/usr/include/oracle/10.2.0.3
/usr/include/oracle/10.2.0.3/client
/usr/include/oracle/10.2.0.3/client/nzerror.h
/usr/include/oracle/10.2.0.3/client/nzt.h
/usr/include/oracle/10.2.0.3/client/occi.h
/usr/include/oracle/10.2.0.3/client/occiAQ.h
/usr/include/oracle/10.2.0.3/client/occiCommon.h
/usr/include/oracle/10.2.0.3/client/occiControl.h
/usr/include/oracle/10.2.0.3/client/occiData.h
/usr/include/oracle/10.2.0.3/client/occiObjects.h
/usr/include/oracle/10.2.0.3/client/oci.h
/usr/include/oracle/10.2.0.3/client/oci1.h
/usr/include/oracle/10.2.0.3/client/oci8dp.h
/usr/include/oracle/10.2.0.3/client/ociap.h
/usr/include/oracle/10.2.0.3/client/ociapr.h
/usr/include/oracle/10.2.0.3/client/ocidef.h
/usr/include/oracle/10.2.0.3/client/ocidem.h
/usr/include/oracle/10.2.0.3/client/ocidfn.h
/usr/include/oracle/10.2.0.3/client/ociextp.h
/usr/include/oracle/10.2.0.3/client/ocikpr.h
/usr/include/oracle/10.2.0.3/client/ocixmldb.h
/usr/include/oracle/10.2.0.3/client/odci.h
/usr/include/oracle/10.2.0.3/client/oratypes.h
/usr/include/oracle/10.2.0.3/client/ori.h
/usr/include/oracle/10.2.0.3/client/orid.h
/usr/include/oracle/10.2.0.3/client/orl.h
/usr/include/oracle/10.2.0.3/client/oro.h
/usr/include/oracle/10.2.0.3/client/ort.h
/usr/include/oracle/10.2.0.3/client/xa.h
/usr/lib
/usr/lib/oracle
/usr/lib/oracle/10.2.0.3
/usr/lib/oracle/10.2.0.3/client
/usr/lib/oracle/10.2.0.3/client/lib
/usr/lib/oracle/10.2.0.3/client/lib/libclntsh.so
/usr/lib/oracle/10.2.0.3/client/lib/libocci.so

dpkg -L oracle-instantclient-jdbc
/.
/usr
/usr/share
/usr/share/doc
/usr/share/doc/oracle-instantclient-jdbc
/usr/share/doc/oracle-instantclient-jdbc/copyright
/usr/share/doc/oracle-instantclient-jdbc/changelog.Debian.gz
/usr/lib
/usr/lib/oracle
/usr/lib/oracle/10.2.0.3
/usr/lib/oracle/10.2.0.3/client
/usr/lib/oracle/10.2.0.3/client/lib
/usr/lib/oracle/10.2.0.3/client/lib/libheteroxa10.so
/usr/lib/oracle/10.2.0.3/client/lib/orai18n.jar

dpkg -L oracle-instantclient-sqlplus
/.
/usr
/usr/share
/usr/share/doc
/usr/share/doc/oracle-instantclient-sqlplus
/usr/share/doc/oracle-instantclient-sqlplus/copyright
/usr/share/doc/oracle-instantclient-sqlplus/changelog.Debian.gz
/usr/bin
/usr/lib
/usr/lib/oracle
/usr/lib/oracle/10.2.0.3
/usr/lib/oracle/10.2.0.3/client
/usr/lib/oracle/10.2.0.3/client/bin
/usr/lib/oracle/10.2.0.3/client/bin/sqlplus
/usr/lib/oracle/10.2.0.3/client/lib
/usr/lib/oracle/10.2.0.3/client/lib/glogin.sql
/usr/lib/oracle/10.2.0.3/client/lib/libsqlplus.so
/usr/lib/oracle/10.2.0.3/client/lib/libsqlplusic.so
/usr/bin/sqlplus

# Configuration

# Create a general link so that env. variables bellow can be more general
# between different versions

cd /usr/lib/oracle/
ln -s 10.2.0.3/ default

# Add the following lines in '/etc/profile' so that all users will benefit

echo 'export SQLPATH=/usr/lib/oracle/default/client/lib' >> /etc/profile
echo 'export LD_LIBRARY_PATH="$LD_LIBRARY_PATH:$SQLPATH"' >> /etc/profile

# LD_LIBRARY_PATH can be clobbered by unknown system to me and it is
# probably nicer to add library location via the following way, which will
# work out of the box for all users.

echo '/usr/lib/oracle/default/client/lib' >> /etc/ld.so.conf
ldconfig

# Set TNS_ADMIN, which should point to map with 'TNSNAMES.ORA' file. Hmm,
# where do you get that file? You should ask database administrator. I got
# it from him and put it via commands bellow. Additionally, I also needed
# file 'SQLNET.ORA' in same place!

cd /usr/lib/oracle/
mkdir -p network/admin
mv TNSNAMES.ORA SQLNET.ORA /usr/lib/oracle/network/admin/.
## keep in uppercase !!!!
echo 'export TNS_ADMIN=/usr/lib/oracle/network/admin' >> /etc/profile

# Login to database with

sqlplus username/password@//machineName:port/databaseName

# Additionally, ODBC drivers where not available as RPM package so I need
# to do installation by hand.

# Unpack
unzip instantclient-odbc-linux32-10.2.0.3-20061115.zip
Archive: instantclient-odbc-linux32-10.2.0.3-20061115.zip
inflating: instantclient_10_2/ODBCRelnotesJA.htm
inflating: instantclient_10_2/ODBCRelnotesUS.htm
inflating: instantclient_10_2/ODBC_IC_Readme_Linux.html
inflating: instantclient_10_2/libsqora.so.10.1
inflating: instantclient_10_2/odbc_update_ini.sh

# Move files to proper place
mv instantclient_10_2/* /usr/lib/oracle/default/client/lib/.
cd /usr/lib/oracle/default/client/lib/.

# Create link - just to be sure
ln -s libsqora.so.10.1 libsqora.so

# We need system ODBC stuff
aptitude install odbcinst1

# Issue ODBC configuration via available shell script. I had a problem to
# understandwhat and where is driver manager directory. Looking in install
# script revealed that this is a root directory, since I should pass
# something like $DM_HOME/etc/odbc.ini. Since 'odbc.ini' is in '/etc',
# driver manager home is just '/'.
chmod a+x odbc_update_ini.sh
./odbc_update_ini.sh /

# Now let's take a look
cat /etc/odbcinst.ini

[Oracle 10g ODBC driver]
Description = Oracle ODBC driver for Oracle 10g
Driver = /usr/lib/oracle/10.2.0.3/client/lib/libsqora.so.10.1
Setup =
FileUsage =
CPTimeout =
CPReuse =

cat $HOME/.odbc.ini

[_yourDSN_]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle 10g ODBC driver
DSN = OracleODBC-10g
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = _yourServerName_
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = _yourUserID_

# Exit root account
exit

# Copy roots' '.odbc.ini' to users home and modify it that it matches to your
# database.
cat /root/.odbc.ini >> .odbc.ini

# You can play with graphical tools

DataManager &
DataManagerII &
OBCDConfig & # I got crash here when I wanted to connect to database

# Or with command line

isql -v _yourDSN_ _yourUserName_ _yourPassword_

# Now slick stuff with RODBC

su - # again as root
aptitude install r-cran-rodbc
exit

# Start R

library(RODBC)

channel <- odbcConnect(dsn=_yourDSN_, uid=_yourUserID_, pwd=_yourPassword_,
case="oracle", believeNRows=FALSE)
odbcGetInfo(channel)

# Simple query
query <- "query1;"
zival <- sqlQuery(channel, query)

# Or a set of queries, where sprintf can be nicely used

query <- c(
"query1;",
sprintf("CREATE TABLE tmp AS
SELECT bla1,
bla2,
FROM tableX
WHERE bla3 = '%s';", x),
"query3;")

sapply(query, sqlQuery, channel=channel)