Oracle scheduled job fails

Question

Hi 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;

The 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, Am i doing any "fetch" here!!! Please help

Answer 1

You don't do any data fetch here, but I guess some ON UPDATE trigger on ARCH_TEMP table might. Check it.

Answer 2

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.

Answer 3

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.

Answer 4

Hi (second answer)...

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

Answer 5

Hi

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