| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Anyone know how to get a database job to run?
A copy of this was sent to "RMakanji" <rmakanji_at_mindspring.com>
(if that email address didn't require changing)
On Mon, 15 Mar 1999 09:34:27 -0600, you wrote:
>I can't get a database job to process. I use the code below to submit the
>job fine but it never runs. I assume its a problem with parameters. I've
>tried playing with the parameters but cannot use the Run(job) call to kick
>it off immediately for testing without getting errors.
>
>What I want it to do is kick of tommorow morning at 5:00AM.
>
>Declare
> v_jobnum NUMBER;
>BEGIN
>
>DBMS_JOB.SUBMIT(v_jobnum,'tax_interface.adp_quarterly(:p_qtr);',trunc(sysdat
>e)+29/24);
> commit;
>EXCEPTION
> WHEN OTHERS THEN
> dbms_output.put_line('Error submitting job. '||SQLERRM);
>END; -- Procedure
>
>
You have a bind variable in there -- how is it supposed to get a value? the job is most likely failing. Here is an example like yours:
SQL> create or replace procedure foo( l_x in number )
2 as
3 begin
4 null;
5 end;
6 /
Procedure created.
SQL>
SQL> declare
2 l_job number;
3 begin
4 dbms_job.submit( l_job, 'foo(:x);', sysdate ); 5 commit;
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select job, what from user_jobs;
JOB WHAT
---------- ------------------------------
97328 foo(:x);
so, it is queued and ready to go, however if I look closer:
SQL> alter session set nls_date_format='dd-mon-yyyy hh24:mi:ss';
Session altered.
SQL> select job, what, next_date, failures, broken from user_jobs;
JOB WHAT NEXT_DATE FAILURES B
---------- ------------------------------ -------------------- ---------- -
97328 foo(:x); 15-mar-1999 11:22:56 1 N
I can see it's already failed once. If you look in your alert.log file on the server, you'll find:
Mon Mar 15 11:20:56 1999
Errors in file /usr/oracle/oracle8/admin/oracle8/bdump/oracle8_snp2_6510.trc:
ORA-12012: error on auto execute of job 97328
ORA-01008: not all variables bound
so, use the alert.log to find the error that happened. You need to do something like:
Declare
v_jobnum NUMBER;
BEGIN
DBMS_JOB.SUBMIT(v_jobnum,
'tax_interface.adp_quarterly(' || p_qtr || ');',
trunc(sysdate)+29/24);
that is, concatenate the value of p_qtr in -- don't bind it.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |