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

Answered by: Chris Saxon - Last updated: November 19, 2020 - 9:37 am UTC

Category: Database - Version: 11.2

Viewed 10K+ 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 we 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

and you rated our response

  (8 ratings)

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

Reviews

Insert , update & delete over db link

October 14, 2016 - 12:44 am UTC

Reviewer: Ranjeet Suvarna from Tokyo

Thank you Cris
Appreciate your help

Please vote your conscience
HRC


What did you mean

May 25, 2017 - 3:17 pm UTC

Reviewer: Samuel Ardila from Bogota, Colombia.

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

Followup  

May 26, 2017 - 1:20 am UTC


"on a remote non-Oracle Database"
             ##########


How do i insert update and delete using db link

August 04, 2017 - 12:41 pm UTC

Reviewer: Ken Fischer from Raleigh, NC

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

Followup  

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

November 08, 2019 - 12:41 pm UTC

Reviewer: Harshada from India

I m getting error missing values keyword if i use the given query
Chris Saxon

Followup  

November 08, 2019 - 3:28 pm UTC

Use which query?

More detail

November 08, 2019 - 3:41 pm UTC

Reviewer: Chuck Jolley from OKC, OK USA

"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

Followup  

November 11, 2019 - 1:52 am UTC

Are you using a full gateway product or just heterogeneous services ?

Plain Jane

November 11, 2019 - 8:40 pm UTC

Reviewer: Chuck Jolley from OKC, OK USA

Just what comes free out of the box. And using the MS ODBC driver for Linux.

Chris Saxon

Followup  

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.

November 12, 2019 - 3:59 pm UTC

Reviewer: Chuck Jolley from OKC, OK USA

Thanks, that's interesting.

But how to commit

November 19, 2020 - 2:39 am UTC

Reviewer: Radi soufan from Jordan

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

Followup  

November 19, 2020 - 9:37 am UTC

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