PHP 連接 Oracle 實戰：ORA 函式族完整應用指南

PHP 提供兩套 Oracle 連線函式：ORA_（舊版）與 OCI（新版），語法相近但 OCI 功能更完整。本文詳細說明連線建立、查詢執行、結果輸出與 HTTP 認證整合的完整流程。

基礎連線測試

<?php
if ($conn = ora_logon("user@TNSNAME", "password")) {
    echo "SUCCESS! Connected to database\n";
} else {
    echo "Failed :-( Could not connect to database\n";
}
ora_logoff($conn);
phpinfo();
?>

關鍵點：使用 TNSNAMES.ORA 定義的服務名稱，成功回傳非零連線 ID 存入 $conn。

完整查詢流程與錯誤處理

<?php
function printoraerr($in_cur) {
    // 檢查 Oracle 錯誤，有則顯示
    if (ora_errorcode($in_cur))
        echo "Oracle code - " . ora_error($in_cur) . "\n";
    return;
}

// 主程式
if (!($conn = ora_logon("user@TNSNAME", "password"))) {
    echo "Connection to database failed\n";
    exit;
}
echo "Connected as connection - $conn\n";

echo "Opening cursor ...\n";
$cursor = ora_open($conn);  printoraerr($cursor);
echo "Opened cursor - $cursor\n";

$qry = "select user, sysdate from dual";
echo "Parsing the query $qry ...\n";
ora_parse($cursor, $qry, 0);  printoraerr($cursor);
echo "Query parsed\n";

echo "Executing cursor ...\n";
ora_exec($cursor);  printoraerr($cursor);
echo "Executed cursor\n";

echo "Fetching cursor ...\n";
while (ora_fetch($cursor)) {
    $user = ora_getcolumn($cursor, 0);  printoraerr($cursor);
    $sysdate = ora_getcolumn($cursor, 1);  printoraerr($cursor);
    echo "  row = $user, $sysdate\n";
}
echo "Fetched all records\n";

echo "Closing cursor ...\n";
ora_close($cursor);
echo "Closed cursor\n";

echo "Logging off from oracle...\n";
ora_logoff($conn);
echo "Logged off from oracle\n";
?>

進階：查詢結果格式化輸出為 HTML 表格

<?php
function printoraerr($in_cur, $conn) {
    if (ora_errorcode($in_cur)) {
        echo "Oracle code - " . ora_error($in_cur) . " \n";
        ora_logoff($conn);
        exit;
    }
    return;
}

function exequery($w_qry, $conn) {
    $cursor = ora_open($conn);  printoraerr($cursor, $conn);
    ora_parse($cursor, $w_qry, 0);  printoraerr($cursor, $conn);
    ora_exec($cursor);  printoraerr($cursor, $conn);
    
    $numrows = 0;
    $w_numcols = ora_numcols($cursor);
    
    // 輸出表頭
    echo "\n\n";
    for ($i = 0; $i < $w_numcols; $i++) {
        $align = (ora_columntype($cursor, $i) == "NUMBER") ? "RIGHT" : "LEFT";
        echo "\t" . ora_columnname($cursor, $i) . "\n";
    }
    echo "\n";
    
    while (ora_fetch($cursor)) {
        echo "\n";
        for ($i = 0; $i < $w_numcols; $i++) {
            $align = (ora_columntype($cursor, $i) == "NUMBER") ? "RIGHT" : "LEFT";
            if (ora_columntype($cursor, $i) == "LONG") {
                echo "\t" . ora_getcolumn($cursor, $i) . " \n";
            } else {
                echo "\t" . ora_getcolumn($cursor, $i) . "\t\n";
            }
            printoraerr($cursor, $conn);
        }
        $numrows++;
        echo "\n";
    }
    
    if ($numrows == 0)
        echo "  Query returned no records\n\n";
    else {
        echo "\n";
        echo "  Count\n";
        echo "  $numrows\n";
        echo "  \n";
    }
    echo "\n";
    ora_close($cursor);
    return;
}

// 主程式
if (!($conn = ora_logon("user@SID", "password"))) {
    echo "Error: Cannot connect to database\n";
    exit;
}

$qry = "SELECT deptno \"Dept\", empno \"Emp\", empnm \"Name\", salary \"Salary\" 
        FROM employee ORDER BY 1, 2";
exequery($qry, $conn);
ora_logoff($conn);
?>

HTTP Basic 認證整合 Oracle 登入

利用 Apache/PHP 的 $PHP_AUTH_USER、$PHP_AUTH_PW 變數，實現瀏覽器原生登入框直接對應資料庫帳號：

<?php
if (!isset($PHP_AUTH_USER)) {
    header("WWW-Authenticate: basic realm=\"$SID\"");
    header("HTTP/1.0 401 Unauthorized");
    $title = "Login Instructions";
    echo "\n\nYou are not authorized to enter the site\n\n";
    exit;
} else {
    if (!($conn = ora_logon("$PHP_AUTH_USER@$SID", $PHP_AUTH_PW))) {
        header("WWW-Authenticate: basic realm=\"$SID\"");
        header("HTTP/1.0 401 Unauthorized");
        $title = "Login Instructions";
        echo "\n\nYou are not authorised to enter the site\n\n";
        exit;
    }
}
?>

運作流程：

1. 未提供認證 → 送出 401 標頭 + WWW-Authenticate → 瀏覽器彈出登入框
2. 使用者輸入帳密 → 瀏覽器重送請求並帶入 Authorization 標頭
3. PHP 解析填入 $PHP_AUTH_USER、$PHP_AUTH_PW
4. 嘗試 ora_logon()，失敗再次送出 401

關鍵函式補充說明

環境部署需求

• PHP 編譯需啟用 --with-oracle（ORA）或 --with-oci8（OCI）
• Oracle Client Libraries 必須正確安裝並設定 ORACLE_HOME
• TNSNAMES.ORA 需正確定義服務名稱
• Web 伺服器執行身分需具備存取 Oracle 客戶端程式庫權限

從 ORA 遷移到 OCI8 的關鍵差異

新專案強烈建議直接使用 OCI8 或 PDO_OCI，享有更好效能、功能與長期支援。