Wednesday, April 22, 2009

Connecting Oracle Database with oracle instant client using PHP in Linux

We can connect Oracle db without oracle client if only we can use instant client instead. But this is for 10g or later version of db.

1. You need to have an account for www.oracle.com. Create a new through Register for a free Oracle Web account) .

2. Choose which release of Oracle instant client you need here.

3. Accept license agreement, find the version you need, click to download. (I chose version 10.2 of basic, sdk and sqlplus package. Note that some version may not work in your system.)

4. Install it. You can install it using rpm or manually using .zip files.

5. If you downloaded sqlplus package and running on linux, you can check whether it works properly with command '>export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:[instant client's path]; ./sqlplus --help' in instantclient directory.

6. Now's the time to test db connection using sqlplus. Make text file with the name 'tnsnames.ora' with connect information like below.

YOURNAME =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = XXX.XXX.XXX.XXX)(PORT = XXXX))
)
(CONNECT_DATA =
(SID = XXXXX)
)
)

7. Set environment variables like below.

> export ORACLE_HOME=
> export TNS_ADMIN=

8. > now try to connect with id/pw. Does it work? Fine!

9. To make it work, you should recompile php module with the option like below.

--with-oci8=instantclient,

10. Check whether follow env variables set set properly

export TNS_ADMIN=/yoursystem/yourpath # where tnsnames.ora exists
export NLS_LANG=KOREAN_KOREA.UTF8 # set encoding for your locale

11. Check with . If you can find oci8 section in the information, it's done.

12. Now you can connect to oracle db using php functions. Let's see sample code.


<?

// ORA_SERVER : defined in tnsnames.ora

// Connect

$conn = oci_connect('user', 'pass', 'ORA_SERVER');

if (!$conn) {

    $e = oci_error();

    print htmlentities($e['message']);

    exit;

}

// Execute query

$query  = 'SELECT * FROM SAMPLETABLE';

$stid   = oci_parse($conn, $query);

if (!$stid) {

    $e = oci_error($conn);

    print htmlentities($e['message']);

    exit;

}

$r = oci_execute($stid, OCI_DEFAULT);

if (!$r) {$e = oci_error($stid);

    echo htmlentities($e['message']);

    exit;

}



// Fetch results

while ($row = oci_fetch_array($stid, OCI_RETURN_NULLS)) {

    foreach ($row as $item) {

        print ($item?htmlentities($item):'&nbsp;').'<br>';

    }

}



// Close

oci_close($conn);

?>