Oracle scheduled job fails
I am using Oracle 10g and using following script to create the job
CREATE OR REPLACE PROCEDURE archtemp AS
BEGIN
UPDATE ARCH_TEMP SET ARCH_DATE = SYSDATE;
COMMIT;
END archtemp;
VAR jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'archtemp;', SYSDATE, 'sysdate + 1/1440');
COMMIT;
END;
T开发者_如何学JAVAhe job never executes automatically (though it runs manually) with following error in alert_sid.log
ORA-12012: error on auto execute of job 26
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 8
I am unable to link the ORA-01422 error with any of my code. I'm not doing any fetch
here.
Assuming this is a script for SQL*Plus, there are two / misssing, so it does nothing at all:
CREATE OR REPLACE PROCEDURE archtemp AS
BEGIN
UPDATE ARCH_TEMP SET ARCH_DATE = SYSDATE;
COMMIT;
END archtemp;
/
VAR jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(:jobno, 'archtemp;', SYSDATE, 'sysdate + 1/1440');
COMMIT;
END;
/
I guess it's another job failing, not yours.
You don't do any data fetch here, but I guess some ON UPDATE
trigger on ARCH_TEMP
table might. Check it.
I'd use a SERVERERROR trigger (as described here) to try to catch the statement that is failing. But first, you could check the alert log. If recursive SQL is erroring, there may be a problem in the data dictionary.
Try putting in an explicit PL/SQL block as the WHAT parameter.
dbms_job.submit(v_jobno, 'begin archtemp; end;', sysdate, 'sysdate+1/1440');
Here's my test case, which seems to work fine:
create table arch_temp (
arch_date date
);
-- create row to test update
insert into arch_temp (arch_date) values (null);
create or replace procedure archtemp as
begin
update arch_temp set arch_date = sysdate;
commit;
end archtemp;
/
-- test everything works in isoloation
begin
archtemp;
end;
/
select * from arch_temp;
-- arch_date = 10:49:34
select * from user_jobs;
-- no rows returned
declare
v_jobno number;
begin
dbms_job.submit(v_jobno, 'begin archtemp; end;', sysdate, 'sysdate+1/1440');
commit;
dbms_output.put_line('v_jobno: ' || to_char(v_jobno));
end;
/
-- dbms_output...
-- v_jobno: 50520
select * from user_jobs;
-- JOB 50520 returned
-- LAST_DATE = 10:51:11
select * from arch_temp;
-- ARCH_DATE = 10:51:11
I tried solution by Nick Pierpoint as well but it didn't work for me It looks something is wrong with LUCK because i tried the same thing on another machine having Oracle 9i and it failed!!!
Thank you all for your replies.
Regards
精彩评论