Connecting to a Remote Oracle Database with PHP and the Oracle Instant Client

Album Cover: The Doors

"Before you slip into unconsciousness, I'd like to have another kiss. Another flashing chance at bliss."
The Doors / The Crystal Ship

Posted on January 04, 2007 8:25 PM in Web Development
Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

Connecting to a remote Oracle database via PHP on Windows is relatively straightforward. Keep in mind I'm stating that after having gone through the process a couple of times.

First off, you'll need to download the Oracle Instant Client. I downloaded it directly from this page after agreeing to some licensing mumbo jumbo. I went with the Instant Client Package - Basic Lite package since I wasn't interested in non-English error messages and the like. I'd also recommend downloading Instant Client Package - SQL*Plus from that same location, because it's a good way to do a sanity check before you move on to the PHP-specific stuff.

Once you've downloaded those packages, unzip them both to the same directory. I chose C:\Program Files\Oracle Instant Client, so I'll be referring to that location for the remainder of these instructions. When you're done, you should\ have a series of .dll files and a few others, along with the SQL*Plus executable, sqlplus.exe. Next thing you'll want to do is create a new file in that same directory named tnsnames.ora. This is an important configuration file that allows you to create kind of a local reference to a remote Oracle database server. Paste the following into that new file:

RDF =  (DESCRIPTION =
  (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = REMOTESERVER)(PORT = 1521))
  )
  (CONNECT_DATA =
   (SERVICE_NAME = LOCALNAME)
  )
 )

Where REMOTESERVER is the name of the remote server that has Oracle running on it and LOCALNAME is the local name by which you wish to refer to the connection with that server.

This is where the sanity check with SQL*Plus comes in. Open up a Windows command prompt and change to the directory where you unzipped all your Oracle Instant Client files (e.g. C:\Program Files\Oracle Instant Client). From there, run the following command to test your setup so far:

sqlplus username/password@LOCALNAME

Where username is a user account with access to the remote Oracle server, password is the password for that user account, and LOCALNAME is, you guessed it, the local name by which you've chosen to refer to the connection with the remote server. If you're presented with an SQL> prompt at this point, you're good to go, knowing that your Oracle connection is in a good state and you're ready to move on to the PHP setup.

To get your PHP scripts talking to the remote Oracle server, you'll use the oci_connect() function. Before you can do that, though, you'll need to enable support for the oci8 module. To do this, open up your php.ini file and uncomment (remove the leading semicolon from) the line that reads:

;extension=php_oci8.dll

Once you've done that, you'll need to update your computer's environment variables. First, modify your PATH environment variable so that it has a reference to C:\Program Files\Oracle Instant Client. Then, create a new environment variable named TNS_ADMIN that points to the same directory. This environment variable ensures that PHP will be able to find your tnsnames.ora file when it tries to connect via oci_connect(), so it's important. Once you've set these environment variables correctly, it's probably a good idea to restart the machine your web server is running on. I had to do this before the changes would register (you can easily check your path if you make a call to phpinfo() from a script). However, it might be possible to only restart Apache (or whatever web server you're running), so you might give that a shot first just to see if you're lucky.

At this point, you should be ready to start connecting to the remote Oracle server from your PHP scripts. To make sure, you can run the following script, keeping in mind that the settings should match what you've set in the steps above:

// try connecting to the database
$conn = oci_connect('username', 'password', 'LOCALNAME');

// check for any errors
if (!$conn)
{
 $e = oci_error();
 print htmlentities($e['message']);
 exit;
}

// else there weren't any errors
else
{
 echo 'I am an Oracle mack daddy.';
}

This is all off the top of my head after going through this process a couple times. If your mileage varies and you think I missed something, be sure to leave a comment and I'll see if I can't correct any mistakes.

Comments

Hyrum on February 26, 2007 at 5:17 PM:

Hi Bernie! You're site is awesome about configuring php to remotely connect to oracle... however... i did everything that your entry said to do.... but it keeps saying.... "undefined function oc_connect" etc etc... i have decommnented the phpoci8.dll in php.ini... i have declared the two global environment variables... but to no avail.. i can't get it to work! what do you think?

Permalink

Q-tech on March 08, 2007 at 6:17 AM:

Hi Barnie.
First of all, thanks for the great and straightforward article!

However, I would like to point out there are two "minor" details for this setup to work:

* PHP _needs_ client's directory (C:Program FilesOracle Instant Client) to be not only in %TNA_ADMIN% but also in %PATH% env. variable in order to find oci.dll! If you dont feel like playing with variables (and restarting server) it's way faster to extract/create all necessery files in C:WINDOWSSYSTEM32 for example.

* You failed to mention that this "RDF" in tnsnames.ora (for Oracle 10g at least) actually represents your _local_ service name! Therefore you need something like the following content for direct TCP connections:

LOCALNAME = (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = YOURSERVERIP)(PORT = 1521))
)
)


Cya.

Permalink

Gavin on April 29, 2007 at 8:12 AM:

This was excellent, thanks!

Permalink

Nags on September 11, 2007 at 1:28 AM:

Hi, I have one doubt about configuring Oracle with PHP, i have Windows 2000 Advanced Server with Oracle 9i and i have installed WAMP in my system but WAMP is not working coz already Oracle 9i HTTP is using 80 port, can you please help me how to connect Oracle 9i with WAMP.

Nags

Permalink

ygrem on November 09, 2007 at 5:21 AM:

first of all i would like to thank you .my problem is that i used oracle 9i client installer and install then when i try to excute ur sample php script.this message is displayed "Fatal error: Call to undefined function oci_connect() in C:Program FilesVertrigoServwwwluck.php on line 5" can you help me on these .
thanx

Permalink

svetimas on November 19, 2007 at 4:11 AM:

Hello,
I have one problem with PHP and OracleHTTP server. On WinXP it runs ok, but on Win2000 server (with the same PHP and OracleHTTP configurations) Apache crashes with no errors. When I comment out extensions=php_oci8.dll it runs ok. I have to mention that other extensions are loaded good, but problem is with php_oci8.dll. I havent found solution anywhere...

Permalink

gov on November 27, 2007 at 5:04 AM:

Hi
The above item was usefull but one thing if i check in cmd prompt
sqlplus username/password@LOCALNAME
it is working fine.
when i check connection through php function oci_connect it is giving Fatal Error: call to undefined function oci_connect();

Please giude how to get rid of this Fatal error.

Permalink

Welolemy on January 12, 2008 at 10:46 AM:

I've two sites and I want to make remote connection from one site to oracle 9i server on the other site through internet.
Can anyone help me how to do it?

Permalink

sornakumar on February 08, 2008 at 10:21 PM:

hi

how to connect php with oraclehttp server?

please gave solution to it's very usefull for me.

thanks

Permalink

suraj on May 26, 2008 at 6:50 AM:

// try connecting to the database
$conn = oci_connect('username', 'password', 'LOCALNAME');

// check for any errors
if (!$conn)
{
$e = oci_error();
print htmlentities($e['message']);
exit;
}

// else there weren't any errors
else
{
echo 'I am an Oracle mack daddy.';
}

i m using this code to connect the system with the
isntant server but i m facing a problem the error it is showing is
server conection fail 500

Permalink

Ali on September 05, 2008 at 4:28 PM:

Excellent article. Very clear and helped me set up my environment on my computer.

Thanks!

Permalink

vasu on September 10, 2008 at 6:05 AM:

Hi!,

A great help you have done for me. Even oracle website does not explain the connectivity issue thru' PHP in such clear terms.

A million thanks for educating me.

Regards
Vasu, India

Permalink

utkarsh on November 10, 2008 at 11:39 PM:

pls help me i do all the things u tell but in command prompt i got the error

ORA-12154 TNS: could not resolve the service name..
help me out

Permalink

utkarsh on November 11, 2008 at 1:54 AM:

i resolve this error..bt still when i use to connect it by php script the error is
undefined function. i used oci_connect and ocilogon both the functions........

Permalink

raja on January 03, 2009 at 7:11 AM:

Hi,
I am using oracle 9i. Now I install WAMP5 but when I enable oracle extensions=php_oci8.dll and run phpinfo(). Oracle oci8 not show in phpinfo(). I check port 80 by wamp It show apache use port 80. when I try connect oracle by php it give error message "Fatal error: Call to undefined function oci_connect()" please any one guide how i solve this problem its very urgent.

Permalink

ahmed on March 13, 2009 at 7:24 AM:

salut,
je vais commencer mon premier projet avec oracle 9i.
j'ai fait l'installation de:
*oracle 9i
* wamp
* activer l'extension php_oci8.dll
mais lorsque je connecte vers la base de donnée, j'ai l'erreur suivant: "Fatal error: Call to undefined function oci_connect()" vous pouvez m'aider svp

merci

Permalink

Mohamed Wali on October 03, 2009 at 1:53 AM:

It's Easy To Connect your PHP server page to Oracle
two steps are important
// check for any errors
if (!$conn)
{
$e = oci_error();
print htmlentities($e['message']);
exit;
}

// else there weren't any errors
else
{
echo 'I am an Oracle mack daddy.';
}

Permalink

remote db on October 06, 2009 at 5:45 AM:

Hi,
I am trying to connect oracle 9 db which is located in remote machine using php coding on Windows.
I used php function oci_connect() for connecting oracle database but i can't connect to remote database
Please any one guide how i solve this problem its very urgent.
Configurations :
WAMP SERVER 2.0
PHP 5.2
MYSQL 5.1

Thanks,
Remote DB

Permalink

Trinadh on December 09, 2009 at 9:02 PM:

I used php function oci_connect() for connecting oracle database but i can't connect to remote database

pls give me code and configuration settings

i am using


xampp
windows xp
oracle 9i

pls urgent

Permalink

Arunabh Das on January 13, 2010 at 6:21 PM:

Absolutely brilliant tutorial. Thanks a ton for this. - Arunabh Das

Permalink

SOPHEA on February 02, 2010 at 12:48 AM:

i can't find oci.dll. How can i get some suggestion from any one guide.

regards.

Permalink

kos on July 14, 2010 at 5:51 PM:

Hi
This is a great article. I thought I got the connectivity issue from php to oracle by the ball....but it still ain't working for me......I tried all the config and settings as mentioned in the article.....I'm still looking for solution to this.....I'm using Oracle 9.

Permalink

kos on July 14, 2010 at 6:15 PM:

when testing there is nothing......not even error message....just blank white page......code below:

$conn = oci_connect($dbusername, $dbpwd, 'MYORATESTDB');
echo "After oci_connect call ----- From dbconnection file......
";
// handle connection ERROR.
if (!$conn)
{
$e = oci_error();
trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
exit;
//echo "DB connection failed.";
}
else
{
echo "We're inside Oracle";
oci_close($conn);
}

Permalink

Tanzania on February 04, 2011 at 4:01 AM:

I have searched for an article for this for like a week now...waking up with google everyday...

Your article has made my day.
I will be so selfish if i won't say thank you for such an article that has helped me got through my problem.
Thank you so very much very much
I rank you...
* * * * *

Permalink

debranjan bera on February 24, 2011 at 4:34 AM:

Give a solution when i connect Oracle 9i with WAMP with out any error generation.

Permalink

SAMAD KHAN on March 20, 2011 at 2:28 AM:

Can any one tell me that how i can connect my website(Built in Asp) with Oracle Database.
Please give me the connection String. I try it many times but fail to do so.
Thanks

Permalink

Erlish on April 03, 2011 at 8:39 PM:

Thanks a lot!

Permalink

Ranjeet Kumar Bhatia on July 15, 2011 at 11:53 PM:

I am new in PHP.

I fallow your all instructions but by bad luck unable to connect with oracle DB 10g and 11g ,Page raise a exception .

Fatal error: Call to undefined function oci_connect()

I all ready added
extension=php_oci8.dll
extension=php_oci8_11g.dll
extension=php_oracle.dll

In php.ini

i have remote database and local database also...

Permalink

Philip on July 28, 2011 at 7:12 PM:

Hei, I finally the way to work

1. copy the all CONTENT of instantclient_10_2 to apache2.2.17/bin
2. restart apache
$db_test = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=178.178.2.82)(PORT=1521)) (CONNECT_DATA=(SID=DBMD)))';
$c = oci_connect("user", "pass", $db_test);
$s = oci_parse($c, 'select * from some_table');
oci_execute($s);
while ($res = oci_fetch_array($s, OCI_ASSOC)) {
var_dump($res);
}
?>

Permalink

Paul on August 24, 2011 at 4:06 AM:

Nice one Philip, you've fixed my problem

Thanks for the article

Permalink

cmholm on September 22, 2011 at 4:49 PM:

I used PuTTY to create an SSH tunnel to a remote Oracle instance, such that I could point SQL Developer to localhost at (in my case) port 1521, set the SID, and was good to go. However, SQLPlus wasn't feeling the love. I'm guessing I wasn't formating tnsnames.ora correctly. Instead, I followed the suggestion from another site:

> sqlplus /nolog
SQL> conn myname@'(description=(address=(protocol=TCP)(host=localhost)(port=1521))(connect_data=(sid=MYSID)))'

and successfully connected. That left the PHP example, from which I was getting a function.oci-connect error. I fixed that by using the easy connect naming method, such that the oci_connect line looked like this:

> $conn = oci_connect('myname', 'mypassword', '//localhost:1521/MYSID');

No tnsnames.ora needed, and I was an Oracle mack daddy.

Permalink

SatyamReddy on September 28, 2011 at 11:16 AM:

my database username is system ,
password is reddy.

for connecting with oracle database in place of localname
can i write like below

$conn = oci_connect('system', 'reddy', '//localhost:1521:xe');

is it correct?

it is giving error like:

Fatal error: Call to undefined function oci_connect() .

please send correct format..

Permalink

Yazmin on October 03, 2011 at 12:20 AM:

// try connecting to the database
$conn = oci_connect("EDEALER", "edealer123", '//10.61.1.10/BSCST2');

// check for any errors
if (!$conn)
{
$e = oci_error();
print htmlentities($e['message']);
exit;
}

// else there weren't any errors
else
{
echo 'I am an Oracle mack daddy.';
}
?>
---
the error :
Warning: oci_connect() [function.oci-connect]: OCIEnvNlsCreate() failed. There is something wrong with your system - please check that PATH includes the directory with Oracle Instant Client libraries in C:xampphtdocsedealer_serverbaroraConn.php on line 4

Warning: oci_error() [function.oci-error]: OCIError: unable to find error handle in C:xampphtdocsedealer_serverbaroraConn.php on line 9

Permalink

GnuDoyng on December 12, 2011 at 12:30 AM:

I am experiencing the above error as well. Anyone knows the solution?

Permalink

Susan H. on January 13, 2012 at 7:19 AM:

That mention Philip finally solves all my problems
{
1. copy the all CONTENT of instantclient_10_2 to apache2.2.17/bin
2. restart apache
}

So really thank you very much for take the time and share your solution !

best!
Susan

Permalink

Sophen on April 24, 2012 at 2:18 AM:

I got this error

Warning: oci_connect() [function.oci-connect]: ORA-12705: Cannot access NLS data files or invalid environment specified in C:wampwwwPHPoracletesttestphpoci8.php on line 3

Permalink

Sophen on April 24, 2012 at 2:59 AM:

I can solved this problem ORA-12705

Windows - The NLS_LANG must be unset in the Windows registry (re-named is best). Look for the NLS_LANG subkey in the registry at HKEY_LOCAL_MACHINESOFTWAREORACLE, and rename it.

Permalink

Blogging tutorials on October 17, 2012 at 5:32 AM:

Wow, you have just made my day. i've searched for this for the past 2 weeks. thanks pal

Permalink

govinda on December 27, 2012 at 3:51 AM:

$conn = oci_connect("username", "password", 'webdb.fontys.nl:1521/cicdb.informatica.local');
is this ok or notcoz this is our connection name for our university in isql plus.

Permalink

Nasrullah Shamim on March 04, 2014 at 11:20 PM:

Hi, I got this error

Warning: oci_connect(): ORA-12154: TNS:could not resolve the connect identifier specified in C:xampphtdocswebindex.php on line 2

When, instantclient_11_2 loaction is, C:instantclient_11_2

please solve this problem.

Permalink

Post Comments

If you feel like commenting on the above item, use the form below. Your email address will be used for personal contact reasons only, and will not be shown on this website.

Name:

Email Address:

Website:

Comments:

Check this box if you hate spam.