ORA-12154 : "TNS could not resolve the connect identifier specified error" is displayed while executing Query in Cross Connection Editor(Oracle And GreenPlum).

Hi,

I am using TOAD data point 3.6 and trying to run a query by connecting two DBs in Cross connection editor–(1) Oracle (2) Greenplum.

(1) Oracle- DB/Schema/Table :: 020P_PPE/OCDW/DISB_VER_B,DISB_SCHEDULE_B
(2) Greenplum – DB/Schema/Table:: udw_dev/udw_ids_elt/cdw_cashflow_key

While trying to execute heterogeneous queries in Toad Data point Cross Connection editor as the statement like below.

SELECT COUNT(*) FROM udw_dev.udw_ids_elt.cdw_cashflow_key A, src_020P_PPE.OCDW.DISB_VER_B B, src_020P_PPE.OCDW.DISB_SCHEDULE_B C WHERE
B.DISB_NUM = A.disb_num AND
C.FREQUENCY_TYPE_CD = A.FREQUENCY_TYPE_CD

The below error is displayed:

“Cross Query Database Error: Invalid data source definition: ORA-12154: TNS: could not resolve the connect identifier specified; The driver doesn’t support the version of ODBC behavior that the application requested(see SQLSetEnvAttr)”

Note: But when I am trying to execute the query after separatng like Oracle or GreenPlum in Cross connecton Editor its working fine.

Could you please let me know how to fix this ?

Thanks,
Bharat

To bharat_ub:

Thank you for posting in Toad World!

Looking at the heterogeneous cross connection sql, it appears that it is missing the connection identifiers enclosed in `. It kinda looks like this:

SELECT /* rows 1000 */
CONTACT.FIRST_NAME, CONTACT.BIRTH_DATE
FROM MyCoolOracleConnectionName.SCHEMA.TABLE CONTACT
INNER JOIN
MyCoolGreenPlumConnectionName.SCHEMA.TABLE ADDRESS
ON (CONTACT.ADDRESS_ID = ADDRESS.ADDRESS_ID)

I recommend constructing the query through our Cross Connection Query Builder! It should construct the SQL for our Cross Query.

Software Developer I,
-Joshua Liong