oracle failure error on scheduled job using dbms_job.submit

--dbms job run every sunday for proposed annual goal report
DECLARE
    x   NUMBER;
BEGIN
dbms_job.submit(job => X,
                what => 'wkly_proposed_goal_improve_prc' ,
                next_date => next_day(trunc(sysdate),'SUN'),
                interval => 'next_day(trunc(sysdate),''SUN'')',
                no_parse => true);
commit;
end;

It said failures 17 and I know the job is not running

I want the job to run every sunday. Can someone help me?
LVL 6
anumosesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Check the alert log to see if there is any indication of why the job is failing.
0
anumosesAuthor Commented:
How can I check the alert log?
0
slightwv (䄆 Netminder) Commented:
Check with your DBA.

Also check the online docs.  I believe you are on 9i:
http://docs.oracle.com/cd/A91202_01/901_doc/win.901/a90164/ch3.htm#1105206

The alert file is named sidALRT.LOG and is found in the directory specified by the BACKGROUND_DUMP_DEST parameter in the initialization parameter file. If the BACKGROUND_DUMP_DEST parameter is not set, the sidALRT.LOG file is generated in ORACLE_BASE\admin\db_name\bdump. Alert files should be deleted or archived periodically.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Alexander Eßer [Alex140181]Software DeveloperCommented:
I suppose "wkly_proposed_goal_improve_prc" is a stored procedure, right?!
Is this procedure
a. in a valid state?!
b. accessible/"visible" to the user/schema which tries to submit the job?!
0
anumosesAuthor Commented:
valid
failure
0
anumosesAuthor Commented:
Yes it is a stored procedure and I have synonyms created and granted permission. When I run the procedure stand alone no problem it executes. But dbms_job.submit is erroring

begin
  wkly_proposed_goal_improve_prc;
End;
0
slightwv (䄆 Netminder) Commented:
Those images don't help.

The alert log should tell you why it is failing.
0
slightwv (䄆 Netminder) Commented:
>>procedure stand alone no problem it executes. But dbms_job.submit is erroring


Did you issue the grants through a ROLE or directly to the user creating the job?

The grants need to be explicit and not through a role.
0
anumosesAuthor Commented:
through user creating the job.
0
slightwv (䄆 Netminder) Commented:
If the user BOB is the dbms_job owner then log into the database as HBC_DATA and execute:
grant execute on wkly_proposed_goal_improve_prc to bob;
0
anumosesAuthor Commented:
In my case dbms_job owner is hbc_data
0
anumosesAuthor Commented:
Since user is hbc_data th permission I gave is as below

GRANT All ON  wkly_proposed_goal_improve_prc TO HBC_USER

GRANT All ON  wkly_proposed_goal_improve_prc TO PUBLIC
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
Shouldn't the job submission rather read as follows?!
DECLARE
    x   NUMBER;
BEGIN
dbms_job.submit(job => X,
                what => 'begin wkly_proposed_goal_improve_prc(); end;' ,
                next_date => next_day(trunc(sysdate),'SUN'),
                interval => 'next_day(trunc(sysdate),''SUN'')',
                no_parse => true);
commit;
end;

Open in new window

0
slightwv (䄆 Netminder) Commented:
The alert log should provide an error message on why the job is failing.

Then there would be no more guessing as to why it is failing.

Until we get the error being generated, all we are doing is guessing.
0
anumosesAuthor Commented:
Originally this was

--dbms job run every sunday for proposed annual goal report
DECLARE
    x   NUMBER;
BEGIN
dbms_job.submit(job => X,
                what => 'wkly_proposed_goal_improve_prc' ,
                next_date => next_day(trunc(sysdate),'SUN'),
              interval => 'next_day(trunc(sysdate),''SUN'')',
                no_parse => true);
commit;
end;

Since I found failure, I changed it to run today and then every sunday
That failed too.

--dbms job run every sunday for proposed annual goal report
DECLARE
    x   NUMBER;
BEGIN
dbms_job.submit(job => X,
                what => 'wkly_proposed_goal_improve_prc' ,
               -- next_date => next_day(trunc(sysdate),'SUN'),
            next_date => to_date('24-Jul-2014 08:01','dd-Mon-yyyy hh24:mi'),
                interval => 'next_day(trunc(sysdate),''SUN'')',
                no_parse => true);
commit;
end;
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
That doesn't help either, I agree with slightwv: please check the alert log for errors!
0
slightwv (䄆 Netminder) Commented:
I'm thinking it isn't a problem with the dates or setup of the job itself.

It is either a permission problem or a problem inside the procedure.

We need the error being generated.
0
anumosesAuthor Commented:
thanks
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
I can only speak for myself, but I'm rather curious what the problem was and how you solved it?!?
0
anumosesAuthor Commented:
not solved yet
0
slightwv (䄆 Netminder) Commented:
If the issue isn't resolved, why close the question?
0
anumosesAuthor Commented:
this is a small company and we dont have a dba or admin. So cannot do a trace. My boss said to have a cron job. That was not my solution. The procedure has no errors as I debugged it.
0
slightwv (䄆 Netminder) Commented:
>>So cannot do a trace.

You don't need to trace anything.  You just need to check the alert log and look for errors.
0
anumosesAuthor Commented:
I am not sure not I have the steps to check the alert log. I was going through the link provided for documentation.

If the parameter is not set, the trace files are stored in the ORACLE_BASE\ADMIN\db_name\bdump directory. But was not able to get here. So I am back to square one.
0
slightwv (䄆 Netminder) Commented:
Connect to the database as SYS or SYSTEM.

Then issue:
show parameter background_dump_dest


That is the folder where it is located.


On a side note:
With not having a DBA/Admin, how do you know everything is being backed up or if there are any problems?
0
anumosesAuthor Commented:
My boss does some of it and database backup is done by another person.
0
slightwv (䄆 Netminder) Commented:
The Oracle alert log is a main file that should be reviewed on a regular basis.  Someone should be doing this.  It is one of the first places potentially dangerous issues are logged.
0
anumosesAuthor Commented:
/oracle/admin/phbc/bdump/phbc_j000_7549.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle/product/9.2.0.8
System name:      HP-UX
Node name:      heart1
Release:      B.11.11
Version:      U
Machine:      9000/800
Instance name: phbc
Redo thread mounted by this instance: 1
Oracle process number: 42
Unix process pid: 7549, image: oracle@heart1 (J000)

*** SESSION ID:(88.30562) 2014-07-24 10:11:30.060
*** 2014-07-24 10:11:30.060
ORA-12012: error on auto execute of job 320
ORA-06550: line 1, column 119:
PLS-00103: Encountered the symbol "" when expecting one of the following:
   := . ( @ % ;
The symbol ";" was substituted for "" to continue.
0
slightwv (䄆 Netminder) Commented:
Try changing:
what => 'wkly_proposed_goal_improve_prc' ,

to:
what => 'wkly_proposed_goal_improve_prc;' ,
0
anumosesAuthor Commented:
No failures now
0
anumosesAuthor Commented:
thanks a lot
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.