改成如下:用sys用户以sysdba权限登录。然后程序改成如下方式 CREATE OR REPLACE PROCEDURE DELTAB IS v_tablename VARCHAR2(512); v_sql VARCHAR2(512); v_sql_cursor VARCHAR2(512); CURSOR CVV_tables IS select table_name from sys.DBA_ALL_TABLES where owner='WACSTEST'; begin OPEN CVV_tables; LOOP FETCH CVV_tables INTO v_tablename; EXIT WHEN CVV_tables%NOTFOUND; v_sql := 'truncate TABLE ' || v_tablename; EXECUTE IMMEDIATE v_sql; COMMIT; END LOOP; end DELTAB;
commit语句不要。因为truncate不需要作回滚操作。是一个ddl操作。 注意关闭游标。
liuyi8903(西西) 说的对. open c_cursor;close c_cursor; 一定要成对出现.
begin
your_proc_name;
end;
CREATE OR REPLACE PROCEDURE DELTAB IS
v_tablename VARCHAR2(512);
v_sql VARCHAR2(512);
v_sql_cursor VARCHAR2(512);
CURSOR CVV_tables IS
select table_name
from sys.DBA_ALL_TABLES
where owner='WACSTEST';
begin OPEN CVV_tables;
LOOP
FETCH CVV_tables INTO v_tablename;
EXIT WHEN CVV_tables%NOTFOUND;
v_sql := 'truncate TABLE ' || v_tablename;
EXECUTE IMMEDIATE v_sql;
COMMIT;
END LOOP;
end DELTAB;
注意关闭游标。
open c_cursor;close c_cursor;
一定要成对出现.