Tuesday 29 July 2014

PL/SQL: Procedure to copy package with another name

Here's simple procedure build upon custom listagg implementation (see Custom listagg function using cursor to bypass ORA-01489) that creates a copy of existing package with a new name.
It comes very handy when you have several versions of package (say production and development) and constantly switch between them.
It simply obtains package definition and body and then recreates them with a new name.
procedure cp_package( a_owner in varchar2, a_name in varchar2, a_new_name in varchar2 ) as 
  v_head clob;
  v_body clob;
begin
  select regexp_replace( 
    pk_utils.listagg_clob( 
        cursor (
          select text
          from all_source
          where name like upper( a_name ) 
            and owner like upper( a_owner )
            and type like 'PACKAGE'
          order by line
        )
        , ''
      )
      , a_name
      , a_new_name
      , 1
      , 0
      , 'imn'
    )
    , regexp_replace( 
    pk_utils.listagg_clob( 
        cursor (
          select text
          from all_source
          where name like upper( a_name ) 
            and owner like upper( a_owner )
            and type like 'PACKAGE BODY'
          order by line
        )
        , ''
      )
      , a_name
      , a_new_name
      , 1
      , 0
      , 'imn'
    )
  into v_head
    , v_body
  from dual
  ;

  execute immediate 'create or replace ' || v_head;  
  execute immediate 'create or replace ' || v_body;  
  
  dbms_output.put_line( 'Successfully copied package ' || upper( a_name ) || ' to ' || upper( a_new_name ) );
end;