PHP 以 UTF-8寫入 ORACLE 變亂碼(2007-3-8)

When you encountered Error "ORA-12705: Cannot access NLS data files or invalid environment specified" or Your write UTF-8 into Oracle table and they turned into unrecognized codes..... Try this;

1. Check environment NLS_LANG variable using phpinfo() to see what setting is on Web server
2. Check Check environment NLS_LANG variable using set grep LANG to see what setting is on Oracle
3. NLS_LANG variables on both server must be the same to avoid 亂碼
   
   In my example: NLS_LANG=American_America.UTF8

我試著以 PHP 連上 Oracle DB 許久, 都不能成功... :(
Error: ORA-12541 listener not found!

今天終於試成功了! 我把這些過程寫下, 希望對你有點幫助!

1. Make sure that Linux environment have following variables, before you start your apache server.
   
   export ORACLE_HOME=/usr/lib/oracle/10.2.0.1
   export D_LIBRARY_PATH=/usr/lib/oracle/10.2.0.1/client/lib
   export TNS_ADMIN=/etc
   /usr/local/apache/bin/apachectl startssl
   
2. You must have the hosts you want to connect configured in /etc/tnsnames.ora (according to TNS_ADMIN's location)
   
   TEST =
   (DESCRIPTION =
   (ADDRESS_LIST =
   (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1522))
   )
   (CONNECT_DATA =
   (SERVICE_NAME = TEST)
   )
   )
   
3. Your PHP must compiled with oci8(Oracle DB) support, try:
   
   echo phpinfo();
   
   to see if you see oci8 libraries available.
   
4. Try to make connection from the Host to Oracle DB first to make sure that connection is available. And you should be inside Oracle shell
   
   shell>sqlplus USERID/PASSWORD@SID
   
   Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - Production
   
   SQL>
   
   
5. Using ADODB DB abstraction tool is an easy way to connect Oracle. If method A won't work, try method B
   
   in PHP include('include/adodb/adodb.inc.php');
   include('adodb/tohtml.inc.php'); //This for debug output
   $ADODB_CACHE_DIR = '/tmp/ADODB_cache';
   oracle_conn = &ADONewConnection('oci8');
   
   Method A.
   oracle_conn->connectSID = true;//If you are using SID to connect, you must have this line
   
   oracle_conn->Connect($ORACLE_HOST, $ORACLE_USER_ID, $ORACLE_USER_PASSWORD, $ORACLE_DATABASE_SID)
   
   
   Method B.
   oracle_conn->Connect(false, $ORACLE_USER_ID, $ORACLE_USER_PASSWORD, $ORACLE_DATABASE_SID)
   
   After this, you should be able to connect to Oracle DB using PHP