Re: Copy from DB to DB within PL/SQL?

Mike Krolewski wrote:

> In article <bc697ts3df34inchb829cl3bjf2g0upn9n_at_4ax.com>,
> Stephen Pennine <spennine_at_home.com> wrote:
> > Greetings group members, here's a new query.
> >
> > We have 2 Oracle DB's in production, 1 at 734, and another at 815. I
> > know both should be at 815, but have no control over that situation.
> > I'm just a techie... and I've told them this before.
> >
> > We'd like to write a PL/SQL procedure be able to copy from the 734 DB
> > to the 815 DB.
> > The procedure will run on the 815 machine; it would connect to the
> > 734, get the data it needed, and bring it back to the 815.
> > (Running on the 734 is an option, but our area owns the 815, and it's
> > really our project, so we don't want to be writing code in someone
> > elses application area)
> > The procedure will be run nightly under the control of the Oracle job
> > queue, via the DBMS_JOB package.
> >
> > We know of the SQL*Plus COPY <FROM/TO> command, but this is specific
> > to SQL*Plus, and as such, won't run under PL/SQL.
> > We also know that we could EXPORT the data from the 734 machine, and
> > the 815 machine could IMPORT it. However, compatibility issues aside,
> > these boxes are in different business areas, and requesting resources
> > from that area would slow our initiative in addition to the fact that
> > IMPORT/EXPORT is woefully inefficient.
> >
> > Is there a way within 815 PL/SQL to connect to other DB's and move
> > data between? Our project needs this functionality to be automated
> > and efficient. We have lots of data, and average equipment.
> >
> > -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
> > http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
> > -----== Over 80,000 Newsgroups - 16 Different Servers! =-----
> >
>
> You should be able to write the command in PL/SQL.
>
> A possible exception is the LONG data types. We tried various attempts
> at transfering tables with LONG in it. PL/SQL limit on LONG is about
> 32K. If your longs are greater than that, they will not transfer.
>
> LONG will transfer during export/import. It will also work in proC (
> building the appropriate buffer size ). I believe the 'copy from' will
> also work with LONGs.
>
> You can write SQLPlus queries with connections via db_links. This will
> work also.
>
> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rii.com
> Usual disclaimers
>
> Sent via Deja.com
> http://www.deja.com/

Another possibility, although not using PL/SQL, would be to write a cron job that calls sqlplus using the copy command. You could then still do everything on your side as long as you have a link to the database in 7.3.4.

Mike Shoop Received on Mon Jan 29 2001 - 20:14:26 CET