CODEWITHSUNDEEP
oracle-databaseplsql
AllOutOfSalt
AllOutOfSalt

Reputation: 1492

Iterating all columns to find and replace a character

I want to iterate through all columns to find and replace a specific character. here is my pl/sql block:

 Declare
  match_count     Number:=0;
  v_search_string VARCHAR2(4000) := '%ي%';
BEGIN
 FOR t IN
 (SELECT owner,
  table_name,
column_name
FROM all_tab_columns
 WHERE (SUBSTR(table_name,1,2)='PN'
OR (SUBSTR(table_name,1,2)   ='CD'
AND owner                    ='PNET_USER' ))
AND (data_type               ='VARCHAR2'
OR data_type                 ='CLOB')
)
LOOP
BEGIN
  EXECUTE IMMEDIATE 'SELECT count(*) FROM '||t.owner || '.' || t.table_name|| ' WHERE '||t.column_name||' LIKE :1' INTO match_count USING v_search_string;
  IF match_count > 0 THEN
    dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
    --EXECUTE IMMEDIATE 'UPDATE '||t.table_name||' SET '||t.column_name||'=replace()'
  END IF;
END;
END LOOP;

It works fine and prints the name of columns that have the invalid characters. But I don't know how to replace the characters. How can i get the value of t.column_name , replace the invalid character and then update the t.table_name ?

Upvotes: 1

Views: 1136

Answers (2)

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

Well it was almost fine (and stevo's answer also). I let you change v_from and v_to with your chars.

Declare
  match_count     Number       :=0;
  v_from          varchar2(5)  := 'a';
  v_like          varchar2(5)  := '%'||v_from||'%';
  v_to            varchar2(5)  := 'b';
  v_sql           varchar2(1000);
  v_emesg         varchar2(1000);

  CURSOR s is
        (SELECT owner, table_name, column_name
        FROM    all_tab_columns
        where   SUBSTR(table_name,1,2)    IN ('PN', 'CD')
        AND     owner                     ='PNET_USER' 
        AND     data_type                 IN('VARCHAR2', 'CLOB'););
begin       
for t in s  LOOP
     begin
      EXECUTE IMMEDIATE 'SELECT count(*) FROM '||t.owner || '.' || t.table_name|| ' WHERE '||t.column_name||' LIKE :1' INTO match_count USING v_like;
      IF match_count > 0 THEN
            begin
            dbms_output.put_line( t.owner || '.' || t.table_name ||' '||t.column_name||' '||match_count );
            v_sql := 'UPDATE '||t.owner||'.'||t.table_name||' SET '||t.column_name||'= REPLACE('||t.column_name||', '''||v_from||''', '''||v_to||''') WHERE '||t.column_name||' LIKE '''|| v_like||'''';
            dbms_output.put_line(v_sql);

            EXECUTE IMMEDIATE v_sql;
            EXCEPTION WHEN OTHERS THEN
               v_emesg := SQLERRM;
               dbms_output.put_line(v_emesg);
               dbms_output.put_line('Errow while trying to update '||t.owner||'.'||.t.table_name||' : column '||t.column_name||'.';
            END;
      END IF;
      end;
  END LOOP;
end;

Upvotes: 2

steve godfrey
steve godfrey

Reputation: 1234

Something like

EXECUTE IMMEDIATE 'UPDATE '||t.table_name||' SET '||t.column_name||'=replace('||t.column_name||',''FROM'',''TO'')'

Should do it, where FROM is the invalid character and TO is whatever you want to change it to. See oracle docs in link for an explanation of how REPLACE works.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions134.htm.

NB I don't have anywhere to try this, so syntax might not be correct.

Upvotes: -1

Related Questions