Solved

setting up a job

Posted on 2004-09-20
18
569 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
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 47

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 76

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

 

Author Comment

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

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 76

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 76

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 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12606893
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.

Question has a verified solution.

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

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

809 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