Solved

Insert into a table in another database via db link

Posted on 2006-07-19
8
13,567 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ORA-12560: TNS:protocol adapter error 8 130
Converting a row into a column 2 53
exp/imp 25 75
ORA-02288: invalid OPEN mode 2 31
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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

777 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