Creating job in Oracle

Hi experts, I am trying to create a new job:
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'BEGIN HISR_RETEN.p_hisr_retention_all; END;'
   ,next_date => to_date('05/09/2013 13:20:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(HISR_UTILITY.f_hisr_sysdate_plus_offset+1/24,''HH'')+(20/(60*24))'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

Open in new window

I think the code is correct, the job is created...
But when I do:
select job, what from user_jobs;

Open in new window

this new job, not appears!
Also, when I tried executing (using Toad tools), the job not appear from the Schema Browser.
Could you me give any idea which is the problem?
Thankyou!
carlino70Asked:
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.

Alexander Eßer [Alex140181]Software DeveloperCommented:
I suppose the problem is the interval 'TRUNC(HISR_UTILITY.f_hisr_sysdate_plus_offset+1/24,''HH'')+(20/(60*24))'
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
slightwv (䄆 Netminder) Commented:
I have to assume you are running both of those from the same database user.

Do you see the dbms_output with the job number when you run the submit?

For grins, try dba_jobs (from a user that can see the dba views):
select job, what from dba_jobs;
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
are you sure, the command above executes with no errors?! like ORA-23319?! or ORA-23420, cause next_date is "too old" ?!
0
The Ultimate Tool Kit for Technolgy Solution Provi

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 for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Alexander Eßer [Alex140181]Software DeveloperCommented:
what does this shows?!

select a.value
  from v$parameter a
 where a.name = 'job_queue_processes';

Open in new window

0
slightwv (䄆 Netminder) Commented:
>>where a.name = 'job_queue_processes';

I believe that will only keep it from executing.  It should still show up in the jobs view if it created successfully.
0
carlino70Author Commented:
Hi people:

I suppose the problem is the interval 'TRUNC(HISR_UTILITY.f_hisr_sysdate_plus_offset+1/24,''HH'')+(20/(60*24))'
I am using the same kind of interval in others jobs, without problems.

I have to assume you are running both of those from the same database user.
Yes, the schema is the owner, in both.

Do you see the dbms_output with the job number when you run the submit?
Yes, every time the code is executed, I see the new job number

are you sure, the command above executes with no errors?! like ORA-23319?! or ORA-23420, cause next_date is "too old" ?!
I don´t see any error when the creation lines are executed.

For grins, try dba_jobs (from a user that can see the dba views):
select job, what from dba_jobs;

I see the same rows in dba_jobs too:
1	EMD_MAINTENANCE.EXECUTE_EM_DBMS_JOB_PROCS();
9708	BEGIN HISR_FUTURE.p_hisr_future_all; END;
8937	BEGIN HISR_ARCHIVE.p_archive_file; END;
7056	--Update Maximo Value- 
begin pkg_pointnumber_max_value.UPDATE_ALL_POINTNUMBER(current_date, NULL, TRUE);

Open in new window

0
slightwv (䄆 Netminder) Commented:
Is this a single instance or RAC cluster?

If the job submits then it sort of has to be in dba_jobs.  I have to ask:  Are you sure you are connected to the same database?
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
@slightwv: does it make a difference when using/running RAC?!
0
slightwv (䄆 Netminder) Commented:
I'm not a RAC person.  I was only thinking of anything that might be causing the askers issues.

If the job submits and provides a job number, the job should be in dba_jobs.  If it isn't, then something "not normal" is going on.  Just trying to think of what the possibilities are.

>>does it make a difference when using/running RAC?!

It shouldn't but I found a thread that states dbms_job isn't RAC aware and did some weird things and that you should use dbms_scheduler which is RAC aware.
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
It shouldn't but I found a thread that states dbms_job isn't RAC aware
Yeah, me too. Apart from that, it is (strongly) advised to use dbms_scheduler as the scheduler is RAC aware...

http://oradbatips.blogspot.de/2007/03/tip-31-jobs-in-dbajobs-are-not-running.html

http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=44783

https://forums.oracle.com/thread/2409112

http://tamimdba.wordpress.com/category/how-to/dbms_packege/dbms_job/
0
carlino70Author Commented:
I'm not working on a RAC instance, is a single instance.

Please follow the sequence of steps performed:

noa1pdsora:/xahome/oracle> sqlplus someuser/somepassword@xa21

SQL*Plus: Release 10.2.0.3.0 - Production on Thu Sep 5 17:21:22 2013

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

SQL> select job, what from user_jobs;

       JOB WHAT
--------------------------------------------------------------------------------
      9708 BEGIN HISR_FUTURE.p_hisr_future_all; END;
      8937 BEGIN HISR_ARCHIVE.p_archive_file; END;
      7056 --Update Maximo Value- begin pkg_pointnumber_max_value.UPDATE_ALL_POINTNUMBER(current_date, NULL, TRUE);
--------------------------------------------------------------------------------

SQL>
SQL> set serveroutput on
SQL> DECLARE
  2    X NUMBER;
  3  BEGIN
  4    SYS.DBMS_JOB.SUBMIT
  5    ( job       => X
  6     ,what      => 'BEGIN HISR_RETEN.p_hisr_retention_all; END;'
  7     ,next_date => to_date('05/09/2013 14:20:00','dd/mm/yyyy hh24:mi:ss')
  8     ,interval  => 'TRUNC(HISR_UTILITY.f_hisr_sysdate_plus_offset+1/24,''HH'')+(20/(60*24))'
  9     ,no_parse  => FALSE
 10    );
 11    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
 12  COMMIT;
 13  END;
 14  /
Job Number is: 36347

PL/SQL procedure successfully completed.


SQL> select job, what from user_jobs;

       JOB WHAT
--------------------------------------------------------------------------------
      9708 BEGIN HISR_FUTURE.p_hisr_future_all; END;
      8937 BEGIN HISR_ARCHIVE.p_archive_file; END;
      7056 --Update Maximo Value- begin pkg_pointnumber_max_value.UPDATE_ALL_POINTNUMBER(current_date, NULL, TRUE);
--------------------------------------------------------------------------------
      
SQL>

Open in new window

The same with:
select job, what from dba_jobs; 

       JOB WHAT
--------------------------------------------------------------------------------
      9708 BEGIN HISR_FUTURE.p_hisr_future_all; END;
      8937 BEGIN HISR_ARCHIVE.p_archive_file; END;
      7056 --Update Maximo Value- begin pkg_pointnumber_max_value.UPDATE_ALL_POINTNUMBER(current_date, NULL, TRUE);

Open in new window


Doesn´t appear the new job.
0
carlino70Author Commented:
Testing, when I execute:
DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'BEGIN HISR_FUTURE.p_hisr_report_all; END;'
   ,next_date => to_date('05/09/2013 18:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(HISR_UTILITY.f_hisr_sysdate_plus_offset+1/24,''HH'')'
   ,no_parse  => FALSE
  );
 -- SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
--COMMIT;
END;
/

Open in new window


without "commit" and SYS.DBMS_OUTPUT.PUT_LINE
select job, what from user_jobs;
I see the new job;
JOB	WHAT
9708	BEGIN HISR_FUTURE.p_hisr_future_all; END;
8937	BEGIN HISR_ARCHIVE.p_archive_file; END;
7056	--Update Maximo Value- 
begin pkg_pointnumber_max_value.UPDATE_ALL_POINTNUMBER(current_date, NULL, TRUE);
36358	BEGIN HISR_FUTURE.p_hisr_report_all; END;

Open in new window

When I do 'COMMIT':
commit;

Open in new window

select job, what from user_jobs;

Open in new window

the new job, dissapear!
JOB	WHAT
9708	BEGIN HISR_FUTURE.p_hisr_future_all; END;
8937	BEGIN HISR_ARCHIVE.p_archive_file; END;
7056	--Update Maximo Value- 
begin pkg_pointnumber_max_value.UPDATE_ALL_POINTNUMBER(current_date, NULL, TRUE);

Open in new window

0
Alexander Eßer [Alex140181]Software DeveloperCommented:
This is from official doc:
For the submitted job to run, you must issue a COMMIT statement immediately after the DBMS_JOB.SUBMIT statement.

Try to switch the commit and the dbms_output, so the commit is done immediately after the submit...
0
Alexander Eßer [Alex140181]Software DeveloperCommented:
0
slightwv (䄆 Netminder) Commented:
OK,
Now I'm caught up to Alex140181's first post.  Looks like the interval isn't in the future.

What does the following return:
select TRUNC(HISR_UTILITY.f_hisr_sysdate_plus_offset+1/24,'HH') from dual;

It must not be returning a time in the future.
0
carlino70Author Commented:
Thank slightwv!

Answering both:

"Try to switch the commit and the dbms_output, so the commit is done immediately after the submit... "
I have run the script creation, changing the location of the sentence 'COMMIT', and there was no difference. The job was not created.

"What does the following return:"
Executing:
select TRUNC(HISR_UTILITY.f_hisr_sysdate_plus_offset+1/24,'HH') from dual;

Open in new window


TRUNC(HIS
---------
SQL>

nothing!

Maybe a problem with the data dictionary?, Some parameter to be reviewed?
About:
job_queue_proceses = 10

Thanks!
0
slightwv (䄆 Netminder) Commented:
If the select I posted returns null, that is the problem.

That is the same function you are using as the interval for the job.  A null value means it relies on the NEXT_DATE.  If the job doesn't resubmit itself, it will run once and go away.


>>Maybe a problem with the data dictionary?, Some parameter to be reviewed?

No.  The problem is with the function you are calling.  You need to figure out why it is returning a null and not some interval value.
0
carlino70Author Commented:
ok, I understand. I'll try using another way to indicate the interval

thanks again!
0
slightwv (䄆 Netminder) Commented:
>>I am using the same kind of interval in others jobs, without problems.

If you've used that same thing in the past, then it is obviously broken and should be addressed.
0
carlino70Author Commented:
Thanks!
It works changing the way to take the interval
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.