Skip to Main Content
  • Questions
  • insert into remote database without using FORALL

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, naveen.

Asked: February 22, 2018 - 9:08 am UTC

Last updated: March 18, 2019 - 9:55 am UTC

Version: oracle 10g

Viewed 1000+ times

You Asked

I have a table with records more than million in a table in database1. I need to populate another table in database2 with some filters.
BuLK forall insert is not allowed as these are remote DB.

I need suggestion to populate remote database table , successful records to get populated in database2 while erros is being captured in another table or file.

performance is a main constraint as forall cannot be used as these are remote database population. Kindly suggest valuable knowledge

and Connor said...

The easiest way is to just insert:

--
-- db1
--
SQL> create table t
  2  as
  3  select owner, object_name, object_type
  4  from dba_Objects
  5  where 1=0;

Table created.

--
-- db2
--
SQL> create table t
  2  as
  3  select owner, object_name, object_type
  4  from dba_Objects;

Table created.

SQL>
SQL>
SQL> insert into t@np12
  2  select * from t;

79078 rows created.

SQL> commit;

Commit complete.


If the source data is in a PLSQL structure, then insert into a global temporary table and then use that as a source to insert.

SQL> create or replace
  2  type the_row as object (
  3    owner varchar2(128),
  4   object_name   varchar2(128),
  5   object_type varchar2(128)
  6  );
  7  /

Type created.

SQL>
SQL> create or replace
  2  type the_list as table of the_row;
  3  /

Type created.

SQL>
SQL> create global temporary table tmp (
  2    owner varchar2(128),
  3   object_name   varchar2(128),
  4   object_type varchar2(128)
  5  );

Table created.

SQL>
SQL> declare
  2    t the_list := the_list();
  3  begin
  4    --
  5    -- load up my source
  6    --
  7    select the_row(owner, object_name, object_type)
  8    bulk collect into t
  9    from dba_objects;
 10
 11    -- copy to GTT
 12    insert into tmp
 13    select * from table(t);
 14
 15    -- use GTT
 16    insert into t@np12
 17    select * from tmp;
 18  end;
 19  /

PL/SQL procedure successfully completed.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

options

Racer I., February 23, 2018 - 9:41 am UTC

Hi,

For the error handling requirement you can look at

https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9014.htm#BCEGDJDJ

For working in chunks you could try BULK INSERT into the local (Temp) table and then INSERT AS SELECT through the DB-Link. I.e. like Conner said, but in smaller chunks. Could use commits (empties the temp table if so configured) or deleting the temp table if you can't afford intermediate commits.

Yet another options is calling a helper package on the remote site to do the processing, which could fetch from a (reverse) db-link and do regular FORALL inserts.

regards,

reverse db-link

Racer I., February 23, 2018 - 9:58 am UTC

Hi,

Maybe I can put a related question here?

When Oracle executes a statement spanning both sides of a db-link on the remote site you will see new statements there using "@!" as a reverse db-link. Can your own statements use that too or only Oracle itself? I.e. in the above remote procedure scenario? Often there are now official reverse links available.

It would habe to be dynamic (using EXECUTE IMMEDIATE) I guess. Google will not search for "@!"...

regards,
Connor McDonald
February 25, 2018 - 8:06 am UTC

https://asktom.oracle.com/pls/apex/asktom.search?tag=names-for-database-links#3363779400346534109

Only we can use it :-)

Its simply our nomenclature for (as you suspected) picking up information in a reverse direction. Non-documented, non-supported for developer use.

Sceptic

Gh, February 25, 2018 - 8:00 am UTC

In your second solution regarding plsql you mentioned
15 -- use GTT 16 insert into t@np12 17 select * from tmp;

Well you build tmp from dba_objects in same db.
And insert target is remote.

Didn't catch the thing.
insert into t@np12 select * from dba_objects straight ;why using gtt?

Connor McDonald
February 25, 2018 - 8:01 am UTC

You cannot bulk bind/forall insert a plsql table type across a database link. Hence a workaround is to load the plsql data into GTT, and then use that to copy to the remote database.

This worked well.

A reader, March 14, 2019 - 9:14 pm UTC

I wrote a script that refreshes a website by merging changes from the source table into the remote website table. It didn't work because merge doesn't work for bulk collect. I changed it to write to a temp table then merge from the temp to the remote as described in this article.

A test merging into a table on the same server as the source took over two minutes to run (60k records).

Using the temp table technique and merging to the remote ran in 2 seconds!

Wow!
Connor McDonald
March 18, 2019 - 9:55 am UTC

glad we could help

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.