这个输出写在上面的proc里该怎么写?直接加在里面好像无法执行;;
create or replace procedure update_userid
as
v_sql varchar2(8000);
v_table varchar2(100);
v_count number(6);
cursor c_table is
select distinct table_name from user_tables a
where column_name='USERID';
……………………
spool c:\aaa.txt;
……………………
SELECT 'UPDATE '||OWNER||'.'||TABLE_NAME||' SET '||COLUMN_NAME||'='||'''XXX''||'||OWNER||'.'||
'substr('||COLUMN_NAME||',5)'||';'
FROM ALL_TAB_COLUMNS WHERE OWNER='xxx' AND COLUMN_NAME='xxx'
不好意思,有个地方错了
create or replace procedure update_userid
as
v_sql varchar2(8000);
v_table varchar2(100);
v_count number(6);
cursor c_table is
select distinct table_name from user_tables a
where column_name='USERID';
begin
dbms_output.enable(1000000);
v_count:=0;
open c_table;
loop
fetch c_table into v_table;
exit when c_table%NOTFOUND;
v_sql := 'update '||v_table||' set userid=''dddd''||substr(userid,5)';
begin
execute immediate v_sql;
commit;
exception
when others then
begin
v_count:=v_count+1;
dbms_output.put_line ('error:'||v_sql);
end;
end;
end loop;
close c_table;
dbms_output.put_line ('error count:'||v_count);
end update_userid;
/
create or replace procedure update_userid
as
v_sql varchar2(8000);
v_table varchar2(100);
v_count number(6);
cursor c_table is
select distinct table_name from user_tables a
where column_name='USERID';
begin
dbms_output.enable(1000000);
v_count:=0;
open c_table;
loop
fetch c_table into v_table;
exit when c_table%NOTFOUND;
v_sql := 'update '||v_table||' set userid=dddd||substr(userid,5)';
begin
execute immediate v_sql;
commit;
exception
when others then
begin
v_count:=v_count+1;
dbms_output.put_line ('error:'||v_sql);
end;
end;
end loop;
close c_table;
dbms_output.put_line ('error count:'||v_count);
end update_userid;