Facing problem in update statement...when using Oracle Transparent Gateway

From: <lovkeshanand_at_gmail.com>
Date: 16 Jan 2006 00:44:26 -0800
Message-ID: <1137401065.950671.248510@f14g2000cwb.googlegroups.com>

Dear All,

                I am facing Problem in Update Query when I am Updating

the Oracle table "ACCOUNT" From the Sql Server Table "Account" using DBLINK ("@DBL_NO_GCMS" which itself refers to SQL SERVER Data Base Through Oracle Transparent Gateway Connectivity), as When I Fires the following Query :-

UPDATE ACCOUNT A
SET ("ACCOUNT_AGE","ACCOUNT_GROUP")= (SELECT B."Account_age",B."Balance"
FROM Account_at_DBL_NO_GCMS B
WHERE A."ACCOUNT_KEY"=B."Account_id" AND A."ACCOUNT_NO"= B."Account_no" AND A."CUSTOMER_KEY"=B."Customer_id" AND A."ACCOUNT_SOCIAL_SECURITY_NUMBER"=B."SSN" AND B."ETL_ACTION_FLAG"='U' and B."Account_id" <5) WHERE A."ACCOUNT_KEY"<5

it gives the following error:-

ERROR at line 47:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
ORA-02063: preceding line from DBL_NO_GCMS

I am not been able to find out the Cause of error as in trace file,it does not giving any cause or error number other than ORA-28500.

as I fired the following Query It worked Fine:-

UPDATE ACCOUNT A
SET ("ACCOUNT_AGE")= (SELECT B."Account_age" FROM Account_at_DBL_NO_GCMS B
WHERE A."ACCOUNT_KEY"=B."Account_id" AND A."ACCOUNT_NO"= B."Account_no" AND A."CUSTOMER_KEY"=B."Customer_id" AND A."ACCOUNT_SOCIAL_SECURITY_NUMBER"=B."SSN" AND B."ETL_ACTION_FLAG"='U' and B."Account_id" <5) WHERE A."ACCOUNT_KEY"<5
SQL> / 4 rows updated.

and

The above two Queries are Working fine.Please Look into the Problem and Kindly let me Know Where I am doing Wrong,as It is very Urgent,

Thank you in advance.

Regards
Lovkesh Received on Mon Jan 16 2006 - 02:44:26 CST