--存储过程:DeleteColumnFromTable(作业调用此过程:删除列)create or replace procedure DeleteColumnFromTable is
m_strSql varchar2(150);
cursor cur is
select TABLE_NAME from COLUMN_REGISTRY group by TABLE_NAME ;
begin
FOR rec IN cur LOOP
m_strSql := ' alter table ' || rec.table_name || ' drop unused columns ';
EXECUTE IMMEDIATE m_strSql;
COMMIT;
END LOOP;end DeleteColumnFromTable;
--创建作业
variable DeleteColumnJob number;
begin
dbms_job.submit(:DeleteColumnJob,'DeleteColumnFromTable;',sysdate,'sysdate+1/48');
commit;
end;
--执行作业begin
dbms_job.run(:DeleteColumnJob); //此时失败
end; 失败提示:
begin
*
ERROR 位于第 1 行:
ORA-12011: 无法执行 1 作业
ORA-06512: 在"SYS.DBMS_IJOB", line 406
ORA-06512: 在"SYS.DBMS_JOB", line 272
ORA-06512: 在line 2请求达人们提供资料及解决方法!谢谢了.
m_strSql varchar2(150);
cursor cur is
select TABLE_NAME from COLUMN_REGISTRY group by TABLE_NAME ;
begin
FOR rec IN cur LOOP
m_strSql := ' alter table ' || rec.table_name || ' drop unused columns ';
EXECUTE IMMEDIATE m_strSql;
COMMIT;
END LOOP;end DeleteColumnFromTable;
--创建作业
variable DeleteColumnJob number;
begin
dbms_job.submit(:DeleteColumnJob,'DeleteColumnFromTable;',sysdate,'sysdate+1/48');
commit;
end;
--执行作业begin
dbms_job.run(:DeleteColumnJob); //此时失败
end; 失败提示:
begin
*
ERROR 位于第 1 行:
ORA-12011: 无法执行 1 作业
ORA-06512: 在"SYS.DBMS_IJOB", line 406
ORA-06512: 在"SYS.DBMS_JOB", line 272
ORA-06512: 在line 2请求达人们提供资料及解决方法!谢谢了.
2 m_strSql varchar2(150);
3 cursor cur is
4 select a from a group by a ;
5 begin
6 FOR rec IN cur LOOP
7 m_strSql := ' alter table ' || rec.a || ' drop unused columns ';
8 EXECUTE IMMEDIATE m_strSql;
9 COMMIT;
10 END LOOP;
11
12 end DeleteColumnFromTable;
13 /过程已创建。SQL> variable DeleteColumnJob number;
SQL> begin
2 dbms_job.submit(:DeleteColumnJob,'DeleteColumnFromTable;',sysdate,'sysdate+
1/48');
3 commit;
4 end;
5 /PL/SQL 过程已成功完成。SQL> begin
2 dbms_job.run(:DeleteColumnJob);
3 end;
4 /PL/SQL 过程已成功完成。
我这里一切正常