ORA-06512: at "SYS.DBMS_IJOB", line 406

I AM TRYING TO AUTOMATE\SCHEDULE A JOB THAT RUNS MY SPROC EMBEDDED WITHIN A PL\SQL PACKAGE.  NOT SURE WHY THE DBMS_JOB.RUN DOES NOT EXECUTE MY JOB, PROPERLY?

ERROR at line 1:
ORA-12011: execution of 1 jobs failed
ORA-06512: at "SYS.DBMS_IJOB", line 406
ORA-06512: at "SYS.DBMS_JOB", line 272
ORA-06512: at line 1

Below is my DBMS_JOB.SUBMIT statement:
DECLARE
    x   NUMBER;
BEGIN
 
    SYS.DBMS_JOB.submit
        (job             => x,
         what            => 'webfolio_empty.BLOOMBERGQUOTEINSERT.INSERT_QUOTE;',
         next_date       => SYSDATE,
         INTERVAL        => 'trunc(sysdate) + 18/24 + 10/1440',
         no_parse        => FALSE
        );
        
    SYS.DBMS_OUTPUT.put_line('Job Number is: ' || TO_CHAR(x));
    COMMIT;
END;

Open in new window

ajayphilipAsked:
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.

sdstuberCommented:
are you getting an error when the job runs, or when the job is submitted?
If it's when the job runs, then it's a problem in the procedure itself


also note, your interval is somewhat problematic.


INTERVAL        => 'trunc(sysdate) + 18/24 + 10/1440',

This schedules the job to run today at 6:10pm,  so, if the job finishes at 6:15, it will resubmit itself for 5 minutes ago.

I think you want it to be like this... so it runs every day.

INTERVAL        => 'trunc(sysdate+1) + 18/24 + 10/1440',
0
ajayphilipAuthor Commented:
there are two issues:  the job does not run when scheduled with the appropriate time interval and when I try to force a test run using dbms_job.run -it gives me that error.  I know the time interval I posted was somewhat problematic.


When I execute the procedure itself - it runs fine.


SQL> EXEC webfolio_empty.BLOOMBERGQUOTEINSERT.INSERT_QUOTE;

PL/SQL procedure successfully completed.
0
sdstuberCommented:
does the user running the procedure in the job have sufficient privileges?  What are the errors in the alert log and trace file (if present)

there should be an underlying error below the 6512 exception
0
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.

schwertnerCommented:
If you use this procedure to gather DB statistics this can help:

grant select any table to ops$oracle;

Also think about bugs (http://www.pythian.com/blogs/375/no-data-found-success-in-dbms_scheduler):
Oracle was pointing us to unpublished bug #4552696 . They said a patch is available for 11 and offered to backport the patch.

0
sdstuberCommented:
schwertner, I think you accidentally posted to the wrong question, must be answering too many people today.  :)

The above link doesn't seem to apply to this thread except for the final example which merely shows a job failing because the underlying procedure is broke
and there isn't anything to suggest this has anything to do with statistics,  in fact, based on the procedure name, it would seem to be financial related.
0
MarkusIdCommented:
Maybe you run into an privileges issue, you cannot use privileges in dbms_job that are granted through a role. Also have a look at this: http://www.experts-exchange.com/Database/Oracle/PL_SQL/Q_23845129.html

0
ajayphilipAuthor Commented:
Turns out this was a permission issue.  I thought setting up a job using the 'system' account should not pose any problems.  I am working in a development db under the system login and I didn't realize that the system account could also have limitations?  

The job was owned by system and system had no rights to run the sproc nor insert into the specific tables.

I've deleted the job and reissued the dbms_job.submit under a valid login to the database with proper rights.

The job is now functioning.



0
sdstuberCommented:
If it was a permission problem, you should accept post 22876941.
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
ajayphilipAuthor Commented:
22876941 WAS THE FIRST ANSWER
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.