Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 16165
  • Last Modified:

Insert into a table in another database via db link

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
jyhuang
Asked:
jyhuang
  • 3
  • 2
1 Solution
 
MohanKNairCommented:
>> 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
 
bpgergoCommented:
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
 
jyhuangAuthor Commented:
The PL*SQL block works. But do you know why the insert statement does not work?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
bpgergoCommented:
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
 
jyhuangAuthor Commented:
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
 
bpgergoCommented:
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now