CakePHP : Oracle TimeStamp Format Error

Ketika aku bermain-main dengan framework CakePHP dengan database Oracle, aku menemukan error

Warning (512): SQL Error: ORA-01843: not a valid month [CORE\cake\libs\model\datasources\dbo_source.php, line 684]

Permasalahan dari error di atas adalah karena terjadi perbedaan format timestamp antara CakePHP dan Oracle.
Pada Oracle, format TimeStamp adalah ‘YYYY-MM-DD HH12:MI:SS AM’, sedangkan default TimeStamp CakePHP adalah ‘YYYY-MM-DD HH24:MI:SS’.

Aku menemukan solusi dengan menambahkan satu baris perintah pada file \cake\libs\model\datasources\dbo\dbo_oracle.php.
Langkah-langkahnya:
1. Carilah function connect()
2. Skrip asli dari function connect() adalah

function connect() {
		$config = $this->config;
		$this->connected = false;
		$config['charset'] = !empty($config['charset']) ? $config['charset'] : null;

		if (!$config['persistent']) {
			$this->connection = @ocilogon($config['login'], $config['password'], $config['database'], $config['charset']);
		} else {
			$this->connection = @ociplogon($config['login'], $config['password'], $config['database'], $config['charset']);
		}

		if ($this->connection) {
			$this->connected = true;
			if (!empty($config['nls_sort'])) {
				$this->execute('ALTER SESSION SET NLS_SORT='.$config['nls_sort']);
			}

			if (!empty($config['nls_comp'])) {
				$this->execute('ALTER SESSION SET NLS_COMP='.$config['nls_comp']);
			}
			$this->execute("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
			
		} else {
			$this->connected = false;
			$this->_setError();
			return false;
		}
		return $this->connected;
	}

3. Ubah menjadi

function connect() {
		$config = $this->config;
		$this->connected = false;
		$config['charset'] = !empty($config['charset']) ? $config['charset'] : null;

		if (!$config['persistent']) {
			$this->connection = @ocilogon($config['login'], $config['password'], $config['database'], $config['charset']);
		} else {
			$this->connection = @ociplogon($config['login'], $config['password'], $config['database'], $config['charset']);
		}

		if ($this->connection) {
			$this->connected = true;
			if (!empty($config['nls_sort'])) {
				$this->execute('ALTER SESSION SET NLS_SORT='.$config['nls_sort']);
			}

			if (!empty($config['nls_comp'])) {
				$this->execute('ALTER SESSION SET NLS_COMP='.$config['nls_comp']);
			}
			$this->execute("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
			//---- D MODIFY ----//
			$this->execute("ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS'");
		} else {
			$this->connected = false;
			$this->_setError();
			return false;
		}
		return $this->connected;
	}

Anda akan melihat tambahan 1 baris perintah yaitu

 $this->execute("ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS'"); 

yang digunakan untuk mengubah session timestamp format menjadi sama dengan timestamp format milik CakePHP.

Silakan dicoba 🙂