"ORA-02070: database xxx does not support some function in this context" error when trying to update tables via Oracle through dg4odbc

Live Connectivity CData Drivers
, , , ,
1

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?

2

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 affectedBEGIN  -- 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.