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

Insert into a table in another database via db link

Posted on 2006-07-19
Last Modified: 2010-07-27

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.
Question by:jyhuang
  • 3
  • 2
LVL 16

Expert Comment

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;


Accepted Solution

bpgergo earned 100 total points
ID: 17139690

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

Did you try one of these? Results?

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

--this'll be faster and but consume more memory if you have many records in table_A
  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;
  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)
      (col1s(i), col2s(i), col3s(i);
  end if;

Author Comment

ID: 17139850
The PL*SQL block works. But do you know why the insert statement does not work?
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

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?


Author Comment

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

Expert Comment

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.)

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle - SQL Parse String 5 45
Oracle - SQL Query with Function 3 53
oracle date format checking 7 26
How to drop system generated virtual column in a table in12c 15 48
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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 information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

840 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