How to trim all columns in all rows in all tables of type string?

Question

Hi folks,

In Oracle 10g, is there a way to do the following in PL/SQL?

for each table in database
  for each row in table
    for each column in row
      if column is of type 'varchar2'
        column = trim(column)

Thanks!

Answer 1

Of course, doing large-scale dynamic updates is potentially dangerous and time-consuming. But here's how you can generate the commands you want. This is for a single schema, and will just build the commands and output them. You could copy them into a script and review them before running. Or, you could change dbms_output.put_line( ... ) to EXECUTE IMMEDIATE ... to have this script execute all the statements as they are generated.

SET SERVEROUTPUT ON

BEGIN
  FOR c IN
    (SELECT t.table_name, c.column_name
       FROM user_tables t, user_tab_columns c
       WHERE c.table_name = t.table_name
         AND data_type='VARCHAR2')
  LOOP

    dbms_output.put_line(
                      'UPDATE '||c.table_name||
                      ' SET '||c.column_name||' = TRIM('||c.column_name||') WHERE '||
                      c.column_name||' <> TRIM('||c.column_name||') OR ('||
                      c.column_name||' IS NOT NULL AND TRIM('||c.column_name||') IS NULL)'
                     );
  END LOOP;
END;

Answer 2

Presumably you want to do this for every column in a schema, not in the database. Trying to do this to the dictionary tables would be a bad idea...

declare
  v_schema varchar2(30) := 'YOUR_SCHEMA_NAME';
  cursor cur_tables (p_schema_name varchar2) is
    select owner, table_name, column_name 
    from all_tables at,
      inner join all_tab_columns atc
        on at.owner = atc.owner 
          and at.table_name = atc.table_name
    where atc.data_type = 'VARCHAR2'
      and at.owner = p_schema;
begin
  for r_table in cur_tables loop
    execute immediate 'update ' || r.owner || '.' || r.table_name
      || ' set ' || r.column_name || ' = trim(' || r.column_name ||');';
  end loop;
end;

This will only work for fields that are VARCHAR2s in the first place. If your database contains CHAR fields, then you're out of luck, because CHAR fields are always padded to their maximum length.