ORA-02070: Database Does Not Support Operation - Fix Remote SQL

ORA-02070: Database Does Not Support Operation in This Context

Error Overview

Error Text: ORA-02070: database string does not support string in this context

The ORA-02070 error is raised when Oracle attempts to push an operation or SQL construct to a remote database through a database link, but the remote database (or the Oracle Gateway mediating the connection) does not support that operation. The error message includes the name of the remote database and the specific operation or SQL function that was rejected.

This error is most common in heterogeneous environments where Oracle connects to non-Oracle databases (SQL Server, MySQL, PostgreSQL, IBM Db2, Sybase, etc.) via Oracle Database Gateway (formerly Oracle Transparent Gateway or Generic Connectivity/ODBC). However, it can also occur between two Oracle databases when one is significantly older than the other and a newer SQL feature is pushed down to an older remote Oracle instance.

Unlike ORA-02064 which signals a categorically unsupported operation type, ORA-02070 is specifically about a particular SQL function, operator, or construct that the remote database’s query engine does not recognize or cannot execute.

Common Causes

1. Oracle-Specific Functions Pushed to Non-Oracle Databases

• Functions like DECODE, NVL, TO_DATE, TRUNC, SYSDATE, ROWNUM are Oracle-specific
• When Oracle’s query optimizer “pushes” a WHERE clause or SELECT expression to the remote database, the remote engine cannot recognize these functions
• The remote database (SQL Server, MySQL, etc.) uses different function names and syntax

2. Oracle Analytic and Window Functions Over Heterogeneous Links

• ROW_NUMBER() OVER (...), RANK() OVER (...), LAG(), LEAD() may not be translatable by the gateway
• Even if the remote database supports window functions natively, the gateway may not translate the syntax
• Oracle 23ai JSON functions and new SQL features are unlikely to be translatable via older gateways

3. Oracle SQL Syntax Not Supported by the Gateway Translation Layer

• CONNECT BY hierarchical queries cannot be translated for non-Oracle databases
• MODEL clause, PIVOT, UNPIVOT are Oracle-specific
• MERGE statement syntax differs significantly between databases and may not translate
• RETURNING clause in DML statements

4. Data Type Incompatibilities

• Mapping Oracle NUMBER to the remote database’s numeric types may fail for edge cases
• Oracle DATE (which includes time) vs SQL Server DATE (date only) creates translation issues
• Oracle VARCHAR2 semantics differ from SQL Server VARCHAR in null handling and trailing spaces
• CLOB, BLOB, and XMLTYPE have no universal mapping in heterogeneous gateways

5. Older Remote Oracle Database Version

• Oracle 19c local database attempting to push LATERAL join syntax to a remote 11g database
• New SQL features introduced in 12c/18c/19c/21c/23ai may be rejected by older remote Oracle instances
• Optimizer query transformation pushing a new construct that an older database cannot parse

6. Capability Limitations of the Specific Oracle Gateway

• Oracle Database Gateway for SQL Server has a defined capability table; unsupported functions trigger ORA-02070
• Oracle Database Gateway for ODBC (Generic Connectivity) has the most restrictive capability set
• Some capabilities are disabled by default in gateway initORACLE_SID.ora and must be explicitly enabled

Diagnostic Queries

Identify the Remote Database and Link

-- Find which database link caused the error
SELECT
  owner,
  db_link,
  username,
  host,
  created
FROM dba_db_links
WHERE db_link = UPPER('failing_link_name');

-- Check if it is a heterogeneous (non-Oracle) link
SELECT
  db_link,
  username,
  host,
  CASE
    WHEN UPPER(host) LIKE '%HS%'      THEN 'Likely Heterogeneous'
    WHEN UPPER(host) LIKE '%GW%'      THEN 'Likely Gateway'
    WHEN UPPER(host) LIKE '%ODBC%'    THEN 'ODBC Gateway'
    WHEN UPPER(host) LIKE '%SQLSVR%'  THEN 'SQL Server Gateway'
    ELSE 'Oracle or Unknown'
  END AS link_type
FROM dba_db_links
ORDER BY db_link;

Query Remote Database Capabilities (Heterogeneous Links)

-- View what the remote database/gateway supports
SELECT
  capability_name,
  capability_description,
  is_supported,
  is_updateable
FROM v$hs_capability@failing_link_name
ORDER BY is_supported DESC, capability_name;

-- Check specific capability
SELECT
  capability_name,
  is_supported
FROM v$hs_capability@failing_link_name
WHERE capability_name IN (
  'QUERY_REWRITE_CAPABLE',
  'JOIN_CAPABLE',
  'SUBQUERY_CAPABLE',
  'LIKE_OPERATOR',
  'NOT_LIKE_OPERATOR',
  'GROUP_BY_CAPABLE',
  'ORDER_BY_CAPABLE',
  'FUNCTIONS',
  'PROCEDURE_CALLS'
);

Identify the Specific SQL Being Rejected

-- Find the SQL that triggered ORA-02070
SELECT
  sql_id,
  SUBSTR(sql_text, 1, 500) AS sql_text,
  executions,
  last_active_time,
  parsing_schema_name
FROM v$sql
WHERE sql_text LIKE '%@failing_link_name%'
  AND last_active_time > SYSDATE - 1/24
ORDER BY last_active_time DESC
FETCH FIRST 10 ROWS ONLY;

Verify Remote Oracle Database Version (for Oracle-to-Oracle Issues)

-- Check remote Oracle version
SELECT banner FROM v$version@remote_oracle_link;

-- More detailed version information
SELECT
  version,
  version_full,
  startup_time
FROM v$instance@remote_oracle_link;

Check the Heterogeneous Services Data Dictionary

-- View heterogeneous services parameters for the link
SELECT
  param_name,
  param_value
FROM v$hs_parameter@failing_link_name
ORDER BY param_name;

-- View HS session information
SELECT
  hs_session_id,
  db_link,
  agent_name,
  machine,
  program,
  logon_time
FROM v$hs_session
ORDER BY logon_time DESC;

Check Alert Log for Gateway Errors

-- Review recent ORA-02070 occurrences in the alert log
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-02070%'
   OR message_text LIKE '%heterogeneous%'
   OR message_text LIKE '%gateway%'
ORDER BY originating_timestamp DESC
FETCH FIRST 30 ROWS ONLY;

Step-by-Step Resolution

1. Identify the Exact Operation Oracle Is Trying to Push Down

Enable heterogeneous services tracing to see exactly what SQL Oracle sends to the remote database:

-- Enable gateway tracing for the session
ALTER SESSION SET events '10841 trace name context forever, level 3';

-- Run the failing query
SELECT col1 FROM remote_table@failing_link WHERE TRUNC(date_col) = TRUNC(SYSDATE);

-- Turn off tracing
ALTER SESSION SET events '10841 trace name context off';

-- Find the trace file
SELECT value FROM v$parameter WHERE name = 'diagnostic_dest';

Review the trace file to see which specific function or construct the gateway rejected.

2. Rewrite the Query to Avoid Oracle-Specific Functions

Replace Oracle-specific functions with portable equivalents or move the computation to the local database:

-- BAD: Oracle-specific TRUNC pushed to remote SQL Server
SELECT col1
FROM remote_table@sqlserver_link
WHERE TRUNC(date_col) = TRUNC(SYSDATE);

-- GOOD: Use ANSI-compatible expressions, or compute locally and bind as a value
SELECT col1
FROM remote_table@sqlserver_link
WHERE date_col >= TRUNC(SYSDATE)
  AND date_col < TRUNC(SYSDATE) + 1;

-- BETTER: Bind the dates to prevent pushdown of the function
SELECT col1
FROM remote_table@sqlserver_link
WHERE date_col >= :v_start_date
  AND date_col < :v_end_date;

3. Use the NO_MERGE or NO_PUSH_PRED Hints to Prevent Pushdown

Oracle’s optimizer may push predicates or expressions to the remote database automatically. Hints can prevent this:

-- Force Oracle to fetch all data locally and apply the filter locally
SELECT /*+ NO_MERGE(r) NO_PUSH_PRED(r) */ col1
FROM (SELECT col1, date_col FROM remote_table@failing_link) r
WHERE TRUNC(r.date_col) = TRUNC(SYSDATE);

By wrapping the remote query in an inline view and preventing merge/predicate push, Oracle fetches the raw data and applies the Oracle-specific function locally.

4. Use DBMS_HS_PASSTHROUGH for Native Remote SQL

For non-Oracle databases, use passthrough SQL to send the remote database’s native SQL directly:

DECLARE
  v_cursor  INTEGER;
  v_col1    VARCHAR2(200);
  v_rows    INTEGER;
BEGIN
  v_cursor := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@sqlserver_link;

  -- Send SQL Server native syntax directly, bypassing Oracle translation
  DBMS_HS_PASSTHROUGH.PARSE@sqlserver_link(
    v_cursor,
    'SELECT col1 FROM remote_table WHERE CAST(date_col AS DATE) = CAST(GETDATE() AS DATE)'
  );

  LOOP
    v_rows := DBMS_HS_PASSTHROUGH.FETCH_ROW@sqlserver_link(v_cursor, FALSE);
    EXIT WHEN v_rows = 0;

    DBMS_HS_PASSTHROUGH.GET_VALUE@sqlserver_link(v_cursor, 1, v_col1);
    DBMS_OUTPUT.PUT_LINE('col1: ' || v_col1);
  END LOOP;

  DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@sqlserver_link(v_cursor);
END;
/

5. Create a View on the Remote Database to Abstract Complex Logic

For non-Oracle remote databases, create a view in the remote database that pre-computes complex expressions using the remote database’s native functions:

-- On the REMOTE SQL Server database (created by the SQL Server DBA):
-- CREATE VIEW oracle_accessible_view AS
-- SELECT
--   id,
--   col1,
--   CAST(date_col AS DATE) AS date_only,
--   CONVERT(VARCHAR(10), date_col, 120) AS date_string
-- FROM remote_table;

-- On the LOCAL Oracle database: query the view through the link (no functions to translate)
SELECT col1
FROM oracle_accessible_view@sqlserver_link
WHERE date_string = TO_CHAR(SYSDATE, 'YYYY-MM-DD');

6. Configure Gateway Capabilities in the Init File

For Oracle Gateway, some capabilities can be explicitly configured in the gateway initialization file (init<SID>.ora):

# In $ORACLE_HOME/hs/admin/initSQLSVR.ora (example for SQL Server gateway)
HS_FDS_CONNECT_INFO=sqlserver_dsn
HS_FDS_TRACE_LEVEL=OFF

# Enable or disable specific capabilities
HS_KEEP_REMOTE_COLUMN_SIZE=TRUE
HS_NLS_NCHAR=UCS2
HS_LANGUAGE=AMERICAN_AMERICA.AL32UTF8

# For ODBC generic connectivity — restrict what Oracle tries to push
HS_CALL_NAME=hs_call_name

Restart the gateway listener after modifying the init file.

7. For Oracle-to-Oracle Version Gaps: Avoid New Syntax in Remote Queries

When the remote Oracle database is an older version, use only SQL syntax supported by that version:

-- Check remote Oracle version
SELECT banner FROM v$version@old_oracle_link;

-- Avoid 12c+ syntax when targeting 11g
-- BAD for 11g remote: Uses FETCH FIRST (12c+)
SELECT col1 FROM remote_table@old_oracle_link
FETCH FIRST 10 ROWS ONLY;

-- GOOD for 11g remote: Use ROWNUM (works on all Oracle versions)
SELECT col1 FROM (
  SELECT col1 FROM remote_table@old_oracle_link
  WHERE ROWNUM <= 10
);

Prevention Strategies

1. Document Gateway Capability Limitations at Project Start

Before building applications that use heterogeneous database links, query and document the capabilities of the target gateway:

-- Export all capabilities to a local table for reference
CREATE TABLE hs_capabilities_ref AS
SELECT
  'SQLSERVER_LINK' AS link_name,
  capability_name,
  capability_description,
  is_supported
FROM v$hs_capability@sqlserver_link;

-- Review unsupported capabilities
SELECT capability_name, capability_description
FROM hs_capabilities_ref
WHERE is_supported = 'NO'
ORDER BY capability_name;

2. Isolate Remote Access Behind Local Packages

Create a local PL/SQL package that abstracts all database link access. This package handles all translation and workarounds in one place:

CREATE OR REPLACE PACKAGE remote_data_access AS
  -- Instead of letting application code use raw DB links,
  -- expose clean APIs that handle ORA-02070 workarounds internally
  FUNCTION  get_records_by_date(p_date IN DATE) RETURN SYS_REFCURSOR;
  PROCEDURE insert_record(p_id IN NUMBER, p_val IN VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY remote_data_access AS
  FUNCTION get_records_by_date(p_date IN DATE) RETURN SYS_REFCURSOR AS
    v_rc     SYS_REFCURSOR;
    v_start  DATE := TRUNC(p_date);
    v_end    DATE := TRUNC(p_date) + 1;
  BEGIN
    -- Bind pre-computed values to avoid function pushdown (prevents ORA-02070)
    OPEN v_rc FOR
      SELECT col1 FROM remote_table@sqlserver_link
      WHERE date_col >= v_start
        AND date_col < v_end;
    RETURN v_rc;
  END;
END;
/

3. Use Bind Variables to Prevent Predicate Pushdown

Binding values rather than using expressions in WHERE clauses prevents Oracle from pushing Oracle-specific function calls to the remote database:

-- Instead of this (Oracle may push SYSDATE or TO_DATE to remote):
SELECT * FROM remote_table@sqlserver_link
WHERE created_date > TO_DATE('2024-01-01', 'YYYY-MM-DD');

-- Use this (local variables are bound, not pushed):
DECLARE
  v_cutoff DATE := DATE '2024-01-01';
BEGIN
  -- v_cutoff is a resolved value; Oracle sends the date literal, not the function
  FOR r IN (
    SELECT col1 FROM remote_table@sqlserver_link
    WHERE created_date > v_cutoff
  ) LOOP
    DBMS_OUTPUT.PUT_LINE(r.col1);
  END LOOP;
END;

4. Test All Queries Against the Heterogeneous Link in a Dev Environment First

-- Create a standard test suite for all queries using a heterogeneous link
-- Run this after any query change or Oracle patch application
CREATE OR REPLACE PROCEDURE test_remote_link_queries AS
BEGIN
  -- Test 1: Basic SELECT
  FOR r IN (SELECT 1 AS c1 FROM dual@sqlserver_link) LOOP
    DBMS_OUTPUT.PUT_LINE('Test 1 passed');
  END LOOP;

  -- Test 2: Filtered SELECT with bind variable
  DECLARE v_id NUMBER := 1;
  BEGIN
    FOR r IN (SELECT col1 FROM remote_table@sqlserver_link WHERE id = v_id) LOOP
      DBMS_OUTPUT.PUT_LINE('Test 2 passed: ' || r.col1);
    END LOOP;
  END;
END;
/

5. Keep Oracle Gateways Updated

Ensure Oracle Database Gateway software is updated in line with the Oracle Database server version. Older gateway versions have fewer capabilities and produce more ORA-02070 errors:

-- Check gateway version in alert log or from the agent executable
-- Compare with My Oracle Support for latest gateway patches for your remote database type
SELECT value FROM v$parameter WHERE name = 'diagnostic_dest';
-- Review gateway agent trace files in the diagnostic destination

Diagnostic Scripts

These Oracle Day by Day utility scripts can assist with this type of investigation:

• gvsess.sql — Identify sessions using heterogeneous database links

Related Errors

• ORA-02055 - Distributed update failed; rollback required
• ORA-02063 - Preceding line from database link (wraps errors from remote DB)
• ORA-02064 - Distributed operation not supported
• ORA-02067 - Transaction or savepoint rollback required
• ORA-02069 - GLOBAL_NAMES parameter must be TRUE
• ORA-02049 - Timeout: distributed transaction waiting for lock

Emergency Response

Quick Fixes

1. Prevent Oracle from pushing the failing operation to the remote database

   -- Wrap the remote query in an inline view with NO_MERGE hint
   SELECT /*+ NO_MERGE(r) */ *
   FROM (SELECT * FROM remote_table@failing_link) r
   WHERE TRUNC(r.date_col) = TRUNC(SYSDATE);

2. Use passthrough SQL to bypass Oracle’s translation layer entirely

   DECLARE
     v_cursor INTEGER;
   BEGIN
     v_cursor := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@failing_link;
     DBMS_HS_PASSTHROUGH.PARSE@failing_link(v_cursor, 'SELECT 1');
     DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@failing_link(v_cursor);
     DBMS_OUTPUT.PUT_LINE('Passthrough connection confirmed working');
   END;
   /

3. Kill a runaway session issuing the failing query repeatedly

   ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

Post-Resolution Cleanup

-- Confirm the link is functional after applying the workaround
SELECT SYSDATE FROM dual@formerly_failing_link;

-- Document the capability limitation for future developers
INSERT INTO hs_capabilities_ref (link_name, capability_name, is_supported)
VALUES ('FAILING_LINK', 'TRUNC_FUNCTION', 'NO');
COMMIT;

-- Verify no in-doubt transactions remain from failed attempts
SELECT COUNT(*) FROM dba_2pc_pending;

-- Review whether the gateway needs a patch or version upgrade
SELECT value FROM v$parameter WHERE name = 'diagnostic_dest';
-- Check gateway agent trace files at the diagnostic destination path