Laravel 與 Oracle 資料庫建立連線

Laravel 原生並沒有對 Oracle Databese 支援，這次剛好在公司一項專案上有需要連線 Oracle Database 的需求，而 laravel-oci8 是一款為 Laravel 開發、支援 Oracle Database 的套件。安裝這款套件需依賴 php-oci，因此特別寫篇文章記錄一下配置的流程。

本篇紀錄的作業環境如下：

OS: macOS Monterey

執行環境：PHP 8.0、Laravel 8.0

資料庫版本：Oracle Database 12c

安裝Oracle Instantclient Client

1. 請根據您的 Oracle Database 版本代號，先安装對應的客户端 Instantclient，下載點：Oracle Instant Client
2. 請務必下載以下三個壓縮檔

instantclient-basic-macos.x64-12.2.0.1.0-2.zip 
instantclient-sqlplus-macos.x64-12.2.0.1.0-2.zip
instantclient-sdk-macos.x64-12.2.0.1.0-2.zip

3. 將三個壓縮檔解壓縮到同一個目錄底下

$ sudo unzip instantclient-basic-macos.x64-12.2.0.1.0-2.zip -d /usr/local/
$ sudo unzip instantclient-sdk-macos.x64-12.2.0.1.0-2.zip -d /usr/local/
$ sudo unzip instantclient-sqlplus-macos.x64-12.2.0.1.0-2.zip -d /usr/local/

上述的檔案會解壓縮到 /usr/local/instantclient_12_2 目錄底下

4. 將檔案個別建立軟連結

$ sudo ln -s /usr/local/instantclient/12.2.0.1.0/sdk/include/*.h /usr/local/include/
$ sudo ln -s /usr/local/instantclient/12.2.0.1.0/sqlplus /usr/local/bin/
$ sudo ln -s /usr/local/instantclient/12.2.0.1.0/*.dylib /usr/local/lib/
$ sudo ln -s /usr/local/instantclient/12.2.0.1.0/*.dylib.12.1 /usr/local/lib/
$ sudo ln -s /usr/local/lib/libclntsh.dylib.12.1 /usr/local/lib/libclntsh.dylib

安裝OCI8

在macOS環境下，需使用 pecl 自行安裝 oci8

$ pecl install oci8

$ pecl install http://pecl.php.net/get/oci8-2.2.0.tgz

在安裝過程當中，我們需要輸入 Oracle Home 的位置，請填入 instantclient,/usr/local/instantclient_12_2 即可，等待完成安装。安裝過程如果沒有出現任何錯誤訊息，表示安裝成功、重啟 php-fpm即可！

安裝、使用laravel-oci8

請見compose.json第十四行，加入 yajra/laravel-oci8

{
    "name": "laravel/laravel",
    "type": "project",
    "description": "The Laravel Framework.",
    "keywords": ["framework", "laravel"],
    "license": "MIT",
    "require": {
        "php": "^7.3|^8.0",
        "fruitcake/laravel-cors": "^2.0",
        "guzzlehttp/guzzle": "^7.0.1",
        "laravel/framework": "^8.65",
        "laravel/sanctum": "^2.11",
        "laravel/tinker": "^2.5",
        "yajra/laravel-oci8": "^8.5"
    },
    "require-dev": {
        "facade/ignition": "^2.5",
        "fakerphp/faker": "^1.9.1",
        "laravel/sail": "^1.0.1",
        "mockery/mockery": "^1.4.4",
        "nunomaduro/collision": "^5.10",
        "phpunit/phpunit": "^9.5.10"
    },
    "autoload": {
        "psr-4": {
            "App\\": "app/",
            "Database\\Factories\\": "database/factories/",
            "Database\\Seeders\\": "database/seeders/"
        }
    },
    "autoload-dev": {
        "psr-4": {
            "Tests\\": "tests/"
        }
    },
    "scripts": {
        "post-autoload-dump": [
            "Illuminate\\Foundation\\ComposerScripts::postAutoloadDump",
            "@php artisan package:discover --ansi"
        ],
        "post-update-cmd": [
            "@php artisan vendor:publish --tag=laravel-assets --ansi"
        ],
        "post-root-package-install": [
            "@php -r \"file_exists('.env') || copy('.env.example', '.env');\""
        ],
        "post-create-project-cmd": [
            "@php artisan key:generate --ansi"
        ]
    },
    "extra": {
        "laravel": {
            "dont-discover": []
        }
    },
    "config": {
        "optimize-autoloader": true,
        "preferred-install": "dist",
        "sort-packages": true
    },
    "minimum-stability": "dev",
    "prefer-stable": true
}

加入完畢後，請執行以下指令進行安裝

$ composer install

在 .env 設定檔加入以下參數

ORACLE_DB_CONNECTION=oracle
ORACLE_DB_HOST=127.0.0.1
ORACLE_DB_PORT=1521
ORACLE_DB_DATABASE=test_db
ORACLE_DB_USERNAME=username
ORACLE_DB_PASSWORD=password

新增config/oracle.php

<?php

return [
    'oracle' => [
        'driver'         => 'oracle',
        'tns'            => env('DB_TNS', ''),
        'host'           => env('DB_HOST', ''),
        'port'           => env('DB_PORT', '1521'),
        'database'       => env('DB_DATABASE', ''),
        'service_name'   => env('DB_SERVICENAME', ''),
        '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'),
        'server_version' => env('DB_SERVER_VERSION', '12c'), //請依據Oracle Database版本調整
        'dynamic'        => [],
    ],
];

在config/database.php connections 陣列加入 oracle connection 設定

'connections' => [
    'oracle' => [
            'driver' => 'oracle',
            'host' => env('ORACLE_DB_HOST'),
            'port' => env('ORACLE_DB_PORT'),
            'database' => env('ORACLE_DB_DATABASE'),
            'service_name' => 'ORCL',
            'username' => env('ORACLE_DB_USERNAME'),
            'password' => env('ORACLE_DB_PASSWORD'),
            'charset' => 'AL32UTF8',
            'prefix' => '',
        ],
];

最後，執行以下程式碼即可確認 laravel 是否與 oracle 正確建立連線了！

public function testDB(){
    try {
        \DB::connection()->getPdo();
        if(\DB::connection()->getDatabaseName()){
            echo "Yes! Successfully connected to the DB: " . \DB::connection()->getDatabaseName();
        }else{
            die("Could not find the database. Please check your configuration.");
        }
    } catch (\Exception $e) {
        die("Could not open connection to database server.  Please check your configuration.");
    }
}