ORACLE 中user_jobs调用 存储过程
来源:百度知道 编辑:UC知道 时间:2024/09/24 13:50:55
---(在sql窗口执行)
create or replace PROCEDURE SP_CREATE_TABLE
(
on_flag OUT NUMBER,
out_reason OUT VARCHAR2
)
is
v_sql varchar2(2000);
v_now_date varchar2(8);
v_table_name varchar2(3);
begin
select to_char(sysdate,'yyyymmdd') into v_now_date from dual;
select 'xin' into v_table_name from dual;
v_sql :='create table '||v_table_name||''||v_now_date||'';
v_sql :=v_sql || ' as select * from abc;
EXECUTE IMMEDIATE v_sql;
commit;
EXCEPTION
WHEN OTHERS
THEN
on_flag := SQLCODE;
out_reason := SUBSTR (SQLERRM, 1, 255);
ROLLBACK;
END;
----然后定时每天执行(在命令窗口执行)
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'SP_CREATE_TABLE;',
SYSDATE,'sysdate+1/24/12');
commit;
end;
/
你这个就是我给你写的吧?
在里边执行下边的,把下边这些放到job里:
DECLARE
on_flag NUMBER;
out_reason VARCHAR2(4000);
BEGIN
-- Now call the stored program
sp_create_table(on_flag,out_reason);
-- Output the results
:a0 := on_flag;
:a1 := out_reason;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SubStr('Error '||TO_CHAR(SQLCODE)||': '||SQLERRM, 1, 255));
RAISE;
END;
对了,还不行的话你就换个工具,换toad,找到job,时间设置好了
还有,大妹子,你分真多,哈
不好意思啊,我这老报一个ora-01008的错误,没弄明白
---------------------------------------------------------------------
OK,解决,那些乱七八糟的要绑定变量,程序和执行的你都改一下吧
程序改成:
create or replace PROCEDURE SP_CREATE_TABLE
(
on_flag OUT NUMBER,
out_reason OUT VARCHAR2
)
is
v_sql varchar2(2000);
v_now_date varchar2(8);
v_table_name varchar2(3);
begin
select to_char(sysdate,'yyyym