Connect oracle database with php.md

Connect Oracle database with PHP

This solution is for those facing problems connecting the Oracle database with PHP. It is tested on Fedora 41(a Linux distribution) and the Oracle 23c database.

Step 1: Install Oracle Instant client

First, you need to install Oracle Instant Client. You can download it from here. After downloading, extract the zip file and move it to the /opt/oracle directory. You also need to download the SDK package and move it to the same directory.

Example from my machine:

> /usr/lib/oracle/23/client64/lib
> ls
fips.so           libclntshcore.so.23.1  libclntsh.so.11.1  libclntsh.so.19.1  libclntsh.so.22.1  libocci.so       libocci.so.12.1  libocci.so.20.1  libocci.so.23.1  network         pkcs11.so
legacy.so         libclntsh.so           libclntsh.so.12.1  libclntsh.so.20.1  libclntsh.so.23.1  libocci.so.10.1  libocci.so.18.1  libocci.so.21.1  libociei.so      ojdbc8.jar      xstreams.jar
libclntshcore.so  libclntsh.so.10.1      libclntsh.so.18.1  libclntsh.so.21.1  libnnz.so          libocci.so.11.1  libocci.so.19.1  libocci.so.22.1  libocijdbc23.so  ottclasses.zip

Step 2: Install PHP 8.1.31

You can download the php source code from here. After downloading the source code, extract it and navigate to the extracted directory.

You can run the following commands to download and extract the source code.

wget https://www.php.net/distributions/php-8.1.31.tar.gz
tar -xvzf php-8.1.31.tar.gz
cd php-8.1.31

You must export this path to your environment variables. To do so, add the following line to your .bashrc or .zshrc file.

export ORACLE_HOME=/usr/lib/oracle/23 #we only need to export the path of the instant client
export LD_LIBRARY_PATH=$ORACLE_HOME:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME:$PATH
export TNS_ADMIN=$ORACLE_HOME
export LD_LIBRARY_PATH=/usr/lib/oracle/23/client64/lib

After adding the above lines to your .bashrc or .zshrc file, you need to source the file.

source ~/.bashrc

Now, you can run the following.

./configure --with-pdo-oci=instantclient,/usr/lib/oracle/23/client64/lib
make
sudo make install

Step 3: run oracle database in docker

install docker in your machine.

add this file to your project directory as docker-compose.yml

services:
  db:
    image: mysql:latest
    container_name: project-1
    environment:
      MYSQL_ROOT_PASSWORD: rootpassword
      MYSQL_DATABASE: mydatabase
      MYSQL_USER: user
      MYSQL_PASSWORD: userpassword
    ports:
      - "3306:3306"
    volumes:
      - db_data:/var/lib/mysql

volumes:
  db_data:

run this command in your project directory

docker-compose up -d

Step 4: Connect to the database

Now, you can connect to the oracle database using the following code.

<?php
// Oracle database connection details
$host = "127.0.0.1";
$port = "1521";
$sid = "FREE";
$username = 'SYSTEM';
$password = 'mypassword123';

// Connection string
$dsn = "oci:dbname=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=$host)(PORT=$port))(CONNECT_DATA=(SID=$sid)))";

// check pdo_oci extension
if (!extension_loaded('pdo_oci')) {
    die('pdo_oci extension is not loaded');
}

try {
    // Connect to the Oracle database using PDO
    $conn = new PDO($dsn, $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected to Oracle database successfully!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

// Close the connection
$conn = null;