CODEWITHSUNDEEP
sqloracle-databasestored-procedures
Daniele Foti
Daniele Foti

Reputation: 19

how to modify a value in a database (all tables all columns)

I have a big Oracle database with more than 1000 tables. Each table has 1000+ rows and 100+ columns.

I am trying to modify each occurrence of value "red" (or "RED") with value "blue" (or "BLUE") in the whole database. Each occurrence of red could possibly be in the middle of a string (%red%)

I was told a stored procedure is the right way. is that correct? which stored procedure would help me?

thanks!

Upvotes: 0

Views: 38

Answers (1)

Littlefoot
Littlefoot

Reputation: 143023

One option is to

  • use nested cursor for loops
    • one that fetches all tables in your schema
    • another that fetches all VARCHAR2 columns in those tables
  • replace "red" with "blue" for all rows that contain "row"

Something like this:

SQL> select * from test;

STRING_COL
--------------------------------------------------
this is a red apple
Deep red sea

SQL>     declare
  2        l_str varchar2(200);
  3      begin
  4        for cur_t in (select table_name from user_tables) loop
  5          for cur_c in (select column_name from user_tab_columns
  6                        where table_name = cur_t.table_name
  7                          and data_type = 'VARCHAR2'
  8                       )
  9          loop
 10            l_str := 'update ' || cur_t.table_name || ' set ' ||
 11                      cur_c.column_name  || ' = replace (' ||
 12                      lower(cur_c.column_name) || ', ''red'', ''blue'')' ||
 13                     ' where instr(lower(' || cur_c.column_name ||'), ''red'') > 0';
 14            execute immediate (l_str);
 15          end loop;
 16        end loop;
 17      end;
 18
 19  /

PL/SQL procedure successfully completed.

SQL> select * from test;

STRING_COL
--------------------------------------------------
this is a blue apple
Deep blue sea

SQL>

Upvotes: 2

Related Questions