to search and replace the string in all db objects source code in oracle database

I wanted to search and replace DZONE to SZONE in all the database objects where it i开发者_运维百科s being used. I have a query to search the DZONE using below query but dont know how to replace in code.

select name,text from user_source where text like '%DZONE';

---

first get the ddl and spool it to a text file for you to review:

select regexp_replace (dbms_metadata.get_ddl (object_type, object_name, USER),'DZONE','SZONE') 
from (
select distinct object_name, object_type
 from user_procedures where object_name in (select name from user_source where text like '%DZONE%')
)

than apply the source to your database. do this for the object types that you want to modify, in this example I only picked the stored procedures, packages and functions. I hope it helps.

---

You can't update the source of an object directly. You need to identify all the objects and recreate them with the modified text - either with an updated version of whatever DDL was used to create them in the first place, or if you don't have that, by extracting the full text, updating it, and then executing it. You could maybe do it with dynamic SQL but this seems a bit dangerous - personally I'd probably want to inspect and verify everything I was updating.

---

I recently need to create such script. This is to replace wrong unicode characters on all database, so I start searching for à all around:

--text with encoding problems

SET SERVEROUTPUT ON SIZE 100000

DECLARE
 CURSOR c1 IS
 SELECT owner, table_name, column_name
              FROM all_tab_columns
              WHERE owner = 'your-owner-name' and data_type LIKE '%CHAR%';
 --
 c1rec c1%ROWTYPE;
 --
 l_sql VARCHAR2(1000);
BEGIN
 FOR r1 IN c1 LOOP
    l_sql := 'DECLARE '||
             '   CURSOR c1 IS '||
             '   SELECT '||r1.column_name||
             '   FROM   '||r1.table_name||' WHERE '||r1.column_name||' like ''%Ã%''; '||
             '   c1rec c1%ROWTYPE; '||
             'BEGIN '||
             '   FOR r1 IN c1 LOOP '||
             '      dbms_output.put_line('''||r1.table_name||'.'||r1.column_name||': ''|| r1.'||r1.column_name||'); '||
             '   END LOOP; '||
             'END; ';

    EXECUTE IMMEDIATE l_sql;
 END LOOP;
END;
/

that will print all word problems (modify owner or data type as you need)

Ok, but after that, you need to fix database (replace). Well that's just more fun with oracle pl/sql

--generating updates with encoding problems

SET SERVEROUTPUT ON SIZE 100000

DECLARE
 CURSOR c1 IS
 SELECT owner, table_name, column_name
              FROM all_tab_columns
              WHERE owner = 'your-owner-name' and data_type LIKE '%CHAR%';
 --
 c1rec c1%ROWTYPE;
 --
 l_sql VARCHAR2(1000);
BEGIN
 FOR r1 IN c1 LOOP
    l_sql := 'DECLARE '||
             '   CURSOR c1 IS '||
             '   SELECT '||r1.column_name||
             '   FROM   '||r1.table_name||' WHERE '||r1.column_name||' like ''%Ã%''; '||
             '   c1rec c1%ROWTYPE; '||
             'BEGIN '||
             '   FOR r1 IN c1 LOOP '||
             '      dbms_output.put_line(''UPDATE '||r1.table_name||' SET '||r1.column_name||' = ''''''|| r1.'||r1.column_name||'||'''''' WHERE '||r1.column_name||' = ''''''|| r1.'||r1.column_name||'||'''''';''); '||
             '   END LOOP; '||
             'END; ';

    --dbms_output.put_line( l_sql );

    EXECUTE IMMEDIATE l_sql;
 END LOOP;
END;
/

that will output a series of update commands you should tweak with your fixes.

UPDATE PERSON SET NAME = 'ÿângela' WHERE NAME = 'ÿângela';
UPDATE ROOM SET DESCRIPCION = 'Sala de reparacÿn' WHERE DESCRIPCION = 'Sala de reparacÿn';
...

something like:

UPDATE PERSON SET NAME = 'Ángela' WHERE NAME = 'ÿângela';
UPDATE ROOM SET DESCRIPCION = 'Sala de reparación' WHERE DESCRIPCION = 'Sala de reparacÿn';
...

Depending on problem you could automate this part (if you manage to deal with the quote hell)

Hint: This is an update with a replace

SET SERVEROUTPUT ON SIZE 100000

DECLARE
 CURSOR c1 IS
 SELECT owner, table_name, column_name
              FROM all_tab_columns
              WHERE owner = 'your-owner-name' and data_type LIKE '%CHAR%';
              --uncomment to shorten the search and debug
              --AND table_name = 'some-table'
 --
 c1rec c1%ROWTYPE;
 --
 l_sql VARCHAR2(1000);
BEGIN
 FOR r1 IN c1 LOOP
    l_sql := 'DECLARE '||
             '   CURSOR c1 IS '||
             '   SELECT '||r1.column_name||
             '   FROM   '||r1.table_name||' WHERE '||r1.column_name||' like ''%ÿ%''; '||
             '   c1rec c1%ROWTYPE; '||
             'BEGIN '||
             '   FOR r1 IN c1 LOOP '||
             '      dbms_output.put_line(''UPDATE '||r1.table_name||' SET '||r1.column_name||' = REPLACE('||r1.column_name||',''''%ÿ%'''',''''ó'''')''||'' WHERE '||r1.column_name||' = ''''''|| r1.'||r1.column_name||'||'''''';''); '||
             '   END LOOP; '||
             'END; ';

    --uncomment to debug         
    --dbms_output.put_line( l_sql );

    EXECUTE IMMEDIATE l_sql;
 END LOOP;
END;
/

which output something like:

UPDATE VEHICLE SET NAME = REPLACE(NAME,'%ÿ%','ó') WHERE NAME = 'Camiÿn';

---

Use update query with where clause

Update user_source 
Set text = 'SZONE'
Where text like '%DZONE';

hopes that helps