Insert , update & delete over db link
Ranjeet Suvarna, October 14, 2016 - 12:44 am UTC
Thank you Cris
Appreciate your help
Please vote your conscience
HRC
What did you mean
Samuel Ardila, May 25, 2017 - 3:17 pm UTC
Hi Chris,
"For example, a statement such as the following will cause an error to be raised:
INSERT INTO remote_table@link as SELECT * FROM local_table;"
i am doing that and no error had raised so far. (i adjust the data to fix all constraints in remote table before to insert).
So what did you mean?
Best Regards.
May 26, 2017 - 1:20 am UTC
"on a remote non-Oracle Database"
##########
How do i insert update and delete using db link
Ken Fischer, August 04, 2017 - 12:41 pm UTC
I was actually able to execute insert commands of the type:
INSERT INTO remote_table@link as SELECT * FROM local_table;
I had to issue "COMMIT" on the local side before the data should show up in the remote database.
August 04, 2017 - 3:30 pm UTC
And is the remote database Oracle or something else?
I m getting error missing values keyword if i use the given query
Harshada, November 08, 2019 - 12:41 pm UTC
I m getting error missing values keyword if i use the given query
November 08, 2019 - 3:28 pm UTC
Use which query?
More detail
Chuck Jolley, November 08, 2019 - 3:41 pm UTC
"Data manipulation language statements that update objects on a remote non-Oracle Database system cannot reference any objects on the local"
Could you go into more detail about this? We have production code that updates an MS SQLServer database through Linux ODBC and Gateway, although using a cursor instead of a direct update, that works just fine.
eg.
CREATE OR REPLACE procedure TAX.cert_7_update_assessor (v_tax_year in varchar2)
as
v_cnt number;
begin
v_cnt := 0;
for i in (select acct_no, original_amount
from tax.accounts
where tax_year = v_tax_year)
loop
update dbo."tbltaxcertification"@assrsql a
set "tre_tax_amount" = i.original_amount
where a."acct_no" = i.acct_no;
v_cnt := v_cnt + 1;
end loop;
commit;
dbms_output.put_line(to_char(v_cnt) || ' rows updated.');
end;
/
November 11, 2019 - 1:52 am UTC
Are you using a full gateway product or just heterogeneous services ?
Plain Jane
Chuck Jolley, November 11, 2019 - 8:40 pm UTC
Just what comes free out of the box. And using the MS ODBC driver for Linux.
November 12, 2019 - 11:31 am UTC
When you use the cursor for loop, the update isn't referencing any local objects! The references to i.* columns in the update become bind variables.
So you don't hit the restriction in your example update.
Chuck Jolley, November 12, 2019 - 3:59 pm UTC
Thanks, that's interesting.
But how to commit
Radi soufan, November 19, 2020 - 2:39 am UTC
If I did the above insert statement... on remote and local oracle db
How can I commit the inserted data...
Isn't commit command will be for local db... so how can i commit on the remote db?
November 19, 2020 - 9:37 am UTC
Committing on the local database also commits changes on the remote database.