This forum is currently locked. You can't register or post questions at this time. (read more)

Copying data from one database to another in plsql block

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests

cron