Day: September 30, 2008

改 oracle 的內碼成 utf8 commands

by 
select * from v$nls_parameters

1	NLS_LANGUAGE	TRADITIONAL CHINESE
2	NLS_TERRITORY	TAIWAN
3	NLS_CURRENCY	NT$
4	NLS_ISO_CURRENCY	TAIWAN
5	NLS_NUMERIC_CHARACTERS	.,
6	NLS_CALENDAR	GREGORIAN
7	NLS_DATE_FORMAT	DD-MON-RR
8	NLS_DATE_LANGUAGE	TRADITIONAL CHINESE
9	NLS_CHARACTERSET	WE8ISO8859P1
10	NLS_SORT	BINARY
11	NLS_TIME_FORMAT	HH.MI.SSXFF AM
12	NLS_TIMESTAMP_FORMAT	DD-MON-RR HH.MI.SSXFF AM
13	NLS_TIME_TZ_FORMAT	HH.MI.SSXFF AM TZR
14	NLS_TIMESTAMP_TZ_FORMAT	DD-MON-RR HH.MI.SSXFF AM TZR
15	NLS_DUAL_CURRENCY	NT$
16	NLS_NCHAR_CHARACTERSET	AL16UTF16
17	NLS_COMP	BINARY
18	NLS_LENGTH_SEMANTICS	BYTE
19	NLS_NCHAR_CONV_EXCP	FALSE


要把這個 NLS_CHARACTERSET 改成:
9	NLS_CHARACTERSET	AL32UTF8

查 oracle 的內碼使用的是啥?

SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';

PARAMETER
------------------------
VALUE
------------------------
NLS_CHARACTERSET
WE8ISO8859P1

NLS_NCHAR_CHARACTERSET
AL16UTF16


看一下 NLS_CHARACTERSET 不是 AL32UTF8

在 sqlplus 用 sysdba 進去, 下這些 commands

shutdown immediate;
STARTUP MOUNT;
ALTER SESSION SET SQL_TRACE=TRUE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE character set INTERNAL_USE AL32UTF8;
ALTER SESSION SET SQL_TRACE=FALSE;

shutdown immediate;
startup;


環境變數
NLS_LANG="TRADITIONAL CHINESE_TAIWAN".AL32UTF8
export NLS_LANG

Codeigniter 聯接 Oracle database 要改的地方

by

Codeigniter version 1.6.3
Oracle version 10.2.0.4 use oci8

遇到這個 error :
Message: ocifetchinto() expects parameter 1 to be resource, null given

// 這個檔 \system\database\DB_driver.php 改成..

// $RES->num_rows	= $RES->num_rows();

if ($this->dbdriver == 'oci8')
{
  $RES->stmt_id		= $this->stmt_id;
  $RES->curs_id		= NULL;
  $RES->limit_used	= $this->limit_used;
}
$RES->num_rows	= $RES->num_rows();


解決沒有使用 UTF8 的問題

// 這個檔 \system\database\drivers\oci8\oci8_driver.php
// 改兩個地方:
function db_connect() 的
return @ocilogon($this->username, $this->password, $this->hostname,$this->char_set);

db_pconnect() 的
return @ociplogon($this->username, $this->password, $this->hostname,$this->char_set);


codeigniter 的連線設定

$db['default']['hostname'] = '//172.20.10.6/DB1';
$db['default']['username'] = "scott";
$db['default']['password'] = "*****";
$db['default']['database'] = "DB1";
$db['default']['dbdriver'] = "oci8";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = TRUE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";

相關聯結:

http://download.oracle.com/docs/cd/B25329_01/doc/install.102/b25144/toc.htm#BABBGFIC

codeigniter 的 oracle driver 問題還不少, 同一隻程式第二個 sql select 的結果跟第一個一樣…

要改

/system/database/drivers/oci8/oci8_driver.php
的159行
if ( ! is_resource($this->stmt_id))
{
  $this->stmt_id = ociparse($this->conn_id, $this->_prep_query($sql));
}
改成
$this->stmt_id = ociparse($this->conn_id, $this->_prep_query($sql));
就夠了.