Find and replace, looped through all tables in oracle DB

Question

I am trying to loop through every table in an oracle DB, and find and replace a string value. I want to find all instances of a string value, replace it with another string value, for all entries in the entire DB. I was close, but for some reason my code is not responding properly.

DECLARE
   CURSER all_tables IS
      SELECT table_name FROM all_tables;
   v_count NUMBER;
BEGIN
   FOR host IN all_tables LOOP
      SELECT REPLACE('jack','j','b') "Changes"
         FROM host
   END LOOP;
END;

Please show what you've done so far. This is possible, but it's a pretty unusual thing to do. — Ed Gibbs, May 15 '13 at 19:29
It's essentially the same as http://stackoverflow.com/questions/208493/search-all-fields-in-all-tables-for-a-specific-value-oracle, except with update instead... — Ben, May 15 '13 at 19:45
`CURSER` is an incorrect keyword, and you have to specify a column name instead of Jack. I don't understand why you wrote this code. — Sebas, May 15 '13 at 19:55
Looping through all columns in all tables is a useful technique. The chosen example is unusual, the general approach is worth learning. — Danilo Piazzalunga, May 15 '13 at 20:17

Danilo Piazzalunga · Answer 1 · 2013-05-15T20:22:52.790

A word of caution

If you are actually trying to replace a string value for every column in every table in your schema, please be aware that it is going to take hours, at least.

Disclaimer aside, similar scripts turn out to be useful in a lot of situations (maybe to change a column name, maybe to perform massive data cleaning, or to create your tables from a metadata table).

The general approach

Basically, you need to:

Loop through every table in the target schema
For each table, loop through every VARCHAR2 column
For each column, generate an UPDATE statement and execute it

A smarter solution generates only one UPDATE statement for each table. Your solutions will then:

Loop through every table in the target schema
For each table, generate the first part of the UPDATE statement up to the SET clause
For each column, append the part of the SET clause to your statement
Execute your statement

Example code for Oracle 10g/11g, using the well-known HR schema:

One UPDATE statement per column

DECLARE
    schemaName VARCHAR2(30) := 'HR';
    stmt VARCHAR2(32767); -- on 11g, use CLOB
BEGIN
    FOR tr IN (
        SELECT t.OWNER, t.TABLE_NAME
        FROM ALL_TABLES t
        WHERE t.OWNER = schemaName
        ORDER BY 1, 2
    )
    LOOP
        FOR cr IN (
            SELECT c.COLUMN_NAME
            FROM ALL_TAB_COLUMNS c
            WHERE c.OWNER = tr.OWNER AND c.TABLE_NAME = tr.TABLE_NAME
              AND c.DATA_TYPE = 'VARCHAR2'
            ORDER BY 1
        )
        LOOP
            stmt := 'UPDATE '||tr.OWNER||'.'||tr.TABLE_NAME
                ||' SET '||cr.COLUMN_NAME||' = REPLACE('||cr.COLUMN_NAME||', ''j'', ''b'')';
            DBMS_OUTPUT.PUT_LINE(stmt||';'); -- useful for debugging
            EXECUTE IMMEDIATE stmt;
        END LOOP;
    END LOOP;
END;
/

One UPDATE statement per table

You can try to be smarter and use only one UPDATE statement for all the columns in a table. Be careful not to overflow the stmt variable.

DECLARE
    schemaName VARCHAR2(30) := 'HR';
    stmt VARCHAR2(32767); -- on 11g, use CLOB
    do_update BOOLEAN;
BEGIN
    FOR tr IN (
        SELECT t.OWNER, t.TABLE_NAME
        FROM ALL_TABLES t
        WHERE t.OWNER = schemaName
        ORDER BY 1, 2
    )
    LOOP
        do_update := FALSE;
        stmt := 'UPDATE '||tr.OWNER||'.'||tr.TABLE_NAME||' SET ';
        FOR cr IN (
            SELECT c.COLUMN_NAME
            FROM ALL_TAB_COLUMNS c
            WHERE c.OWNER = tr.OWNER AND c.TABLE_NAME = tr.TABLE_NAME
              AND c.DATA_TYPE = 'VARCHAR2'
            ORDER BY 1
        )
        LOOP
            do_update := TRUE;
            stmt := stmt||cr.COLUMN_NAME||' = REPLACE('||cr.COLUMN_NAME||', ''j'', ''b''), ';
        END LOOP;
        IF do_update THEN
            stmt := SUBSTR(stmt, 1, LENGTH(stmt) - 2); -- remove trailing ', '
            DBMS_OUTPUT.PUT_LINE(stmt||';'); -- useful for debugging
            EXECUTE IMMEDIATE stmt;
        END IF;
    END LOOP;
END;
/

Find and replace, looped through all tables in oracle DB

1 Answers1