Re: PL/SQL - Need table copy proc

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/09/30
Message-ID: <34315f65.25226313@newshost>#1/1

On Tue, 30 Sep 1997 13:33:49 -0500, Cherie Machler <n32cmn_at_is.nwa.com> wrote:

>Kind people,
>
>I need a PL/SQL proc that reads the records
>from one table and writes them to another table.
>
>I've got one somewhere but don't have time to
>drive home and locate it. If anyone could help
>me out by emailing a copy to me, I'd greatly
>appreciate it.
>
>Thanks,
>
>Cherie
>
>Please email to n32cmn_at_is.nwa.com as I'm not sure whether
>my return-to address is correct.

Using either of the 2 functions/procedures below, it is pretty easy. For example, if the table you are copying to doesn't exist, you can:

SQL> exec execute_immediate_proc( 'create table Newemp as select * from emp' );

If the table does exist, you could:

SQL> begin
   >   dbms_output.put_line( 
   >            execute_immediate('insert into newemp select * from emp' ) || 
   >            ' Rows copied' );
   > end;
   > /

Remember, roles are not enabled in procedures. To do the first one, you will need the create table privelege granted directly to you and in both, you will need to either own the base table you are copying from or have select granted direct to you and either own the base table you are copying to or have insert granted directly to you.

create or replace function execute_immediate( stmt in varchar2 )

return number                                                                   
as                                                                              
    exec_cursor     integer default dbms_sql.open_cursor;                       
    rows_processed  number  default 0;                                          
begin                                                                           
    dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );                        
    rows_processed := dbms_sql.execute(exec_cursor);                            
    dbms_sql.close_cursor( exec_cursor );                                       
    return rows_processed;                                                      
exception                                                                       
    when others then                                                            
      if dbms_sql.is_open(exec_cursor) then                                     
        dbms_sql.close_cursor(exec_cursor);                                     
      end if;                                                                   
      raise;                                                                    
end;                                                                            
/                                                                               

create or replace procedure execute_immedate_proc( stmt in varchar2 ) as

        l_dummy number;
begin

        l_dummy := execute_immediate( stmt );
end;
/

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Sep 30 1997 - 00:00:00 CDT