How To Use Laravel With Oracle Database

Oracle Database is one of the most reliable database management systems, while Laravel is currently the most popular PHP framework. So, the question is, can we create a Laravel oracle database connection, and how would it work?

But, before going any further, we will introduce Oracle better, so you can understand how Laravel and oracle can work together.

Relational databases have been around since the 70s. Following the ideas of Ted Codd, a computer scientist at IBM, an experimental relational database management system called System R was released in 1975 by IBM themselves.

Later on, a young entrepreneur named Larry Ellison put up $2000 of his own money to start a company with Bob Miner and Ed Oats to produce the first commercially available relational database management system.

The system was compatible with IBM's System R. The company shipped the product in 1979 titled "Oracle." So you can call Oracle Database the OG relational database management system. Now, as influential as it may be, Oracle Database is not supported out of the box by Laravel.

However, like a lot of things that are not supported out of the box, we can actually make Laravel work with Oracle Database. PHP has had support for Oracle Database since 2003 through the OCI8 PHP extension. Pair that with the yajra/laravel-oci8 (also known as yajra oci8) package, and boom, we have Laravel working with Oracle Database.

Stay with me to learn more about Oracle Laravel connection steps and how to do it yourself. It seems challenging, but if you follow my instructions, you can successfully connect Laravel with the Oracle database.

Installing Platform Dependencies

If you’ve read my article on using Laravel with MongoDB, you may think that installing the OCI8 package from PECL will be enough. But sadly, it’s a bit more complicated than that to create a Laravel oracle database connection. The OCI8 PHP extension needs to be built from the source, and if your system is not properly configured, you’ll face a lot of hurdles.

Before I begin, I would like to clarify that I’ll be using Ubuntu throughout the entire article. You may use something else, such as Fedora, CentOS, Oracle Linux, etc. The first thing that you’ll have to install is the Oracle Instant Client. It enables the development and deployment of applications that connect to Oracle Database, either on-premise or in the Cloud.

To download the Instant Client package, head over here and download the Basic Package (ZIP):

Do not download the RPMs even if you’re on an RPM-based system. Apart from this basic package, you’ll also need the SDK package. Scroll down on the download page and locate the SDK Package (ZIP):

Once you’ve downloaded both files, open a terminal window and move both files to the /opt/oracle directory:

mkdir -p /opt/oracle
mv instantclient-basic-linux.x64-21.4.0.0.0dbru.zip /opt/oracle/.
mv instantclient-sdk-linux.x64-21.4.0.0.0dbru.zip /opt/oracle/.

Keep in mind that the version number may be different in your case. Also, the /opt/oracle directory is not mandatory. You can choose some other directory if you want.

Next, cd into the /opt/oracle directory and extract the archives:

cd /opt/oracle
unzip instantclient-basic-linux.x64-21.4.0.0.0dbru.zip
unzip instantclient-sdk-linux.x64-21.4.0.0.0dbru.zip

Now you’ll have to update the ldconfig configuration so that the C++ compiler can find these shared libraries. To do so, first, add the instant client directory to the configuration file:

echo /opt/oracle/instantclient_21_4 > /etc/ld.so.conf.d/oracle-instantclient.conf

This command saves the line /opt/oracle/instantclient_21_4 into the /etc/ld.so.conf.d/oracle-instantclient.conf file.

Next, run the following command:

ldconfig

This will restart ldconfig and create necessary links to the newly installed shared libraries. Now you’re ready to build and install the OCI8 PHP extension from PECL. Before that, you’ll need to install some packages. To do so, execute the following command:

# ubuntu
sudo apt install php-dev php-pear build-essential libaio1

# fedora/centos
sudo dnf groupinstall "Development Tools" && sudo dnf install php-devel php-pear libaio

# CentOS
sudo yum groups install "Development Tools" && sudo yum install php-devel php-pear libaio

If you’re on a different system than these two, use something like https://pkgs.org to locate the appropriate packages. Also, if you have multiple PHP versions installed, make sure to specify the version in the package names like so: sudo apt install php7.4-dev php7.4-pear build-essential libaio1

After installing all these necessary packages, execute the following command to install the actual OCI8 package:

echo "instantclient,/opt/oracle/instantclient_21_4" | pecl install oci8

During the installation of the laravel oci extension, PECL will ask you where you’ve installed the shared library. That’s why I’ve piped the location to the install command. You can execute simply pecl install oci8 but in that case, you’ll have to write the instantclient,/opt/oracle/instantclient_21_4 line manually when asked for. One thing that you’ll have to keep in mind is that there are different versions of this extension for the different PHP versions:

• Use pecl install oci8 to install for PHP 8.1
• Use pecl install oci8-3.0.1 to install for PHP 8.0
• Use pecl install oci8-2.2.0 to install for PHP 7
• Use pecl install oci8-2.0.12 to install for PHP 5.2 - PHP 5.6
• Use pecl install oci8-1.4.10 to install for PHP 4.3.9 - PHP 5.1

After you’ve successfully installed the extension, you’ll have to enable it. To do so, open your php.ini file using nano text editor:

# PHP CLI
sudo nano /etc/php/7.4/cli/php.ini

# PHP FPM
sudo nano /etc/php/7.4/fpm/php.ini

Scroll down until you reach the Dynamic Extensions section. You can also do a forward search in nano by pressing Ctrl + W key combination. Once you've found the desired portion of the file, you'll see a list of extensions. You'll have to add the following line of code to activate the OCI8 extension:

extension=oci8

You can add this line anywhere in the Dynamic Extensions section. Save the file by hitting Ctrl + S and exit the text editor by hitting the Ctrl + X key combination. In the case of CLI execute php -i and check if the OCI8 extension is active or not. In the case of FPM, use the following PHP script to verify the installation:

<?php

phpinfo();

This script will output information about PHP's configuration. Check and make sure the OCI8 extension is active.

Setting-Up a Laravel Project With Oracle Database

Now that the extension is installed and activated, the next step is to create a new Laravel project and use the yajra/laravel-oci8 package in it. Start by bootstrapping a new Laravel project:

laravel new oracle-demo

Then cd into the project folder and install the yajra/laravel-oci8 package into the project:

composer require yajra/laravel-oci8

Now open the config/app.php file and add the following line inside the providers array:

'providers' => [
// ...
Yajra\Oci8\Oci8ServiceProvider::class,
],

Next, publish the configuration files by executing the following command:

php artisan vendor:publish --tag=oracle

Publishing the configuration file is optional but it’ll allow you to customize a lot of stuff. Go inside config/database.php file and locate the oracle array:

'oracle' => [
'driver'    => 'oracle',
'tns'       => env('DB_TNS', ''),
'host'      => env('DB_HOST', ''),
'port'      => env('DB_PORT', '1521'),
'database'  => env('DB_DATABASE', ''),
'username'  => env('DB_USERNAME', ''),
'password'  => env('DB_PASSWORD', ''),
'charset'   => env('DB_CHARSET', 'AL32UTF8'),
'prefix'    => env('DB_PREFIX', ''),
'prefix_schema' => env('DB_SCHEMA_PREFIX', ''),
'edition'   => env('DB_EDITION', 'ora$base'),
],

These are the parameters you’ll need to connect Laravel to Oracle Database. That’s pretty much what you need to use Oracle Database with your Laravel projects.

Oracle User Model

After configuring your project to use Laravel with Oracle Database, you should be able to use Eloquent and Query Builder just as you used to do with other RDBMS. One thing that you need to keep in mind is that Oracle queries are case-sensitive by default. As a result, you may face some issues while authenticating users.

That’s why the package comes with a custom user provider. Open config/auth.php file and make the following updates:

'providers' => [
'users' => [
    'driver' => 'oracle',
    'model' => App\User::class,
],
]

Now you should be able to use Laravel authentication as usual.

Conclusion

I would like to thank all Laravel developers for the time they've spent reading this article on how to use Laravel with Oracle. I hope you've enjoyed the Laravel Oracle tutorial and have learned some valuable stuff from this article. The yajra/laravel-oci8 package has excellent documentation of its own. I would highly suggest that you go through their documentation before starting any serious project that uses this package.

If you have any questions or confusion about how to use Oracle with Laravel, feel free to reach out to me. I'm available on Twitter and LinkedIn and always happy to help. Till the next one, stay safe and keep on learning.