ZF-5893: Can not connect to Oracle Database with SERVICE_NAME and can't use tnsnames.ora as well

Issue Type:
Bug
Created:
2009-02-26T11:22:59.000+0000
Last Updated:
2012-11-20T21:38:02.000+0000
Status:
Open
Fix version(s):
    Reporter:
    Vik Paul (neo123)
    Assignee:
    None
    Tags:
    • Zend_Db_Adapter_Oracle
    Related issues:
    Attachments:

    Description

    I am using zend core version 2.5.2 and attempting to connect to Oracle 10g database. I used following code to set up the details $config = new Zend_Config( array( 'database' => array( 'adapter' => 'pdo_oci', 'params' => array('host'=> 'host1, 'port'=>'1521','dbname'=> 'DEV','username' => 'dev','password' => 'pwd') ) ) ); $db = Zend_Db::factory($config->database); Zend_Db_Table::setDefaultAdapter($db);

    I keep getting ORA-12505: TNS:listener does not currently know of SID given in connect descriptor (ext\pdo_oci\oci_driver.c:581)' for this. The database is expecting SERVICE_NAME instead of SID. And for this reason I can't seem to connect to the database.

    I tried putting TNS_ADMIN in environment variable and assign tnsnames.ora entry to dbname. Then I get illegal address parameter error. I could connect to the databases which can accept SID parameter. But since this is clustered database, it expects service_name. Any help is appreciated. I am running out of ideas.

    Comments

    Posted by Vik Paul (neo123) on 2009-02-26T14:43:19.000+0000

    I changed:

    ../library/Zend/Db/Adapter/Pdo/Oci.php line 89: replaced SID with SERVICE_NAME

    It is working fine now.

    Posted by Jurrien Stutterheim (norm2782) on 2009-02-26T15:49:14.000+0000

    Which version of the Pdo_Oci adapter are you using? There's a patched version in the trunk that allows you to use tnsnames.ora.

    Posted by Vik Paul (neo123) on 2009-02-27T09:16:34.000+0000

    We are using zend core v2.5. I couldn't see any version info on pdo_oci in admin console. But found following line in ../ZendFramework/library/Zend/Db/Adapter/Pdo/Oci.php file

    @version $Id: Oci.php 11942 2008-10-13 20:21:18Z mikaelkael $

    Posted by Mickael Perraud (mikaelkael) on 2009-04-17T00:07:27.000+0000

    Try the new 1.8, please

    Posted by Roger Hunwicks (rhunwicks) on 2009-07-01T09:44:20.000+0000

    We are using the Oracle Easy Connect syntax in conjunction with the service name. I think this approach is better, because all the configuration is within the Zend_Config, we are not reliant on the TNSNAMES being configured correctly. Obviously, if you have a cluster or something like that, TNSNAMES is probably the better option:

    $config = new Zend_Config(
array(
'database' => array(
'adapter' => 'oracle',
'params' => array('dbname'=> '//hostname.domain.tld/service_name','username' => 'dev','password' => 'pwd')
)
)
);
$db = Zend_Db::factory($config->database);
Zend_Db_Table::setDefaultAdapter($db);

    Posted by franek (franek) on 2009-09-11T03:44:58.000+0000

    Hi,

    For us, Oracle Easy Connect syntax does not work. To resolve the problem we need to use tnsnames.ora syntax :

    
$config = new Zend_Config(
array(
'database' => array(
'adapter' => 'oracle',
'params' => array(
    'dbname'=> '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = hostname.domain.tld)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = service_name)))',
    'username' => 'dev',
    'password' => 'pwd')
    )
)
);
$db = Zend_Db::factory($config->database);
Zend_Db_Table::setDefaultAdapter($db);

    ...