Skip to Main Content
  • Questions

  • How do i insert update and delete using db link

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Ranjeet.

Asked: October 13, 2016 - 7:43 am UTC

Last updated: November 19, 2020 - 9:37 am UTC

Version: 11.2

Viewed 50K+ times! This question is

You Asked

There are two instance on Unix server Instance A and Instance B
i want to update one table on Instance B from Instance A
What is the best way ?

and Chris said...

You write DML over a db link the same way you'd write it if all the tables were local. You just add @dblink to the remote tables!

All the tables are local. So to simulate remote access loopback points back to the same DB. 

create table ta (
  x int,
  y int
);

insert into ta values (1, 1);
insert into ta values (2, 1);
commit;

create table tb as
  select * from ta
  where  1=0;

create database link loopback using 'orcl';

insert into tb 
  select x, 2 from ta@loopback;

select * from tb;

X  Y  
1  2  
2  2  

update tb loc
set    y = (
  select y from ta@loopback rem
  where  rem.x = loc.x
);

select * from tb;

X  Y  
1  1  
2  1 

delete tb loc
where  exists (
  select null from ta@loopback rem
  where  rem.x = loc.x
);

select * from tb;

 0 rows selected


There are some restrictions regarding what you can do over DB links though. For example:

Data manipulation language statements that update objects on a remote non-Oracle Database system cannot reference any objects on the local Oracle Database. 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;


If you want to know more about these along with creating and using DB links, check out the docs:

http://docs.oracle.com/database/121/ADMIN/ds_admin.htm#ADMIN029

Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.



Connor McDonald
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.

Chris Saxon
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
Chris Saxon
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;
/


Connor McDonald
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.

Chris Saxon
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?
Chris Saxon
November 19, 2020 - 9:37 am UTC

Committing on the local database also commits changes on the remote database.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.