Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 14446
  • Last Modified:

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

0
ajayphilip
Asked:
ajayphilip
2 Solutions
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
ajayphilipAuthor Commented:
22876941 WAS THE FIRST ANSWER
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now