Re: [Q] How 2 automate stored procedure schema change?

al_at_spots.ab.ca (Allen C. Pomeroy) wrote:

Here is a sql*plus script that will copy a procedure/function (but not a package/package body) from one schema to another. In order for it to work, the owner of the procedure/function must grant execute on it to the copier. This script reads the code out of the data dictionary, adds a 'create or replace' to the front of it and executes the create or replace procedure/function command. To allow it to work for packages/bodies you would need to add a third param, the type of object being copied (since package specs/bodies have the same name).

Hope this helps

• migrate.sql ----------------------------- REM usage: _at_migrate FROM_USER PROCEDURE_NAME REM column oc noprint column oc2 noprint set heading off set feedback off set verify off spool tmp.sql select 1 oc, 1 oc2, 'create or replace' from dual union select 2 oc, line oc2, text from all_source where owner = upper('&1') and name = upper('&2') union select 3 oc, 1 oc2, '/' from dual order by 1, 2 / spool off _at_tmp.sql ------------ EOF ------------------------------------------

>Have a look at SQL*PAD from ObjectWorks, I have used it to migrate
>both stored procs and triggers from a development user in one
>instance to another user in another instance all by drag-and-drop.
 

>>
>>I would love to be able to have an automated way (stored proc for
>>example) that would read the code out of the catalog and then
>>recompile it under the CDAS schema.
 

>I don't think there is a scripting language for the tool, but the
>simple drag-and-drop sure beats the tar out of exporting, ftp'ing,
>and recompiling manually. (I can select many at once to operate on).
 

>You can get ahold of them at info_at_objectworks.com or
>(403)237-7333.
 

>Hope this helps.
>...Al
Received on Mon Jul 03 1995 - 00:00:00 CEST