---我建立的存储过程如下,意思是建立一个以当前日期命名的表,例如xin20090205
---(在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;
/
然后报告错误
ORA-06550: 第 1 行, 第 93 列:
PLS-00905: 对象 DIZZY.SP_CREATE_TABLE 无效
ORA-06550: 第 1 行, 第 93 列:
PL/SQL: Statement ignored
ORA-06512: 在 "SYS.DBMS_JOB", line 79
ORA-06512: 在 "SYS.DBMS_JOB", line 136
ORA-06512: 在 line 2
jobno
---------
请问是怎么回事？能给我讲讲怎么改么？
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'SP_CREATE_TABLE;',
SYSDATE,'sysdate+1/24/12');
commit;
end jobno;
å¨éè¾¹æ§è¡ä¸è¾¹çï¼æä¸è¾¹è¿äºæ¾å°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,'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||' as select * from abc';
EXECUTE IMMEDIATE v_sql;
commit;
EXCEPTION
WHEN OTHERS
THEN
on_flag := SQLCODE;
out_reason := SUBSTR (SQLERRM, 1, 255);
ROLLBACK;
END;
ç¨åºæ§è¡ä½æ¹æï¼
DECLARE
on_flag NUMBER;
out_reason VARCHAR2(4000);
BEGIN
-- Now call the stored program
sp_create_table(on_flag,out_reason);
END;
åºè¯¥æ¯ä¸æ ä¸å¤±äºï¼åæé®é¢çè¨å§
