Re: Copying a Package from with a Stored Procedure

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 1996/12/19
Message-ID: <32b9c52c.29415146@dcsun4>#1/1

Justin,

if you use the following procedure:

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;                                                                            
/                                                                               

And you have

• access to the other procedure directly (not via a role)
• create procedure granted directly to you (not via a role)

You could (not syntax checked below, writing this from scratch, the above routine definitely works...)

procedure copyproc( p_owner in varchar2, p_name in varchar2 ) is

   l_cmd varchar2(32000);
   l_last_type varchar2(30);
begin

   for x in ( select text, type from all_source

              where owner = upper(p_owner) and name = upper(p_name)
              order by type, line ) 
   loop
      if ( l_last_type is null ) then
         l_last_type := x.type;
      end if;
      if ( l_last_type <> x.type ) then
         execute_immediate( l_cmd ); 
         l_cmd := NULL;
      end if;
      l_cmd := l_cmd || x.text;

  end loop;
  execute_immediate( l_cmd );
end;
/

Again, you need direct access when using the stored procedure, not via a role...    

On Thu, 19 Dec 1996 15:49:47 -0500, Justin Fidler <justin_at_club.cc.cmu.edu> wrote:

>
>Is there an easy way to copy a package body and spec, from within a
>stored procedure (i.e., without using SQL*Plus, etc.)? I can get the
>source from the data dictionary views, but how do I pass it to the
>compiler to compile it as a package? Can I just insert into the data
>dictionary?
>
>My ultimate goal is to have a stored procedure that will copy all the
>packages owned by another user into my own schema, so I can mess around
>with them without damaging production code. I want this to be an
>automated process entirely within the datbase, so I can perform the
>transfer on a routine basis, based on certain events.
>
>Thanks,
>Justin Fidler
>justin+@cmu.edu / justin_at_intraactive.com
>
>

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com

• Check out Oracle Governments web site! ----- Follow the link to "Tech Center" and then downloadable Utilities for some free software...

  ---

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Thu Dec 19 1996 - 00:00:00 CST