Skip to main content

When connected to my data source as a Remote Oracle Database via the ODBC Driver, I run into the following error when trying to perform inserts or updates into my database:

 

Error ORA-02070: database xxx does not support <operation> in this context

or

ORA-02070: database xxx does not support some function in this context

 

I am connected via the Database Gateway dg4odbc. How can I resolve this issue?

Once you are connected to your ODBC Data source via an Oracle tool like SQL Plus, you can perform updates by using a query passthrough approach to push the update to the data source. Try pushing your update in the following format:

DECLARE
c INTEGER; -- Cursor ID for passthrough
nr INTEGER; -- Number of rows affected
BEGIN
-- Open a passthrough cursor on the remote DB (via DB link)
c := DBMS_HS_PASSTHROUGH.OPEN_CURSOR@<db_link>;

-- Prepare the SQL statement with placeholders
DBMS_HS_PASSTHROUGH.PARSE@<db_link>(c,
'<your SQL statement with placeholders, e.g., UPDATE ... SET ... WHERE ...>');

-- Bind variables (starting at position 1)
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@<db_link>(c, 1, <value1>);
DBMS_HS_PASSTHROUGH.BIND_VARIABLE@<db_link>(c, 2, <value2>);
-- Add more binds as needed...

-- Execute the non-query (INSERT/UPDATE/DELETE)
nr := DBMS_HS_PASSTHROUGH.EXECUTE_NON_QUERY@<db_link>(c);

-- Optionally display number of affected rows
DBMS_OUTPUT.PUT_LINE(nr || ' rows updated');

-- Close the passthrough cursor
DBMS_HS_PASSTHROUGH.CLOSE_CURSOR@<db_link>(c);
END;

You can use this procedure to execute a parameterized SQL statement (e.g., an UPDATE) on a remote non-Oracle database via a database link. 

Please make sure to replace <db_link> with the actual name of your database link (e.g., @myd365db).

This template supports UPDATE, INSERT, or DELETE statements.