[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

oracle failure error on scheduled job  using dbms_job.submit

Posted on 2014-07-24
32
Medium Priority
?
1,983 Views
Last Modified: 2014-07-28
--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?
0
Comment
Question by:anumoses
  • 15
  • 12
  • 4
31 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40216605
Check the alert log to see if there is any indication of why the job is failing.
0
 
LVL 6

Author Comment

by:anumoses
ID: 40216625
How can I check the alert log?
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 40216635
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40216702
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
 
LVL 6

Author Comment

by:anumoses
ID: 40216715
valid
failure
0
 
LVL 6

Author Comment

by:anumoses
ID: 40216720
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40216721
Those images don't help.

The alert log should tell you why it is failing.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40216723
>>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
 
LVL 6

Author Comment

by:anumoses
ID: 40216727
through user creating the job.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40216728
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
 
LVL 6

Author Comment

by:anumoses
ID: 40216763
In my case dbms_job owner is hbc_data
0
 
LVL 6

Author Comment

by:anumoses
ID: 40216771
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
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40216774
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40216782
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
 
LVL 6

Author Comment

by:anumoses
ID: 40216786
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
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40216792
That doesn't help either, I agree with slightwv: please check the alert log for errors!
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40216798
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
 
LVL 6

Author Comment

by:anumoses
ID: 40223927
thanks
0
 
LVL 14

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 40223984
I can only speak for myself, but I'm rather curious what the problem was and how you solved it?!?
0
 
LVL 6

Author Comment

by:anumoses
ID: 40223988
not solved yet
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40223993
If the issue isn't resolved, why close the question?
0
 
LVL 6

Author Comment

by:anumoses
ID: 40223999
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
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40224034
>>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
 
LVL 6

Author Comment

by:anumoses
ID: 40224076
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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 40224113
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
 
LVL 6

Author Comment

by:anumoses
ID: 40224116
My boss does some of it and database backup is done by another person.
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 40224137
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
 
LVL 6

Author Comment

by:anumoses
ID: 40224252
/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
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 40224265
Try changing:
what => 'wkly_proposed_goal_improve_prc' ,

to:
what => 'wkly_proposed_goal_improve_prc;' ,
0
 
LVL 6

Author Comment

by:anumoses
ID: 40224272
No failures now
0
 
LVL 6

Author Closing Comment

by:anumoses
ID: 40224273
thanks a lot
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question