asked on

dbms_job strange error

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?

Steve Wales

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.

schwertner

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;

dramacqueen

ASKER

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.

Sujith

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 Wales

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;

ASKER CERTIFIED SOLUTION

LindaC

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LindaC

Don't forget the commit after running this.

dramacqueen

ASKER

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.