0

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. I'm not doing any fetch here.

pmr
  • 58,701
  • 10
  • 113
  • 156
Atti
  • 3
  • 1
  • 3
  • I don't think you are showing us the correct code (or error message). The error message references a line 8 which is apparently not in the procedure you posted. And *that* update statement could not throw *that* error message. –  May 02 '12 at 21:50

5 Answers5

1

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.

Erich Kitzmueller
  • 36,381
  • 5
  • 80
  • 102
  • Even if this job is removed, i keep getting following error in the log – Atti Sep 08 '10 at 09:28
  • [I pressed Enter for new line and the comment is posted :) ] ORA-00604: error occurred at recursive SQL level 1 ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 8 – Atti Sep 08 '10 at 09:29
  • Well, if you can (not a production machine), disable the other jobs and see what happens. Still get the errors in the log? No? Re-Enable the first job, wait. Repeat until the error reappears. Yes? Log in as a DBA and check ALL_JOBS for jobs running in another schema. – Erich Kitzmueller Sep 08 '10 at 12:15
  • Thanks for your trip. There is one job which is causing the "recursive SQL ..." error but even after disabling all other jobs, my job(#26) is still failing with same error. – Atti Sep 09 '10 at 07:06
  • can you post the output of "select * from user_jobs where job=26"? – Erich Kitzmueller Sep 09 '10 at 07:32
  • I posted it as an answer to this question – Atti Sep 09 '10 at 07:54
  • 'JOB','LOG_USER','PRIV_USER','SCHEMA_USER','LAST_DATE','LAST_SEC','THIS_DATE','THIS_SEC','NEXT_DATE','NEXT_SEC','TOTAL_TIME','BROKEN','INTERVAL','FAILURES','WHAT','NLS_ENV','MISC_ENV','INSTANCE' 127,'HRSA','HRSA','HRSA',09.09.2010 11:55:48,'11:55:48',,'',09.09.2010 12:03:48,'12:03:48',0,'N','sysdate + 1/1440',3,'archtemp;','NLS_LANGUAGE="'AMERICAN"' NLS_TERRITORY="'AMERICA"' NLS_CURRENCY="'$"' NLS_ISO_CURRENCY="'AMERICA"' NLS_NUMERIC_CHARACTERS="'.,"' NLS_DATE_FORMAT="'DD-MON-RR"' NLS_DATE_LANGUAGE="'AMERICAN"' NLS_SORT="'BINARY"'','0102000200000000',0 – Atti Sep 09 '10 at 08:31
  • Hmmm... nothing odd here... I must confess I'm running out of plausible ideas. Maybe there are check constraints on the table that do something *strange* that causes the error. – Erich Kitzmueller Sep 09 '10 at 08:47
0

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

Kirill Leontev
  • 10,641
  • 7
  • 43
  • 49
  • It's possible that ammoQ is right, then, perform a `select * from user_jobs where job = 26`, and see which one is actually failing. It should have `broken` status. – Kirill Leontev Sep 08 '10 at 09:41
  • There are three other jobs but none of them is Broken or even failed once. – Atti Sep 08 '10 at 09:48
0

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.

Community
  • 1
  • 1
Gary Myers
  • 34,963
  • 3
  • 49
  • 74
  • I didn't get you about "SERVERERROR". I disabled all other jobs but still mine is failing – Atti Sep 09 '10 at 07:09
  • SERVERERROR is a type of trigger that can capture additional information when an error is returned. You are getting a TOO_MANY_ROWS error and you need to identify the SQL and table(s) involved. A trace (DBMS_MONITOR) should do the job as well. – Gary Myers Sep 09 '10 at 23:32
0

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
Nick Pierpoint
  • 17,641
  • 9
  • 46
  • 74
0

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

Atti
  • 1
  • So you went through my example and it failed? – Nick Pierpoint Sep 15 '10 at 14:15
  • Yes, however, I used same script to create two jobs on a different service(9i) with two different tables and procedures and both worked well. My colleague created 1 job on 11g with DBA and it failed and and the other job on same service was created using USER which is running fine. I tried both with and without DBA on 10g and both failed. – Atti Sep 16 '10 at 05:56