Find occurrences of a text and replace them with a new word in PL/SQL Developer?

Question

We have a Oracle database and we are using PL/SQL Developer for editor. There is an old javascript function which have been used thousands of times in different procedures. We need to replace the name of that function. Is there a way in PL/SQL Developer to find and replace the word in all procedures and all occurrences like other text editors or IDEs (Netbeans, Visual Studio, Eclipse, Android Studio) do?

Answer 1

I tried to write a code for your purpose, hope it helps!!

First you need a table to store results:

CREATE TABLE NEW_SOURCES
(
   OLD_NAME      VARCHAR2 (256 BYTE),
   NEW_NAME      VARCHAR2 (256 BYTE),
   SOURCE        CLOB,
   CHANGE_DATE   DATE
);

Now the function to do this:

CREATE OR REPLACE FUNCTION GENERATE_REFACTOR (
                                              P_OLD_NAME   IN VARCHAR2,
                                              P_NEW_NAME   IN VARCHAR2
                                             )
   RETURN NUMBER --Returns number of objects if success and -1 otherwise
IS
   CURSOR CUR_OBJ (
      P_NAME   IN VARCHAR2)
   IS
        SELECT NAME, TYPE, OWNER
          FROM ALL_DEPENDENCIES
         WHERE     REFERENCED_NAME = P_NAME
               AND TYPE IN ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')--Specify your object type here
      ORDER BY 3, 2, 1;

   CURSOR CUR_SRC (
                   P_NAME    IN VARCHAR2,
                   P_TYPE    IN VARCHAR2,
                   P_OWNER   IN VARCHAR2
                  )
   IS
        SELECT *
          FROM ALL_SOURCE
         WHERE NAME = P_NAME AND TYPE = P_TYPE AND OWNER = P_OWNER
      ORDER BY LINE;

   V_OLD_FUNCTION_NAME   VARCHAR2 (256);
   V_NEW_FUNCTION_NAME   VARCHAR2 (256);
   C_SOURCE              CLOB;
   V_BUFFER              VARCHAR2 (4000);
   V_MODIFIED_LINE       VARCHAR2 (4000);
   V_COUNT               NUMBER;
   V_GAP                 VARCHAR2 (512)
      :=    CHR (10)
         || '---------------------'
         || CHR (10)
         || 'CREATE OR REPLACE '; -- You can add your own comment header here
 PROCEDURE APPEND_TEXT (
                         V_CLOB     IN OUT NOCOPY CLOB,
                         V_BUFFER   IN OUT NOCOPY VARCHAR2,
                         V_TEXT                   VARCHAR2
                        )
--Can Append Text to CLOB in optimized way
--Useful in adding a lot of records one by one to a clob
--Notice: have to provide a big varchar2 buffer and clean it afterwards
IS
BEGIN
   V_BUFFER := V_BUFFER || V_TEXT;
EXCEPTION
   WHEN VALUE_ERROR
   THEN
      IF V_CLOB IS NULL
      THEN
         V_CLOB := V_BUFFER;
      ELSE
         DBMS_LOB.APPEND (V_CLOB, V_BUFFER);
         V_BUFFER := V_TEXT;
      END IF;
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Exc: ' || SQLERRM);
END;
BEGIN
   V_OLD_FUNCTION_NAME := P_OLD_NAME;
   V_NEW_FUNCTION_NAME := P_NEW_NAME;

   --If you wanna gather sources all in a single clob use these two lines and if you want them each one in a single clob comment them
   DBMS_LOB.CREATETEMPORARY (C_SOURCE, FALSE, 1000);
   V_BUFFER := NULL;

   FOR OBJ IN CUR_OBJ (V_OLD_FUNCTION_NAME)
   LOOP
      --If you wanna gather sources all in a single clob comment these two lines and if you want them each one in a single clob uncomment them
      --DBMS_LOB.CREATETEMPORARY (C_SOURCE, FALSE, 1000);
      --V_BUFFER := NULL;
      V_BUFFER := V_BUFFER || V_GAP;

      FOR LINE IN CUR_SRC (OBJ.NAME, OBJ.TYPE, OBJ.OWNER)
      LOOP
         IF LINE.TEXT LIKE '%' || V_OLD_FUNCTION_NAME || '%'
         THEN
            SELECT REPLACE (
                            LINE.TEXT,
                            V_OLD_FUNCTION_NAME,
                            V_NEW_FUNCTION_NAME
                           )
              INTO V_MODIFIED_LINE
              FROM DUAL;

            APPEND_TEXT (C_SOURCE, V_BUFFER, V_MODIFIED_LINE);
         ELSE
            APPEND_TEXT (C_SOURCE, V_BUFFER, LINE.TEXT);
         END IF;
      END LOOP;

      --If you wanna gather sources all in a single clob comment these two lines and if you want them each one in a single clob uncomment them
      --DBMS_LOB.APPEND (C_SOURCE, V_BUFFER);
      -- INSERT INTO NEW_SOURCES VALUES (OBJ.OWNER||'.'||OBJ.NAME, OBJ.TYPE, C_SOURCE, SYSDATE);

      V_COUNT := V_COUNT + 1;
   END LOOP;

   --If you wanna gather sources all in a single clob use these two lines and if you want them each one in a single clob comment them
   DBMS_LOB.APPEND (C_SOURCE, V_BUFFER);

   INSERT INTO NEW_SOURCES VALUES (V_OLD_FUNCTION_NAME, V_NEW_FUNCTION_NAME, C_SOURCE, SYSDATE);

   RETURN V_COUNT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE ('Exc: ' || SQLERRM);
      RETURN -1;
END;
/

Now commit and query the NEW_SOURCES table, your modified sources are there, you can save it in a .sql file and run it in pl/sql developer.

Take a backup at First

Keep in mind you may get errors in running final script in case of 'create or replace' clause, if so then comment it in v_gap variable declaration.

Good luck.