Solved

setting up a job

Posted on 2004-09-20
18
564 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
  • 5
  • 5
  • 4
  • +1
18 Comments
 

Author Comment

by:Imanmalik
Comment Utility
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 47

Expert Comment

by:schwertner
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
 

Author Comment

by:Imanmalik
Comment Utility
could you please expalin me little more i m confused since i never did this before .
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 76

Expert Comment

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

slightwv, seazodiac and schwertner
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

744 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now