CODEWITHSUNDEEP
sql-serveroracle-databaseplsqloracle11gheterogeneous-services
Kent Anderson
Kent Anderson

Reputation: 486

Heterogeneous Oracle to SQL Server: Using "IS NULL" in Where clause for Update statement

I am having trouble with using the IS NULL condition in an Update statement executed on an SQL Server 2000 database via an Oracle Database (11.2.0.4) using an ODBC heterogeneous connection.

Example of update statement that will not work. 

UPDATE TABLENAME@RemoteSQLServer2000
  SET "ColumnName" = 'SomeValue'
WHERE "AnotherColumnName" IS NULL;

Result:

ORA-02070: database RemoteSQLServer2000 does not support IS NULL in this context

However, the following statement works fine:

SELECT *
  FROM TABLENAME@RemoteSQLServer2000
 WHERE "AnotherColumnName" IS NULL;

Does anyone have an idea about what I can do to make this work? Thanks in advance. Let me know if you need more info.

Upvotes: 1

Views: 1248

Answers (2)

ouirmi
ouirmi

Reputation: 1

try this in your where clause instead of where AnotherColumnName is null

Where AnotherColumnName || 'x' = 'x'

Upvotes: 0

Kent Anderson
Kent Anderson

Reputation: 486

Thanks to @MickMnemonic and another consultant, we were able to find a solution.

Oracle has a package that allows you to execute SQL from an application to an SQL Server database. This function will allow you to successfully execute a statement like the one mentioned in the question where you are filtering on NULL values. Oracle Documentation for DBMS_HS_PASSTHROUGH

Following is an example of the new code that works.

DECLARE
   num_rows   INTEGER;
BEGIN
   num_rows :=
      DBMS_HS_PASSTHROUGH.EXECUTE_IMMEDIATE@RemoteSQLServer2000 (
         'UPDATE TABLENAME SET "ColumnName" = ''SomeValue'' WHERE AnotherColumnName IS NULL');
END;
/

Upvotes: 2

Related Questions