views:

285

answers:

2

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!

+2  A: 

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;
Dave Costa
USER_TAB_COLUMNS contains the columns found in both tables and views. To make this work, you'll need to filter out the views.
Allan
If your database contains many and many tables with columns of type VARCHAR2, the buffer of the dbms_output.put_line could not be sufficient to contain all the UPDATE statements, so it will be convenient to store the UPDATE statements in an Oracle table, using INSERT statements instead of DBMS_OUTPUT.PUT_LINE statements.
The chicken in the kitchen
Agreed, chicken, but then again for my tables, which are only a few, I just increased the output buffer, and it worked fine.
daveslab
+2  A: 

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.

Allan