Copying data from one database to another in plsql block

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Copying data from one database to another in plsql block

Postby Jakob » Tue Jul 09, 2013 10:55 am

Hi Tim

What are the options in copying data from one database to another database in plsql block?

In sqlplus copy from command does transfer data from one database to another, however in plsql is it possible to execute copy from?

I have tried
Code: Select all
execute immediate 'copy from test/test@test insert emp using select * from emp';
which resulted in
Code: Select all
ORA-00900: invalid SQL statement


The following is an option, but how can I specify the source target which is another database and I will be executing my scripts from target database

Code: Select all
TYPE allrows IS TABLE OF emp%ROWTYPE;
rows$        allrows;

BEGIN
   select   *
        bulk   collect into rows$
        from   emp;

      forall x in rows$.first .. rows$.last
         insert into   emp
              values   rows$ (x);
END;


Any help is highly appreciable.

P.S. Any solution available other than creating a dblink to source database

Thanks
Jakob
Member
 
Posts: 4
Joined: Mon Jun 17, 2013 1:19 pm

Re: Copying data from one database to another in plsql block

Postby Tim... » Tue Jul 09, 2013 11:45 am

Hi.

The best option if it must be done inside PL/SQL is to use a database link.

You can then do:

Code: Select all
INSERT INTO destination
SELECT * FROM source@dblink;


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17953
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 1 guest