Problem with auto-execution of DBMS_JOB

[anuktac]

Hi,
I have a dbms_job set up to run every sysdate+(1/24)/25 interval. However, it does not get picked up automatically, even when the scheduled time (next_date and next_sec) have gone by. However, if I manually execute the job, then it completes successfully. Looking at the alert log, I find that for every unsuccessful execution there is an entry :
*** SESSION ID8.23195) 2003-03-27 11:23:35.741
*** 2003-03-27 11:23:35.741
ORA-12012: error on auto execute of job 30
ORA-12154: TNS:could not resolve service name
ORA-06512: at "CCBASC.My_PROGRAM", line 100
ORA-12154: TNS:could not resolve service name
ORA-06512: at line 1

I am puzzled by this. Any ideas?
If it could not resolve the service name, then why does it get successfully executed when dbms_job is executed manually?
-Anukta

 

[THoey]

I haven't seen that error, but something I ran into that sounds similar to your is that you need to make sure that the JOB_QUEUE_PROCESSES in the initialization parameters is set to at least one (1).

I will try to research your error later... Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[anuktac]

Terry,
Thanks fro replying.
These are my settings:
job_queue_interval = 60
job_queue_processes = 2

So, inspite of the job_queue_processes being non-zero, why does it not work?
-Anukta

 

[carp]

I think I would start by looking at the code that is causing the problem:

at "CCBASC.My_PROGRAM", line 100

 

[sem]

What do you mean by "manually"? Via dbms_job.run? Regards, Dima

 

[anuktac]

By manually executing it I mean, going into sqlplus and executing it myself by DBMS_JOB.RUN.
I doubt that there is a problem with the program itself, since it executes successfully via dbms_job.run. It fails when trying to run at scheduled time by dbms_job.
-Anukta

 

[sem]

I think that something's wrong with your dblink (I suppose you're using it in CCBASC.My_PROGRAM). Just an assumption: the same name of dblink is resolved differently for different accounts. One of them (under which the job is submitted) has wrong description. Regards, Dima