How to Connect Laravel with Oracle

This tutorial provides a step-by-step guide on how to establish a connection between your Laravel application and an Oracle database. Connecting Laravel to Oracle can be a bit tricky, but by following these steps, you should be able to configure your application successfully. This guide assumes you have a basic understanding of both Laravel and Oracle.

Step 1: Install the Oracle Instant Client

The first step is to install the Oracle Instant Client on your system. This provides the necessary libraries for PHP to connect to your Oracle database. Download the appropriate version (Basic Package and SDK Package) from the Oracle website based on your operating system and architecture. Make sure you download the same version for both packages.

After downloading, extract the contents of both zip files into a single directory. For example, you might create a directory called /opt/oracle/instantclient and extract the files there.

Set the LD_LIBRARY_PATH environment variable to point to the Instant Client directory. This allows PHP to find the Oracle libraries.

export LD_LIBRARY_PATH=/opt/oracle/instantclient:$LD_LIBRARY_PATH

You can add this line to your .bashrc or .zshrc file to make it permanent.

Step 2: Install the Oracle PDO Driver for PHP

Next, you need to install the PDO_OCI driver for PHP. This driver allows PHP to interact with Oracle databases using the PDO (PHP Data Objects) extension.

Important: Before proceeding, verify that your PHP installation has the PDO extension enabled. You can check this by creating a simple PHP file (e.g., info.php) with the following code:

<?php
phpinfo();
?>

Open this file in your web browser, and search for "PDO". If PDO is enabled, you should see a section for it. If not, you need to enable it in your php.ini file. Uncomment the line extension=pdo.so (or the appropriate extension for your system) and restart your web server.

Once you have confirmed that PDO is enabled, you can proceed with installing the Oracle PDO driver. There are several ways to install the PDO_OCI extension. The most common is using PECL:

pecl install oci8

The PECL installer might ask you for the path to your Oracle Instant Client. Provide the directory you created in Step 1 (e.g., /opt/oracle/instantclient).

After the installation, you'll need to enable the extension in your php.ini file. Add or uncomment the following line:

extension=oci8.so

Finally, restart your web server to apply the changes.

Step 3: Configure Laravel's Database Connection

Now, it's time to configure your Laravel application to connect to the Oracle database. Open your .env file and update the database connection settings.

DB_CONNECTION=oracle
DB_HOST=your_oracle_host
DB_PORT=1521
DB_DATABASE=your_oracle_database_service_name
DB_USERNAME=your_oracle_username
DB_PASSWORD=your_oracle_password
DB_CHARSET=AL32UTF8
DB_PREFIX=
DB_SERVER_VERSION=11g

Replace the placeholder values with your actual Oracle database credentials and settings.

DB_HOST: The hostname or IP address of your Oracle server. DB_PORT: The port number Oracle is listening on (usually 1521). DB_DATABASE: This should be your Oracle service name (also known as SID). You can find this in your tnsnames.ora file or from your DBA. DB_USERNAME: Your Oracle username. DB_PASSWORD: Your Oracle password. DB_CHARSET: Recommended to be AL32UTF8 to support unicode characters. DB_SERVER_VERSION: Specify the major version of your Oracle server.

Step 4: Configure database.php

Edit your config/database.php file and configure the oracle connection.

'oracle' => [
    'driver'         => 'oracle',
    'tns'            => env('DB_TNS', ''), // Can be used if you prefer TNS names.
    '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', ''),
    'server_version' => env('DB_SERVER_VERSION', '11g'),
],

This configuration reads the database settings from the environment variables you defined in Step 3. You can also hardcode the values directly in this file if you prefer, but using environment variables is generally recommended for security and flexibility.

Step 5: Test the Connection

Finally, test the connection to ensure that everything is working correctly. You can use Laravel Tinker to execute a simple database query.

php artisan tinker

In the Tinker shell, execute the following command:

DB::connection('oracle')->select('select 1 from dual');

If the connection is successful, you should see the result [{"1":"1"}] or similar. If you encounter any errors, review the previous steps and ensure that all configurations are correct.

By following these steps, you should now have a working connection between your Laravel application and your Oracle database. Remember to consult the Laravel documentation and the Oracle Instant Client documentation for more detailed information and troubleshooting tips. Good luck!