Solved

Insert into a table in another database via db link

Posted on 2006-07-19
8
12,992 Views
Last Modified: 2010-07-27
Hi,

I am tring to insert data from table_A in db A into table_B in db B via db link as described below.

insert into table_B@db_link_to_db_b
(col1, col2, col3)
select col1,col2,col3
from table_A

col1 and col2 are VARCHAR2 type, and col3 is number type
I got a strange error. Although col1,col2, and col3 in table_A have values, only value of col3 is  inserted into table B. Both col1 and col2 are blank in table_B. But if I do something like below(using constant value), both ' constant1' and 'constant2' can be inserted into table_B.

insert into table_B@db_link_to_db_b
(col1, col2, col3)
select 'constant1','constant2',col3
from table_A

What do I miss? Thank you.
0
Comment
Question by:jyhuang
  • 3
  • 2
8 Comments
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17139421
>> insert into table_B@db_link_to_db_b(col1, col2, col3) select col1,col2,col3 from table_A

The above statement looks correct. Try this

insert into table_B@db_link_to_db_b(col1, col2, col3) select col1,col2,col3 from table_A where col1 is not null and col2 is not null;



0
 

Accepted Solution

by:
bpgergo earned 100 total points
ID: 17139690
Strange.

Sorry, but are you sure about not having null values in table_A.col1, col2?

Did you try one of these? Results?

begin
  for c in (select * from table_A)
  loop
    insert into table_b@dblink
    (col1, col2, col3)
    values
    (c.col1, c.col2, c.col3);
  end loop;
end;

--this'll be faster and but consume more memory if you have many records in table_A
declare
  type t_num_arr is table of number;
  type t_chr_arr is table of varchar2(4000);
  col1s t_chr_arr; col2s t_chr_arr; col3s t_num_arr;
begin
  select col1, col2, col3
  bulk collect into
  cols1, cols2, cols3
  from table_A;
  if cols1 is not null --it won't
  and cols1.count > 0 then
    forall i in col1s
      insert into table_B@dblink
      (col1, col2, col3)
      values
      (col1s(i), col2s(i), col3s(i);
  end if;
end;
0
 
LVL 1

Author Comment

by:jyhuang
ID: 17139850
The PL*SQL block works. But do you know why the insert statement does not work?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:bpgergo
ID: 17142311
My idea was that there's some trigger on table_B@dblink which sets :new.col1 := null unless :new.col1 = 'constant1'.
If the pl/sql block works then it is more mysterious.

I don't know exactly. Could be:
- in distributed transaction and maybe under other certain circumstences <insert into ... select> does not work /I will try this out 'cause I'm curious/
- it may work in 10g rel2 but not in an old version /i don't really belive in this scenario/
- it's a bug
- ?

Oracle/op. sys. version?

Gergo
0
 
LVL 1

Author Comment

by:jyhuang
ID: 17143410
There is no trigger on table_B.  table_A is in Oracle 8i and table_B is in Oracle 9i. The OS is AIX 4.x
0
 

Expert Comment

by:bpgergo
ID: 17145352
Well, I tried it out and it works for me fine with a dblink pointing from a 10g to a 9i (different locations.)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now