How to rename multiple stored procedures in Oracle

Question

I'm using Oracle 12C, now I'm in a following trouble:

I have multiple stored procedures like:

schema.TEST1, schema.TEST2, schema.TEST3....

Now, I want to rename all of them to schema.TEST01, schema.TEST02, schema.TEST03...or any name I want which was formatted before, this is for backup.

In Oracle, I can't rename a stored procedure using a ALTER statement rename like SQL. How can I do this with one click?

Thanks!

Answer 1

Make changes according to your schema and naming convention. But it is nonsense, you do not need to backup in that way. But I took it as a challenge and would like to present you the below code

Use CLOB if source text is large enough.

DECLARE
type names_table is table of VARCHAR2(50);
  names names_table;
TYPE source_txt_table is TABLE OF VARCHAR2(32767);
  source_txt source_txt_table;
  header VARCHAR2(32767);
  final_sourc_txt VARCHAR2(32767);
BEGIN

  SELECT OBJECT_NAME bulk COLLECT into names from user_procedures WHERE object_type = 'PROCEDURE' AND OBJECT_NAME IN ('DO_SOMETHING_1','DO_SOMETHING_2');

  FOR i in 1..names.LAST
  LOOP

    SELECT text bulk COLLECT into source_txt
             FROM all_source
             WHERE name = names(i)
             ORDER BY line;

      source_txt(1) := 'CREATE OR REPLACE '||source_txt(1);

     header := REGEXP_REPLACE(upper(source_txt(1)), names(i), 'HR.'||names(i)||'_bck');  --make changes according to new naming convention

     source_txt(1) := header;

      FOR j in 1..source_txt.LAST
      LOOP

       final_sourc_txt := final_sourc_txt||source_txt(j);

      END LOOP;
      EXECUTE IMMEDIATE final_sourc_txt;
      dbms_output.put_line('Success: '|| names(i));
      final_sourc_txt := NULL;
      header := NULL;
      source_txt := NULL;
  END LOOP;

END;