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

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

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.

I had the problem to replace a single wrong char in a text field of a table by another char, in fact a ¿ by a simple single quote. And come up with following script, executed in SQLDeveloper 19.2 against an Oracle DB V12.2.0:

-- enables DBMS_OUTPUT.PUT_LINE
set serveroutput on;

DECLARE 
    SQL_STR     VARCHAR2(2000);
    text_f      VARCHAR2(2000);
BEGIN
   FOR cur_object IN (select ID, TEXT_F
                      from FRAGE f 
                      where  f.TEXT_F LIKE %¿%)
   LOOP
      BEGIN
         text_f := cur_object.TEXT_F;
         
         -- replace all occurences of ¿ by a single quote:
         SELECT REPLACE ( text_f,
                          ¿,
                           )
              INTO text_f
              FROM DUAL;         
              
         -- escape the single quote in order to be used in the sql statement:              
         SELECT REPLACE ( text_f,
                          ,
                           )
              INTO text_f
              FROM DUAL;         
              
--         DBMS_OUTPUT.PUT_LINE (text_f);
         
         SQL_STR := update Frage 
                        set TEXT_F =  || text_f ||  
                        where id =  || cur_object.ID;
         DBMS_OUTPUT.PUT_LINE (executing SQL  || SQL_STR || );
         EXECUTE IMMEDIATE SQL_STR;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.PUT_LINE (Failed!  || sqlerrm);
      END;
   END LOOP;
END;
/

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

Leave a Reply

Your email address will not be published. Required fields are marked *