This content has been marked as final. Show 5 replies
Sure, that might be a good exercise in PL/SQL.
before you actually try anything like this, consult your DBA. Even with the WHERE clauses, you'll probably be generating lots of redo, and your DBA might want to know about that in advance.
Query user_tab_columns to find VARCHAR2 (or other string) columns, sorting them by table_name and column_name.
For every row that you find, add something to two strigs, set_txt, which will utimately look like this
and where_txt, which will look like this:
UPDATE table_x SET column_1 = REPLACE (column_1, '-', '_') , column_2 = REPLACE (COLUMN_2, '-', '_')
After doing this for the last column of each table, concatenate these into a string such as
WHERE INSTR (column_1, '-') > 0 OR INSTR (column_2, '-') > 0
and execute it, using. EXECUTE IMMEDIATE.
UPDATE table_x SET column_1 = REPLACE (column_1, '-', '_') , column_2 = REPLACE (COLUMN_2, '-', '_') WHERE INSTR (column_1, '-') > 0 OR INSTR (column_2, '-') > 0
Use the analytic ROW_NUMBER function to determine if a given row is the first for a table (this will determine whether you will add 'SET' or ',' to set_txt) or if the column is the last for the table (in which case you'll want to use EXECUTE IMMEDIATE).
If you get stuck, post your best attempt at the code here, and describe exactly what the problem is.
you can try
i am not sure about that kind of update stmt, i never tried
FOR I IN SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME IN ( your list of tables in ' ' & seperated by ,) LOOP UPDATE I.TABLE_NAME SET column_1 = REPLACE (column_1, '-', '_'); END LOOP;
if you wanna alter it then u can use dynamic sql for the update stmt...
Edited by: kiran on Jan 14, 2011 3:23 PM
It really is a good practice to know how to update multiple column values. From my point of view, you can use a procedure declared inside a PL/SQL block and use it to dynamically update the columns in all the table you want updated. Also you can try using the dba_tab_columns data dictionary table to get the column name for the table that is initialized using a collection. If this is a bit vague, I'll try giving you an example. Please find below the sequence of code (not a working code since i cannot test it for the mean time but just the logic alone might help).
I'm still not sure if the logic will work. But it's a good practice to know how to update multiple column values.
DECLARE --declare a collection for the list of tables you want their columns updated (You will loop to this table names and pass the table names to a procedure which will update the columns of the passed table name) TYPE tbl_col_type is TABLE of VARCHAR2(50); L_table_col tbl_col_type := tbl_col_type('Table_Name1','Table_Name2','Table_Name3'); --delare a procedure that will update all the tables PROCEDURE UPD_TBL(I_table_name IN VARCHAR2) IS --This procedure takes 1 input and that's the table name passed from the collection. cursor C_columns is select column_name from dba_tab_columns where table_name = I_table_name; --This curor will get all the column names of the table passed, example - get all the columns of the table and validate it later on in the update syntax. BEGIN for rec in C_columns LOOP update I_table_name set rec.column_name = .... For this one try using the replace function discussed in this thread. rec.column_name is the column name taken from the cursor, this will loop through all the columns. if you don't want to update everything, just make a condition in the SQL update statement. e.g. set rec.column_name = ... where rec.column_name = the name of column you want updated. END LOOP; commit; EXCEPTION when OTHERS then NULL; END UPD_TBL; BEGIN FOR rec IN L_table_col.first .. L_table_col.last LOOP --This will loop to the declared collection, passing the table name to the declared procedure. UPD_TBL(L_table_col(rec)); END LOOP; EXCEPTION when OTHERS then dbms_output.put_line(SQLERRM); END;
Hope this helps.