Solved

setting up a job

Posted on 2004-09-20
18
573 Views
Last Modified: 2008-03-03
i have following procedure that needs to run every midnight can some one help me how to set up or perhaps create a procedure that will run every midnight
0
Comment
Question by:Imanmalik
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
  • 4
  • +1
18 Comments
 

Author Comment

by:Imanmalik
ID: 12102542
CREATE OR REPLACE procedure BuildApprovalsData is
begin
      
      
      
       begin
         execute immediate 'drop table hmis_requests.fyi_sysdata';
        exception when others then null;
       end;
             
       begin
        execute immediate 'drop table hmis_requests.fyi_ohma_doc';
        exception when others then null;
       end;
             
       begin
        execute immediate 'drop table hmis_requests.fyi_ohma_wf';
        exception when others then null;
       end;      
      
       begin
        delete from hmis_requests.tblDATASET_HYDRO;
        exception when others then null;
       end;
             
       begin
      delete from hmis_requests.tblDATASET_SYMBOL;
        exception when others then null;
       end;
             
       begin
        delete from hmis_requests.tbl_HYDRO;
        exception when others then null;
       end;
             
       begin
        delete from hmis_requests.tbl_SYMBOL;
        exception when others then null;
       end;
             
      
       execute immediate 'create table hmis_requests.fyi_sysdata as  select * from fyiadm.fyi_sysdata ';
        execute immediate 'create table hmis_requests.fyi_ohma_doc as select * from fyiadm.fyi_ohma_doc ';
        execute immediate 'create table hmis_requests.fyi_ohma_wf as select * from fyiadm.fyi_ohma_wf ';
      
      
      insert into hmis_requests.TBLDATASET_HYDRO
(SYS_FKEY, SYS_DKEY, IDNO, COMPANY, FIRST_NAME,
 LAST_NAME, JOB_TITLE, ADDRESS1, ADDRESS2, CITY,
 STATE, ZIP_CODE, COUNTRY, PHONE, HYDROSTATIC_FLAG,
 ACETYLENE_FLAG, ULTRASONIC_FLAG, ACOUSTIC_FLAG, OTHER_REQUEST, SPEC,
 CODE_TYPE, CLTST, COMMENTS, RECEIVED_DATE, APPROVAL_DATE,
 INSPECTOR_NAME, INSPECTION_DATE, VIDEO_DATE, ALT_NAME, ALT_ADDRESS1,
 ALT_CITY, ALT_STATE, ALT_ZIPCODE)
SELECT
 A.sys_fkey, A.sys_dkey, C.fyi_reference_id,
 B.sys_author, C.fyi_first_name, C.fyi_last_name, C.fyi_position,
 C.fyi_address1, C.fyi_address2, C.fyi_city, C.fyi_state, C.fyi_postal_code, C.fyi_Country, C.fyi_phone,
 A.fyi_hydrostatic_flag, A.fyi_acetylene_flag, A.fyi_ultrasonic_flag, A.fyi_acoustic_flag, A.fyi_other_request,
 A.fyi_specification, A.fyi_code_type,
 B.sys_subject, B.sys_comments,
 to_date(C.fyi_received_date,'YYYY-MM-DD'),
 D.AP_date,
 D.Insp_name,
 D.Insp_date,
 D.Video_date,
C.FYI_ALT_NAME, C.FYI_ALT_ADDRESS, C.FYI_ALT_CITY, C.FYI_ALT_STATE, C.FYI_ALT_POSTAL_CODE
FROM hmis_requests.fyi_ohma_doc A, hmis_requests.fyi_sysdata B,hmis_requests.Fyi_ohma_wf C,
     (SELECT MAX(to_date(fyi_approval_date,'RRRR-MM-DD')) AP_DATE,
               MAX(fyi_inspector_name)  Insp_Name,
               MAX(to_date(fyi_inspection_date,'RRRR-MM-DD')) Insp_date,
                   MAX(to_date(fyi_video_date,'RRRR-MM-DD')) Video_date,
                   fyi_Reference_Id fyiRefId,
                   sys_Fkey sysFKey
        FROM   hmis_requests.fyi_ohma_doc
        GROUP BY sys_fkey,fyi_reference_id ) D
WHERE
      A.fyi_disposition='Approve' AND
        ((SUBSTR(A.fyi_reference_id,1,1) IN ('A','B','C','D','E','F','G','H','I','J','V')) AND
     (SUBSTR(A.fyi_reference_id,1,2) NOT IN ('AN','CA','CO','CT','EX','IN'))) AND
      B.sys_title='Request' AND
      B.SYS_DELETE_DATE IS NULL AND
      A.SYS_FKEY=B.sys_fkey AND
      A.sys_dkey=B.sys_dkey AND
      A.sys_ver_major=B.sys_ver_major AND
      A.sys_ver_minor=B.sys_ver_minor AND
      C.sys_fkey=B.sys_fkey AND
        D.sysFkey = A.sys_fkey AND
        D.fyiRefId = A.fyi_reference_id
       ORDER BY 1;            
             
            commit;


insert into HMIS_requests.tbl_HYDRO
(SYS_FKEY, SYS_DKEY, IDNO, COMPANY, FIRST_NAME,
 LAST_NAME, JOB_TITLE, ADDRESS1, ADDRESS2, CITY,
 STATE, ZIP_CODE, PHONE, HYDROSTATIC_FLAG, ACETYLENE_FLAG,
 ULTRASONIC_FLAG, ACOUSTIC_FLAG, OTHER_REQUEST, SPEC, CODE_TYPE,
 CLTST, COMMENTS, RECEIVED_DATE, APPROVAL_DATE, INSPECTOR_NAME,
 INSPECTION_DATE, VIDEO_DATE)
SELECT   sys_fkey,
         sys_dkey,
             idno,
             NVL(alt_name, company) company,
             first_name,
             last_name,
             job_title,
         NVL(alt_address1, address1) address1,
         DECODE (NVL (alt_address1, 'N'), 'N', address2, NULL) address2,
         DECODE (NVL (alt_address1, 'N'), 'N', city, alt_city) city,
         DECODE (NVL (alt_address1, 'N'), 'N', state, alt_state) state,
         DECODE (NVL (alt_address1, 'N'), 'N', zip_code,alt_zipcode) zipcode,
             phone,
             hydrostatic_flag,
         acetylene_flag,
             ultrasonic_flag,
             acoustic_flag,
             other_request,
             spec,
         code_type,
             cltst,
             comments,
         received_date rdate,
             approval_date apdat,
         inspector_name,
             inspection_date idate,
         video_date video
    FROM hmis_requests.tbldataset_hydro a,
         (SELECT   max(approval_date) approvaldate,
                   idno gidno
              FROM hmis_requests.tbldataset_hydro b
          GROUP BY idno) b
   WHERE a.approval_date = b.approvaldate
     AND a.idno = b.gidno
ORDER BY idno;

commit;


insert into HMIS_REQUESTS.TBLDATASET_SYMBOL
(SYS_FKEY, SYS_DKEY, SYS_VER_MAJOR, FYI_REFERENCE_ID, SYS_AUTHOR,
 FYI_FIRST_NAME, FYI_LAST_NAME, FYI_POSITION, FYI_ADDRESS1, FYI_ADDRESS2,
 FYI_CITY, FYI_STATE, FYI_POSTAL_CODE, PH, FYI_PHONE,
 FYI_DRUM_REQUEST, FYI_PAIL_REQUEST, CYL, FYI_OTHER_REQUEST, FYI_SPECIFICATION,
 FYI_REGULATION, FYI_CODE_TYPE, SYS_SUBJECT, SYS_COMMENTS, FYI_RECEIVED_DATE,
 TMP, SYS_DATE_CREATED, AP_DATE, ALT_COMPANY, ALT_ADDRESS1,
 ALT_CITY, ALT_STATE, ALT_ZIP)
SELECT
 A.sys_fkey,
 A.sys_dkey,
 a.sys_ver_major,
 C.fyi_reference_id,
 B.sys_author,
 C.fyi_first_name,
 C.fyi_last_name,
 C.fyi_position,
 C.fyi_address1,
 C.fyi_address2,
 C.fyi_city,
 C.fyi_state,
 C.fyi_postal_code,
 SUBSTR(C.fyi_intl_phone,1,14) ph,
 C.fyi_phone,
 D.Drum,
 D.Pail,
 D.Cylinder,
 D.Other,
 D.FYI_Spec,
 D.Regulation,
 D.Code_Type,
 B.sys_subject,
 B.sys_comments,
 to_date(C.fyi_received_date,'RRRR-MM-DD'),
 NVL(SUBSTR(b.sys_subject,23,10),'') tmp,
 to_date(B.sys_date_created,'RRRR-MM-DD'),
 D.AP_Date,
 C.fyi_alt_name,
 C.fyi_alt_address,
 C.fyi_alt_city,
 C.fyi_alt_state,
 C.fyi_alt_postal_code
FROM hmis_requests.fyi_ohma_doc A, hmis_requests.fyi_sysdata B,hmis_requests.Fyi_ohma_wf C,
     (SELECT MAX(to_date(fyi_approval_date,'RRRR-MM-DD')) AP_DATE,
                   fyi_Reference_Id fyiRefId,
                   sys_Fkey sysFKey,
              MAX(fyi_drum_request) Drum,
                   MAX(fyi_pail_request) Pail,
                   MAX(fyi_cylinder_request) Cylinder,
                   MAX(fyi_other_request) Other,
                   MAX(fyi_specification) FYI_Spec,
                   MAX(fyi_regulation) Regulation,
                   MAX(fyi_code_type) Code_Type
        FROM   hmis_requests.fyi_ohma_doc
        GROUP BY sys_Fkey, fyi_reference_id ) D
WHERE
    NVL(B.sys_subject, ' ') <> 'LT MN CT RT Process' AND
      substr(A.fyi_reference_id,1,1)='M' AND
      B.SYS_DELETE_DATE IS NULL AND
      A.fyi_reference_id <> 'M5561' AND
      A.fyi_reference_id <> 'M5602' AND
    A.SYS_FKEY=B.sys_fkey AND
    A.sys_dkey=B.sys_dkey AND
    A.sys_ver_major=B.sys_ver_major AND
    A.sys_ver_minor=B.sys_ver_minor AND
      C.sys_fkey=B.sys_fkey AND
      D.sysFkey = A.sys_fkey AND
      D.fyiRefId = A.fyi_reference_id
ORDER BY A.fyi_reference_id;
             
commit;

            insert into hmis_requests.tbl_SYMBOL
(SYS_FKEY, SYS_DKEY, MAJOR_VERSION, IDNO, COMPANY,
 FIRST_NAME, LAST_NAME, JOB_TITLE, ADDRESS1, ADDRESS2,
 CITY, STATE, ZIP_CODE, IPHONE, PHONE,
 DRUM, PAIL, CYLINDER, OTHER, SPEC,
 CODE_TYPE, REGULATIONS, COMMENTS, RECEIVED_DATE, TEMPDATE,
 DE, APPROVAL_DATE)
SELECT sys_fkey, sys_dkey, sys_ver_major, fyi_reference_id,
          NVL (alt_company, sys_author) company, fyi_first_name,
          fyi_last_name, fyi_position,
          NVL (alt_address1, fyi_address1) address1,
              decode(alt_address1,null,fyi_address2,null) address2,
              decode(alt_address1,null,fyi_city,alt_city) city,
                decode(alt_address1,null,fyi_state,alt_state) state,
              decode(alt_address1,null,fyi_postal_code,alt_zip) zipcode,
          ph,
              fyi_phone,
              DECODE (NVL (fyi_drum_request, 'N'), 'N', 'N', 'n', 'N', 'Y') drum,
          DECODE (NVL (fyi_pail_request, 'N'), 'N', 'N', 'n', 'N', 'Y') pail,
          DECODE (NVL (cyl, 'N'), 'N', 'N', 'n', 'N', 'Y') cylinder,
          fyi_other_request, fyi_specification, fyi_code_type, fyi_regulation,
          sys_comments,
              fyi_received_date rdate,
          tmp tempdate,
          sys_date_created dow,
              ap_date apdate
     FROM hmis_requests.tbldataset_symbol a,
          (SELECT
                  a.sys_fkey mfkey,
                  a.sys_dkey mdkey,
                  MAX (a.sys_ver_major) mmver,
                  a.fyi_reference_id refid,
                  a.ap_date apdate
                  FROM hmis_requests.tbldataset_symbol a,(
                  select max(a.sys_dkey) sys_dkey,a.sys_fkey,a.fyi_reference_id,a.ap_date
                  from hmis_requests.tbldataset_symbol a,(
                  select max(a.sys_fkey) sys_fkey,a.fyi_reference_id,a.ap_date
                  from hmis_requests.tbldataset_symbol a, (SELECT MAX (ap_date) ap_date,fyi_reference_id
                  FROM hmis_requests.tbldataset_symbol
                  group by fyi_reference_id) b
                  where a.fyi_reference_id=b.fyi_reference_id
                  and   a.ap_date = b.ap_date
                  group by a.fyi_reference_id,a.ap_date) b
                  where a.fyi_reference_id=b.fyi_reference_id
                  and   a.ap_date = b.ap_date
                  and   a.sys_fkey = b.sys_fkey
                  group by a.sys_fkey,a.fyi_reference_id,a.ap_date) b
                  where a.fyi_reference_id=b.fyi_reference_id
                  and   a.ap_date = b.ap_date
                  and   a.sys_fkey = b.sys_fkey
                  and   a.sys_dkey = b.sys_dkey
                  group by a.sys_fkey,a.sys_dkey,a.fyi_reference_id,a.ap_date) r
    WHERE r.refid = a.fyi_reference_id
      AND r.mfkey = a.sys_fkey
      AND r.mdkey = a.sys_dkey
      AND r.mmver = a.sys_ver_major;

commit;

exception when others then null;


end;
0
 
LVL 48

Expert Comment

by:schwertner
ID: 12102598
1. Create the procedure or job
CREATE OR REPLACE PROCEDURE compute_statistics IS
BEGIN
    -- some actions like:
    -- dbms_stats.gather_database_stats(cascade=>true);
END compute_statistics;

2. Schedule it (in this case once a week)

set serveroutput on
set linesize 10000
variable x number;
begin
   DBMS_OUTPUT.enable(100000);  
   dbms_job.submit(:x,'compute_statistics;',trunc(sysdate),'trunc(sysdate+7)');
   commit;
   dbms_output.put_line(TO_char(:x));
end;
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12105274
Just wanted to add:
  You must have the parameter: job_queue_processes set to something greater than 0
and
  If you are in 8i or below you must also set: job_queue_interval to an appropriate level.

You also need to take care when setting the next_date.  It may be possible for the execution time to slip around and not run at EXACTLY midnight (I don't know about 9i and above.  This used to be a real problem in 8i and below).
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!

 

Author Comment

by:Imanmalik
ID: 12105544
could you please expalin me little more i m confused since i never did this before .
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12105795
I suggest that you start by scanning the docs for dbms_job:
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10802/d_job.htm

schwertner provided a stub procedure and the the necessary call to dbms_job.submit to run thae stub every 7 days.

Other than that, what specific parts are confusing?

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12107991
Imanmalik:

try to execute this in SQLPLUS , log in as sysdba, or the user (who have the privs to create job)

SQL> variable jobno number;
SQL> exec dbms_job.submit(:jobno, 'begin BuildApprovalsData; end;', trunc(sysdate) + 12*60*60, 'trunc(sysdate) + 1 + 12*60*60');
SQL>commit;


or


as a block of Plsql code:

declare

jobno number;
begin
dbms_job.submit(:jobno, 'begin BuildApprovalsData; end;', trunc(sysdate) + 12*60*60, 'trunc(sysdate) + 1 + 12*60*60');
end;
/

commit;

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12108061
slightwv also got a good point.

you have to make sure JOB_QUEUE_PROCESSES greater than 0.


but you don't need to set JOB_QUEUE_INTERVAL because it will take a default of 60 secs if left unset.

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12112217
seazodiac:  By not taking into account a proper setting for job_queue_interval, the job can slide by up to a minute and possibly run at 12:00:59 instead of 12:00:00 as they wanted.  

Imanmalik: job_queue_interval is the interval at which Oracle wakes up and looks for pending jobs.  If you take the default of 60 secs, Oracle will only look for jobs to run every minute.  For example:  Oracle looks for pending jobs at: 11:59:59 and there is nothing to run.  Your job is set to run at: 12:00:00.  Oracle will not run your job until: 12:00:58 (A whole 58 seconds after you told it to run).
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12112341
slightwv:

exactly, that's my whole point that you can leave the JOB_QUEUE_INTERVAL alone to a default of 60 secs.

the asker wants the job to run at MIDNIGHT, let's consider the most stringent case. 12:00:00 AM sharp...

the oracle SNMP job monitor will wake up every minute, its cycle is independent of any job schedule, it's running on System clock.

that means if you schedule your job at  06:36:24 , you job will not be executed immediately , it will run at 06:37:00.
So this default of 1 minute interval will satisfy the asker's schedule of running job at MIDNIGHT, wouldn't you agree?

so on any minute, snmp job monitor will check, so leaving the default is fine.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12112826
seazodiac:  From what I've been told by Oracle (the big guns, not first line support):  The job monitor doesn't work that way.  I agree that it will wake up and check every minute but the time that it goes back to sleep can slide thereby causing the next startup to not be on the exact second of the last start.  With that said:  I had this conversation back when 9.0 was just releasesd and I was running 817 (the exact time period escapes me).  I had the opportunity to be in a con-call with some senior level Oracle people and was informed of this (again, not the typical senior consultants that are a dime a dozen.  The big names......).  I took them at their word and never really tried it for myself.

I guess a quick test would be to create a job that just inserts sysdate into a dummy table and let it run for a week or so and see if it is the exact same second.  You've peaked my curiosity and I'd love to try it but no longer have any 8i databases to try it with.

I'd love to take this offline and see if we can come up with some form of definitave test (I'll probably go ahead with my example on my 10g development DB just for my own piece of mind).  Hopefully since you're the moderator you have access to my email address.  Drop me an email is you wish.

I'm afraid if we keep going, poor  Imanmalik will be even more confused.
0
 

Author Comment

by:Imanmalik
ID: 12116082
i created the proc and now i m confused , i checked th job and it's here is the result it set to run on following yr

 1* SELECT JOB,TO_CHAR(NEXT_DATE,'DD-MON-YY HH:MI:SS')FROM SYS.DBA_JOBS
QL> /

      JOB TO_CHAR(NEXT_DATE,
--------- ------------------
 
       21 01-JAN-23 12:00:00
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12116229
can you try this and post the results?

log in to your database:

SQL>select sysdate from dual;


and also in your Operating system where the database sits, check out the system clock.


and also, what's your script to submit the job?

can you post the complete code?
0
 

Author Comment

by:Imanmalik
ID: 12121950
SQL> select sysdate from dual;

SYSDATE
---------
22-SEP-04


SQL> variable jobno number;
SQL> exec dbms_job.submit(:jobno, 'begin BuildApprovalsData; end;', trunc(sysdate) + 12*60*60, 'trunc(sysdate) + 1 + 12*60*60');
SQL>commit;
0
 
LVL 23

Accepted Solution

by:
seazodiac earned 500 total points
ID: 12122281
sorry, I give you wrong Next_date format, check out this (replace 12*60*60 with 1/2 , which represents the shifts in days, so MIDNIGHT should be half a day shift)


variable jobno number;
exec dbms_job.submit(:jobno, 'begin BuildApprovalsData; end;', trunc(sysdate) + 1/2, 'trunc(sysdate) +1/2');
commit;



0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12606893
I'm thinking a 3-way split:

slightwv, seazodiac and schwertner
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

724 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