Skip to Main Content
  • Questions

  • ORA-02070: database DBLINK does not support subqueries in this context when updating SQL Server via gateway

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, IFTIKHAR.

Asked: May 31, 2020 - 8:48 am UTC

Last updated: June 01, 2020 - 3:31 pm UTC

Version: 11G

Viewed 1000+ times

You Asked

Hi Tom,

I am trying to update MS SQL Server table from Oracle via dblink. Using 11g on Oracle with Gateway HS configuration i can query of SQL Server table from Oracle but cannot update as below is detail error is showing.


Error starting at line : 3 in command -
UPDATE FTEST.PAYMENTH@DBLINK
SET "APPSTATUS_0"=1
WHERE "NUM_0"=  'PPFT10202000001'
Error at Command Line : 5 Column : 17
Error report -
SQL Error: ORA-02070: database DBLINK does not support some function in this context
02070. 00000 -  "database %s%s does not support %s in this context"
*Cause:    The remote database does not support the named capability in
           the context in which it is used.
*Action:   Simplify the SQL statement.




In SQL Server table "Paymenth" having NUM_0 & UPDTICK_0 columns. NUM_0 is NVARCHAR where UPDTICK_0 is INTEGER. I can use UPDTICK_0 in where condition and can proceed for the update but cannot use NVARCHAR in where condition and getting above error. see the below example with INTEGER column condidition in where clause.

UPDATE FTEST.PAYMENTH@DBLINK
SET "APPSTATUS_0"=2
WHERE "UPDTICK_0"=9

85 rows updated.


please advise to use NVARCHAR in where clause while updating MS SQL Server table from Oracle.

and Chris said...

There is a known bug related to this issue. See MOS note 457473.1.

We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database