How to crop all columns in all rows in all tables of a row type?

Question

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, performing large-scale dynamic updates is potentially dangerous and time-consuming. But here is how you can generate the necessary commands. This is for one circuit, and will simply create commands and output them. You can copy them to a script and view them before running. Or you can change dbms_output.put_line( ... ) to EXECUTE IMMEDIATE ... so that this script executes all the instructions as they are created.

 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 each column in the schema, not the database. Trying to do this with vocabulary 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, you are out of luck, because CHAR fields are always filled to the maximum length.