We help IT Professionals succeed at work.

dbms_job strange error

dramacqueen
dramacqueen asked
on
1,450 Views
Last Modified: 2010-07-27
I'm trying to setup a regular job in Oracle using dbms_job.  But every time I try to run it, I get the following weird error:
SQLWKS> declare
     2>   new_job integer;
     3>
     4> begin
     5> dbms_job.submit(new_job, 'begin XMPT_LOAD_PROC; end;',
     6> 'trunc(sysdate)+15/24+35/1440',
     7>  'TRUNC(sysdate)+2/24');
     8>   dbms_output.put_line('Job: '||to_char(new_job));
     9> end;
    10>
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 5

The proc exists and is valid.  If I move the part of line 5 from (new_job, 'be.. to the next line, I get the same error still on line 5.  I've made sure the submitting user has exec any proc priv and has been granted exec priv by sys on dbms_job.  I've checked that dbms_job is publicly visible via public synonym.  Any ideas?
Comment
Watch Question

Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
See the examples under DBMS_JOB.SUBMIT here: http://www.psoug.org/reference/dbms_job.html

You need to change :
dbms_job.submit(new_job, 'begin XMPT_LOAD_PROC; end;',
To:
dbms_job.submit(:new_job, 'begin XMPT_LOAD_PROC; end;',

Note the colon before "new_job".

Job Number is an output parameter, the value is return to this variable from the SUBMIT procedure - the colon is needed to specify that this is a variable.
CERTIFIED EXPERT
Top Expert 2008

Commented:
This is working in SQL*Plus example:

set serveroutput on
set linesize 10000
variable x number;
begin
   DBMS_OUTPUT.enable(100000);  
   dbms_job.submit(:x,'compute_statistics;',trunc(sysdate),'trunc(sysdate+7)');
   commit;
   dbms_output.put_line(TO_char(:x));
end;

Author

Commented:
sjwales:
Now even weirder:
SQLWKS> declare
     2> new_job integer;
     3> begin
     4>  dbms_job.submit(:new_job, 'begin XMPT_LOAD_PROC; end;',
     5>  'trunc(sysdate)+15/24+35/1440',
     6>   'TRUNC(sysdate)+2/24');
     7>    dbms_output.put_line('Job: '||to_char(new_job));
     8>  end;
     9>  
    10>
MGR-00143: variable "NEW_JOB" has not been defined

schwertner:
Yours gives:
SQLWKS> set serveroutput on
Server Output                   ON
SQLWKS> set linesize 10000
MGR-00110: illegal SET option
SQLWKS> variable x number;
SQLWKS> begin
     2>    DBMS_OUTPUT.enable(100000);  
     3>    dbms_job.submit(:x,'compute_statistics;',trunc(sysdate),'trunc(sysdate+7)');
     4>    commit;
     5>    dbms_output.put_line(TO_char(:x));
     6> end;
     7>
ORA-06550: line 1, column 93:
PLS-00201: identifier 'COMPUTE_STATISTICS' must be declared
ORA-06550: line 1, column 93:
PL/SQL: Statement ignored
ORA-06512: at "SYS.DBMS_JOB", line 71
ORA-06512: at "SYS.DBMS_JOB", line 121
ORA-06512: at line 3

I'm beginning to wonder if there's something seriously wrong with this DB.  Have asked it to be rebooted at end of working day.
SujithData Architect
CERTIFIED EXPERT

Commented:
Try this:

declare
new_job integer;
begin
 dbms_job.submit(new_job, 'begin XMPT_LOAD_PROC; end;',
 trunc(sysdate)+15/24+35/1440,
 'TRUNC(sysdate)+2/24');
 dbms_output.put_line('Job: '||to_char(new_job));
end;
/
Steve WalesSenior Database Administrator
CERTIFIED EXPERT

Commented:
Here's code from $ORACLE_HOME/rdbms/admin/spauto.sql that is used to setup a job for Statspack that gives a fine example of how to make this work:

variable jobno number;
variable instno number;
begin
  select instance_number into :instno from v$instance;
  dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
  commit;
end;

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Commented:
Don't forget the commit after running this.

Author

Commented:
LindaC, you get the points, because submitting it with an exec has worked.  Well done and thanks.  

I had tried the isubmit but from within an anonymous block like the submit one shown with similar results.  

I think there's still something wrong with this DB because it really should accept the anonymous block but at least I've managed to get the job submitted.

Thanks to all others who tried to help.

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.