PL/SQL script to search for a particular string in data from the entire database

Question

I work on Oracle SQL Developer and looking for a script for which the input will be - $1 - String to be searched $2 - String to be replaced with The script will find for $1 in all TABLE/VIEWS/FUNCTIONS/PACKAGES, etc. and will replace with $2 from the entire database of a particular schema. The output should be the count of replacements.

Guidance is highly appreciated.

Answer 1

If you want to change the table data containing certain string in any column then it could be done using dynamic sql and pl/sql. It is quite risky action, though. You realy should at least be sure to target tables to those that such an action would not compromise accidentaly. Here is a rough sample of an option to do it (with some test data).
TEST Tables:

Create Table TEST_TBL_1 (ID Number(6), TEXT_1 VarChar2(16), A_DATE DATE, TEXT_2 VarChar2(16));
Insert Into TEST_TBL_1
  ( Select 11, 'abcdef', DATE '2024-01-01', '***XYZ***' From Dual Union All
    Select 12, 'ghijkl', DATE '2024-01-02', Null From Dual Union All
    Select 13, 'mnopqr', DATE '2024-01-03', '***XYZ***' From Dual 
  );
Create Table TEST_TBL_2 (ID Number(6), STR_1 VarChar2(16));
Insert Into TEST_TBL_2
  ( Select 21, 'tbl_2_A' From Dual Union All
    Select 22, 'tbl_2_B' From Dual Union All
    Select 23, 'tbl_2_C' From Dual 
  );
Create Table TEST_TBL_3 (ID Number(6), TXT VarChar2(16));
Insert Into TEST_TBL_3
  ( Select 31, '***XYZ***' From Dual Union All
    Select 32, '***XYZ***' From Dual Union All
    Select 33, Null From Dual 
  );

PL/SQL Block

Set SERVEROUTPUT ON
--
-- replacing '***XYZ***' with '==>xyz<=='
--
Declare
    Cursor c Is
          Select  TABLE_NAME, COLUMN_NAME 
          From    all_tab_columns 
          Where   OWNER = 'YOUR_OWNER_NAME' And 
                  TABLE_NAME Like 'TEST_TBL_%' And  -- limiting to just TEST Tables
                  DATA_TYPE = 'VARCHAR2';
    mTab  VarChar2(32);
    mCol  VarChar2(32);
    mCmd  VarChar2(255);
    mCnt  Number(6) := 0;
    mTotal  Number(6) := 0;
    --
    strToReplace  VarChar2(12) := '***XYZ***';
    strReplacement  VarChar2(12) := '==>xyz<==';
Begin
    Open c;
    Loop
        Fetch c Into mTab, mCol;
        Exit When c%NOTFOUND;
        mCmd := 'Select Count(*) From ' || mTab || ' Where InStr(' || mCol || ', ''' || strToReplace || ''') > 0';
        Execute Immediate mCmd Into mCnt;
        If mCnt > 0 Then
            mCmd := 'Update ' || mTab || Chr(10) || 
                    'Set ' || mCol || ' = Replace(' || mCol || ', ''' || strToReplace || ''', ''' || strReplacement || ''') ' || Chr(10) ||
                    'Where InStr(' || mCol || ', ''' || strToReplace || ''') > 0';
            Execute Immediate mCmd;
            Commit;
            mTotal := mTotal + mCnt;
        End If;
    End Loop;
    Close c;
    DBMS_OUTPUT.PUT_LINE(mTotal || ' rows updated');
End;

--       R e s u l t :
--  4 rows updated
--  PL/SQL procedure successfully completed.

TEST Tables - Before and After

Select * From TEST_TBL_1;
/*   B e f o r e :
        ID TEXT_1           A_DATE   TEXT_2          
---------- ---------------- -------- ----------------
        11 abcdef           01.01.24 ***XYZ***       
        12 ghijkl           02.01.24                 
        13 mnopqr           03.01.24 ***XYZ***          */
/*   A f t e r :
        ID TEXT_1           A_DATE   TEXT_2          
---------- ---------------- -------- ----------------
         1 abcdef           01.01.24 ==>xyz<==       
         1 ghijkl           02.01.24                 
         1 mnopqr           03.01.24 ==>xyz<==          */
         
Select * From TEST_TBL_2;
/*   B e f o r e    &   A f t e r :
        ID STR_1           
---------- ----------------
        21 tbl_2_A         
        22 tbl_2_B         
        23 tbl_2_C             */

Select * From TEST_TBL_3;
/*   B e f o r e :
        ID TXT             
---------- ----------------
        31 ***XYZ***       
        32 ***XYZ***       
        33                    */
/*   A f t e r :
        ID TXT             
---------- ----------------
        31 ==>xyz<==       
        32 ==>xyz<==       
        33                    */

Answer 2

This is not possible directly. So like Paul W said, you should do this outside the database.
What you can do however is export your source easily into files with following select (only for function and procedure, but you can extend it for other objects) you will get your data extracted and you can put it into files:

SELECT dbms_metadata.get_ddl(object_type,object_name,owner) 
FROM user_objects
WHERE object_type IN ('FUNCTION,'PROCEDURE);

After this you can change the string in the files. And then compile all the objects back against the database. Then everything will be changed.