Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Insert into a table in another database via db link

Posted on 2006-07-19
8
Medium Priority
?
15,529 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 400 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

705 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