0

I am struggling with subject mentioned error during execution of my PL/SQL code, during debugging of my code i have observed that my database is not allowing me to use 'COMMIT' in procedure while using DBMS_JOB.SUBMIT.

My procedure is a bit complex, so i will use sample piece of code as reference:

DECLARE
  x integer(30):=0.0;
BEGIN
    SYS.DBMS_JOB.SUBMIT
    ( job       => X 
     ,what      => 'declare x integer:= 0.0; begin x:= x+1; end;'
     ,next_date => sysdate
     ,interval  => 'NULL'
     --,no_parse  => FALSE
    );
    insert into tem_job_insert values (x);
    update tem_job_insert set job_num = x+1;
    --SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' ||to_char(x));
   commit;
END;

Error :

ORA-01426: numeric overflow

ORA-06512: at line 14

I have tried to execute the same piece of code (i mean both my sample code & my actual code) in some other databases and it works fine with out any issues, problem is only with these DBs.

Please feel free to ask if i had missed to add any detail.

Kamil Gosciminski
  • 16,547
  • 8
  • 49
  • 72
SuryaV
  • 41
  • 1
  • 5
  • What data type is `tem_job_insert.job_num`? Do you get the error with just the job submission or insert/update, or do all those elements have to be there? And which version of patch level of Oracle are you seeing this in? (Assigning decimal fraction values to integer variables is odd but should be harmless...) – Alex Poole Mar 01 '16 at 19:49
  • Hello Alex, 1. What data type is tem_job_insert.job_num --> It's defined as a number 2. Do you get the error with just the job submission or insert/update, or do all those elements have to be there? --> I'm getting this error for Job submission. I have used those elements as a example. 3. And which version of patch level of Oracle are you seeing this in? --> My Oracle version is : "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production" For some reason i think it's not an issue with the code as it was working fine earlier. Let me know if you need any more details. – SuryaV Mar 02 '16 at 05:39
  • Because you define the column `job_num` length of 2 (0-99) in table `tem_job_insert `, your `x` `(job_num)` maybe bigger, then 99. Increase the size of the column to 5. (0-99999). – Stepan Kasyanenko Mar 02 '16 at 09:35
  • Although it would be better if you do remove the column size. Because in a system table `dba_jobs` column job number has no size. – Stepan Kasyanenko Mar 02 '16 at 09:38
  • In another database, this script works because the number of running job there is less than 99. – Stepan Kasyanenko Mar 02 '16 at 09:41
  • Thanks Stephan, your comment helped me to look in the Job queue direction. My DB was configured was queue size of '10', increasing that to '100' resolved the issue. – SuryaV Mar 02 '16 at 12:04
  • @StepanKasyanenko - I think you've misread that slightly. The comment doesn't say it's `number(2)`; the 2 part is referring to the second question I asked in my comment; the response to part 1 ended with the word 'number', and there's a part 3 later. – Alex Poole Mar 03 '16 at 09:03
  • @SuryaV - do you have stuck jobs then? Not quite sure how you get that error still. Might also be worth looking at using dbms_scheduler rather than the old dbms_job. – Alex Poole Mar 03 '16 at 09:05

0 Answers0