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;
----------------------------------------------------------------
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