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.