Oracle with PHP Doctrine Issues.

We had some issues with a PHP Doctrine deployment, one of which included a “ORA-01861: literal does not match format string.” message. We made a quick fix by changing the date fields into varchar but the proper fix is mentioned below:

Most likely “ORA-01861: literal does not match format string.”
happened because date format in database and doctrine is not the same.
In oracle default date format depends on your installation and most
likely it looks like this: 21-JUN-2009. So when doctrine inserts
atable object, it tries to set CREATED_AT column using ‘Y-m-d H:i:s’
format and you get an exception. To get around this problem you can
create connection listener that sets default date format on connect:
ALTER SESSION SET NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’ or
$connection->setDateFormat(). Ideally doctrine should automatically
set oracle date on connect.

Thanks Vadik56
http://groups.google.com/group/doctrine-user/browse_thread/thread/78d1d33576ab905f

Also it was pretty difficult to find the proper format of the oracle connection string, working example:

in config

dsn: oci://[user]:[password]@[server_url]/(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=TCP)(HOST = [server_url])(PORT=1521))) (CONNECT_DATA=(SID=[namespace])))

or in code

doctrine.connection_string =
“oci://[user]:[password]@[server_url]/(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=[server_url])(PORT=1521))) (CONNECT_DATA=(SID=[namspace])))”

Thanks Bertrand
http://forum.symfony-project.org/index.php/m/71601/
and
http://www.doctrine-project.org/Doctrine_Connection_Oracle/1_1#method_setdateformat

To monitor queries, add an event listener:

$profiler = new Doctrine_Connection_Profiler();
$conn = Doctrine_Manager::connection();
$conn->setListener($profiler);

…
foreach ( $profiler as $event ) {
echo $event->getQuery();
}
…

http://stackoverflow.com/questions/841950/doctrine-profiler-does-not-catch-queries