Why DBMS_Scheduler job failed?

Question

I have written one procedure and one job. From job I am running procedure. Following is the script for creating job

DBMS_SCHEDULER.create_job  (job_name  => 'IBPROD2.RUN_FETCH_ACCT_ALERTS',
job_type        => 'STORED_PROCEDURE',
job_action      => 'FETCH_ACCT_ALERTS',
start_date      => sysdate,
repeat_interval => 'FREQ=HOURLY;INTERVAL=2;',
enabled         => TRUE,
auto_drop       => FALSE
);

After creating job I am running following command to get job details for owner IBPROD2 where I can see failure_count column value as 1 for RUN_FETCH_ACCT_ALERTS job. There is no problem in procedure FETCH_ACCT_ALERTS when I run it manually.

Can anyone help me in why the job is failing? Am I missing something?

Answer 1

Query the ALL_SCHEDULER_JOB_RUN_DETAILS view (or perhaps the DBA equivalent).

select *
from all_scheduler_job_run_details
where job_name = 'IBPROD2.RUN_FETCH_ACCT_ALERTS'

You'll be particularly interested in the error# which will give you an Oracle error number you can look up. Also, the additional_info column might have some, er, additional info.

---

The error code means this:

ORA-28179: client user name not provided by proxy
Cause: No user name was provided by the proxy user for the client user.
Action: Either specify a client database user name, a distinguished name or an X.509 certificate.

So it's something to do with your security set up. Authentication is failing for reason. As I lack a detailed knowledge of your architecture (and I'm not a security specialist) I'm not in a position to help you.

Because I have already created many jobs to run different procedures with same owner. All are running successfully.

So in what way does this procedure differ from all the others?