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

[Quoted] 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/