Re: Q: copy table functionality in PL/SQL?

From: Connor McDonald <mcdonald.connor.cs_at_bhp.com.au>
Date: Wed, 24 Jun 1998 16:06:20 +0800
Message-ID: <3590B37C.296@bhp.com.au>

rune.slinning_at_bigfoot.com wrote:
>
> Hi!
>
> I need functionality to copy large tables from one schema to another, and it
> would be nice if I could use the SQL*Plus COPY command from within a PL/SQL
> procedure. I want to copy the tables in PL/SQL so I can hanle
> errors/exceptions.
>
> The tables I'll be copying have 450 000+ rows so I need a big rollback
> segment to copy this with an insert statement like: "insert into ... select *
> from ..."
>
> Is there anybody out there who have implemented a copy procedure/function
> using PL/SQL??
>
> Rune
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading

Quick and Dirty One...

---

procedure gimme_a_copy(old_table varchar2, new_table varchar2) is is

    c     integer;
    s        integer;

begin

    c := dbms_sql.open_cursor;
    dbms_sql.parse( c,'create table '||new_table||' as select * from '||

                        old_table||' unrecoverable',dbms_sql.native );     s := dbms_sql.execute(c);
    dbms_sql.close_cursor(c);
exception when others then

    dbms_sql.close_cursor(c);
    raise;
end;

--

---

Connor McDonald
BHP Information Technology
Perth, Western Australia
"These views mine not BHP..etc etc"

"The only difference between me and a madman is that I am not mad." Received on Wed Jun 24 1998 - 03:06:20 CDT