Oracle Instant Client libraries installation

Download Oracle Instant Client libraries (URL may change, already happened few times). Be sure you download correct (x64 or x32) architecture. And correct version. I'm using Instant Client 10.1.0.5.0 with Oracle 10g, 11g and 12c and PHP 5.6, 7.1, 7.2, 7.3 and 7.4. Never hit any problem with such setup but my queries are quite simple.

Last time I used Instant Client 19.6.0.0.0 and compiled with PHP 7.4 fine. I had to download two files:

instantclient-basic-linux.x64-19.6.0.0.0dbru.zip
instantclient-sdk-linux.x64-19.6.0.0.0dbru.zip

and extacted them into single directory. I'm using /usr/local/lib/oracle. File tree follows:

/usr/local/lib/oracle/
    /instantclient_19_6/
        /network/
        /sdk/
        /libclntsh.so -> libclntsh.so.19.1
        /libclntsh.so.10.1 -> libclntsh.so.19.1
        /libclntsh.so.11.1 -> libclntsh.so.19.1
        /libclntsh.so.12.1 -> libclntsh.so.19.1
        /libclntsh.so.18.1 -> libclntsh.so.19.1
        /libclntsh.so.19.1
        /libnnz10.so
        /...

In older versions, I had to create a libclntsh.so symlink to libclntsh.so.10.1. Not needed now.

Path to Instant Client libraries will be used for PHP extension compilation. So create symlink to drop version information from path.

cd /usr/local/lib/oracle
ln -s instantclient_19_6 instantclient

Ensure, that access to libraries is allowed for web server/PHP FPM user. Otherwise applications will fail with a strange NULL-message errors. I allow read for anyone:

cd /usr/local/lib/oracle
find instantclient_19_6 -type f -exec chmod 644 {} +
find instantclient_19_6 -type d -exec chmod 755 {} +

PHP OCI8 module compilation and installation

As a PHP packages repository I prefer Ondřej Surý's one (deb.sury.org).

# Install php-dev tools
apt-get install php7.4-dev

cd /tmp

# Download OCI8 extension sources
wget https://pecl.php.net/get/oci8-2.2.0.tgz  # 2.1.8 for PHP <7.3
tar xzf oci8-2.2.0.tgz
cd oci8-2.2.0

# Compile extension
phpize7.4
./configure --with-oci8=instantclient,/usr/local/lib/oracle/instantclient --with-php-config=/usr/bin/php-config7.4
make

# Install extension
make install  # copy osi8.so into /usr/lib/php/20190902/
chmod 644 /usr/lib/php/20190902/oci8.so  # probably not needed, depends on your umask

# Configure PHP (just update php.ini in any way you perefer to load extension)
echo '; priority=10' > /etc/php/7.4/mods-available/oci8.ini
echo 'extension=oci8.so' >> /etc/php/7.4/mods-available/oci8.ini
chmod 644 /etc/php/7.4/mods-available/oci8.ini
phpenmod oci8

DTrace support

If you want to compile OCI8 extension with DTrace support, you have to:

apt-get install systemtap-sdt-dev
export PHP_DTRACE=yes
# and ./configure ...

Hint system where to find libs

When you run php -v or php -m now, you probably get warning about missing libraries. If not, you are lucky one. If so, you can set environment variable export LD_LIBRARY_PATH=/usr/local/lib/oracle/instantclient and try it again. I hope it helped. If so, you can set this variable in Apache/Nginx startup scripts, in your .bashrc files or whatever environment the PHP will run. Or, and that's the way I prefer, update dynamic linker configuration. Create file /etc/ld.so.conf.d/zz_php_oci8.conf:

# Oracle libs for PHP OCI8 extension
/usr/local/lib/oracle/instantclient

and refresh configuration by executing ldconfig.

Environmental variables

Besides LD_LIBRARY_PATH there are more variables you can adjust:

ORACLE_HOME - whole Oracle installation path, don't use with instant client libs
ORACLE_SID - don't use it, usable only with PHP and Oracle on same machine, but you can always pass connection string to oci_connect()
NLS_LANG - National Language Support, can be set by putenv('NLS_LANG=CZECH.CZECH REPUBLIC.UTF8')
NLS_NUMERIC_CHARACTERS and NLS_DATE_FORMAT - NLS adjustments, ignored when NLS_LANG envvar not set, can be set by query
TNS_ADMIN - path to tnsnames.ora and sqlnet.ora config files if you use configuration names in oci_connect()

Handy queries

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH:MI:SS.FF'
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '. '