Connecting to External Oracle Database with Symfony 4 Framework

Introduction

I frequently use the Symfony Framework to develop PHP applications and I have recently started developing using the latest Symfony 4 Framework. The Symfony Developers have significantly simplified the configuration and setup required for the framework. This article is about the setup of connecting to an external Oracle database using a Doctrine connection with the OCI8 driver.

My Setup

In my particular setup, I have a CentOS 6 (Linux) Virtual Machine host which has the Symfony 4 Framework and application that I have developed; and this connects via OCI8 to an external Oracle 12c database that has our ERP data that I need to query. The below diagram shows a rough picture of what this looks like so you have a visual impression of the setup.

Before you will be able to connect via OCI8, you will need to install OCI8 on your system. This can be difficult, and you can take a look at my previous article Installing OCI8 on RHEL for some guidelines.

Installing Doctrine

If you look at the current Symfony Doctrine documentation, you will need to install Doctrine, but not the “Maker” bundle, which is used for generating Entity classes.

The following command will install Doctrine:

composer require doctrine

After running this command, Doctrine is now available in your project.

Configuring OCI8 Database Connection

The Symfony 4 configuration has been simplified, and you merely have to enter and modify a DATABASE_URL to specify how you connect to the database you will use.

The steps required to add the DATABASE_URL are:

1. Browse to the root folder of your Symfony 4 project.
2. Edit the .env (hidden file) located in the root folder.
3. After installing Doctrine, it should add a DATABASE_URL, but if it does not exist, just add one.

Save your .env file after making changes.

Format of DATABASE_URL

All connection information is specified in the DATABASE_URL. Here is the format:

DATABASE_URL=[driver]://[user]:[pass]@[host]:[port]/[SID]

So for example, if you are connecting to an external Oracle database with OCI8, then you might use:

DATABASE_URL=oci8://abunk:mypass@192.168.1.1:1521/mysid

Where in the above “oci8” is the OCI8 driver, “abunk” is my username, “mypass” is my password for the Oracle schema, “192.168.1.1” is the IP address of the Oracle database host, “1521” is the standard Oracle TCP port to use, and “mysid” is the Oracle System ID.

Using Connection

Then to use the Doctrine database connection, you would use it in your Symfony 4 Controller, something like this:

<?php
// src/Controller/DefaultController.php
...
use Doctrine\DBAL\Driver\Connection;
...
class DefaultController extends Controller{
     /**
      * @Route("/myroute", name="myroute")
      */    public function showQuery(Request $request, Connection $conn){
                ...
                $group = $conn->fetchAssoc(
                   'SELECT CUST_NAME FROM CUSTOMER WHERE CUST_ID=290'
               );
               ...