populate only if not populated

[dsd7038]

I use the following script to populate all uppercase fields as shown below. However once this script is run once and populates U## fields. If run the second time it will do the same. How can I change the script so that it ignores existing populated U## fields but only populates new U## field names.

cursor get_obj is
select table_name,column_name from user_tab_columns
where column_name like 'U##%';

BEGIN

cursor_id:=dbms_sql.open_cursor;

FOR obj_rec in get_obj LOOP

u_sql := 'UPDATE '||obj_rec.table_name||'SET '||obj_rec.column_name||' = UPPER(' || SUBSTR(obj_rec.column_name, 4) || ')';
dbms_sql.parse(cursor_id,u_sql,1);
dbms_output.put_line(u_sql);
result := dbms_sql.execute(cursor_id);

END LOOP;

dbms_sql.close_cursor(cursor_id);

END;
/

[slimdave]

Begin
For c
In (select table_name tab,column_name col
from user_tab_columns
where column_name like 'U##%')
Loop
Execute Immediate
'Update '||c.tab||
' Set '||c.col||'=Upper('||SubStr(c.col, 4)||')'||
'Where '||c.col||'!=Upper('|| SubStr(c.col, 4)||')';
End Loop;
End;
/


I also dumped the explicit cursor and the DBMS_SQL, which IMHO make it more difficult to read.

[dsd7038]

I receive error dml_locks exceeded when I run the following. I know I can increase the dml_locks parameter in the ini file, BUT is there a way whereby the select statement will only select column names where the value in U## does not equal the value in the SUBSTR obj_rec.column_name, 4.

Please advise

SQL Statement


cursor get_obj is
select table_name,column_name from user_tab_columns
where column_name like 'U##%';

BEGIN

cursor_id:=dbms_sql.open_cursor;

FOR obj_rec in get_obj LOOP

u_sql := 'UPDATE '||obj_rec.table_name||' SET '||obj_rec.column_name||
' = UPPER(' || SUBSTR(obj_rec.column_name, 4) || ')'||
'Where '||obj_rec.column_name||'!=Upper('|| SubStr(obj_rec.column_name, 4)||')';
dbms_sql.parse(cursor_id,u_sql,1);
dbms_output.put_line(u_sql);
result := dbms_sql.execute(cursor_id);

END LOOP;

dbms_sql.close_cursor(cursor_id);