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;
No comments:
Post a Comment