Tuesday, October 28, 2014

Replace character from Oracle Procedure, Function, Package and Trigger

You can replace character and compile Procedure, Function, Package and Trigger using this block.
----------------------------------------------------------------

DECLARE
  V_CLOB_USER CLOB;
  V_SEARCHING VARCHAR2(300):='FROM_USER';
  V_REPLACE_WITH VARCHAR2(300):='TO_USER';
 BEGIN
       
        FOR INDUSR IN (SELECT DISTINCT NAME, REPLACE (TYPE, 'PACKAGE BODY', 'PACKAGE') TYPE,U.OWNER OWNER_OBJECT
                          FROM DBA_SOURCE U, DBA_OBJECTS O
                         WHERE     O.OBJECT_NAME = U.NAME
                             AND U.OWNER = O.OWNER
                             AND U.OWNER = V_SEARCHING
                               AND TYPE IN ('PROCEDURE', 'FUNCTION', 'PACKAGES', 'TRIGGER', 'PACKAGE BODY')) LOOP

         BEGIN

         SELECT REPLACE(DBMS_METADATA.GET_DDL(INDUSR.TYPE,INDUSR.NAME,INDUSR.OWNER_OBJECT),V_SEARCHING,V_REPLACE_WITH) INTO V_CLOB_USER FROM DUAL;

         EXECUTE IMMEDIATE V_CLOB_USER;
           EXCEPTION
                    WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE(SQLERRM);
          END;

   END LOOP;
END;

No comments:

Post a Comment