how to change the name of all procs?

[gsingh72]

I have about 50 procedures already created in the database . Now I have to add suffix _bak in their names like

abc
it shud become abc_bak

how to achieve this?

pls.help

[jovery]

I don't think there is a command to actually rename a procedure, you will need to write a script which extracts the source of the procedure (DBA_SOURCE) and creates it under a new name.

Regards

[Sameer]

Originally posted by jovery
I don't think there is a command to actually rename a procedure, you will need to write a script which extracts the source of the procedure (DBA_SOURCE) and creates it under a new name.

Regards

If you need ready-made script to extract source of the procedure, refer to http://www.quest-pipelines.com/pipelines/plsql/archives.htm#code19

HTH

Sameer

[stmontgo]

if you are using 9i u could use dbms_metadata to get and change the ddl on the fly. Here is an exmaple on how to get the ddl

set pagesize 9999
set long 9999

SQL> select dbms_metadata.get_ddl('PROCEDURE','MONITOR_INDEXES') from
dual;

DBMS_METADATA.GET_DDL('PROCEDURE','MONITOR_INDEXES')
----------------------------------------------------------------------
----------

CREATE OR REPLACE PROCEDURE "JADEDEV"."MONITOR_INDEXES" (p_switch VA
RCHAR)
AS
BEGIN

IF p_switch = 'ON' THEN
FOR x in (SELECT index_name FROM user_indexes WHERE index_type
!= 'LOB')
LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' || x.index_name || ' MONITORI
NG USAGE';
END LOOP;

ELSIF p_switch = 'OFF' THEN
FOR x in (SELECT index_name FROM user_indexes)
LOOP
EXECUTE IMMEDIATE 'ALTER INDEX ' || x.index_name || ' MONITORI
NG USAGE';
END LOOP;



END IF;

END;



SQL>