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?

We’ll take a look at Oracle first so you can better understand how Laravel and Oracle 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.

In 1977, a young entrepreneur named Larry Ellison used $2000 of his own money to start a company with Bob Miner and Ed Oats and produced the first commercially available relational database management system.

The system was compatible with IBM’s System R. The company shipped the product in 1979 under the name “Oracle”

Oracle Database is not supported out of the box by Laravel but we can 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 package, also known as yajra oci8, and we have Laravel working with Oracle Database.

This article will show you the Oracle Laravel connection steps, and how to do it yourself. It may seem challenging but by following these instructions you’ll be able to successfully connect Laravel with the Oracle database.

Table of Contents

• Installing Platform Dependencies
• Setting Up a Laravel Project With Oracle Database
• Oracle User Model
• Conclusion

Installing Platform Dependencies

You may think installing the OCI8 package from PECL would be enough, but creating a Laravel Oracle database connection is a little more complex. 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, or Oracle Linux. 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.

Download the Instant Client package here. Choose 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 a different directory.

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. 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, 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: sudo apt install php7.4-dev php7.4-pear build-essential libaio1

After installing all necessary packages, execute the following command to install the 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 simply execute pecl install oci8 but in that case, you’ll have to write the instantclient,/opt/oracle/instantclient_21_4 line manually when asked. One thing 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 the Ctrl + W key combination. Once you’ve found the file you’ll see a list of extensions. 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 will allow you to customize a lot of stuff. Enter the 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 so you can 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 did with other RDBMS. One thing 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,
],
]

This should allow you to use Laravel authentication as usual.

Conclusion

I would like to thank Laravel developers for the time spent reading this article about how to use Laravel with Oracle. I hope you’ve enjoyed the tutorial and have learned some valuable information. The yajra/laravel-oci8 package has excellent documentation of its own. I suggest that you go through their documentation before starting any serious project using 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.

FAQs

Q: Does PHP support Oracle Database?

PHP supports Oracle Database through the OCI8 PHP extension.

Q: Is Oracle a good relational database?

Oracle is the first commercially available RDBMS and is trusted by large enterprises.

Q: What is Oracle Database best for?

Oracle works in any use case where a relational database is a good fit, if you have the budget for running an Oracle Database system.

Q: What are the applications of Oracle Database?

Oracle’s applications are the same as any other relational database management system, such as MySQL or PostgreSQL.