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.
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 */