Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    UK
    Posts
    52

    Unanswered: How to use DBMS_ALERT with DBMS_JOB.SUBMIT

    Hi Friends,

    I am basically looking for how to use DBMS_ALERT to find out whether the jobs submitted by DBMSJOBTEST procedure has actually completed. As of now I am querying the USER_JOBS table to see whether the jobs completed and I keep on querying until the job disappears.

    - select job, total_time from user_jobs;

    It would be of great help if someone could explain how I can achieve this i.e, once each job gets completed I get a confirmation, so that based on the confirmation I can execute some other procedure.

    Thanks,
    Gautam Paul

    The following are my procedures:
    --------------------------------

    CREATE OR REPLACE PROCEDURE "DBMSJOBTEST" as
    vJob number;

    begin
    dbms_job.submit(job => vJob,
    what => 'DBMSJOB ;');
    dbms_output.put_line('DBMSJOB Job ' || to_char(vJob));
    dbms_job.submit(job => vJob,
    what => 'DBMSJOB2 ;');
    dbms_output.put_line('DBMSJOB Job ' || to_char(vJob));
    commit;
    end;

    --------------------------------------------------------------

    CREATE OR REPLACE PROCEDURE "DBMSJOB" as
    vstart number;
    begin

    vStart := dbms_utility.get_time;

    for i in 1..1000000 loop
    -- dbms_output.put_line(i);
    insert into temp1 values (i,'name'||to_char(i)) ;
    end loop ;

    dbms_output.put_line('Elapsed time: ' ||
    round((dbms_utility.get_time - vStart)/100, 2) || ' hsecs' );

    end;

    -------------------------------------------------------------

    CREATE OR REPLACE PROCEDURE "DBMSJOB2" as
    vstart number;
    begin
    vStart := dbms_utility.get_time;
    for i in 1..1000000 loop
    -- dbms_output.put_line(i);
    insert into temp2 values (i,'name'||to_char(i)) ;
    end loop ;
    dbms_output.put_line('Elapsed time: ' ||
    round((dbms_utility.get_time - vStart)/100, 2) || ' hsecs' );
    end;
    -----------------------------------------------------------
    CREATE TABLE "TEMP1" ("ID" NUMBER, "NAME"
    VARCHAR2(50 byte))
    TABLESPACE "DATA" PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS
    255
    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 0)
    LOGGING

    CREATE TABLE "TEMP2" ("ID" NUMBER, "NAME"
    VARCHAR2(50 byte))
    TABLESPACE "DATA" PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS
    255
    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
    2147483645 PCTINCREASE 0)
    LOGGING
    Last edited by gpeee; 10-06-04 at 12:18. Reason: adding table structures

  2. #2
    Join Date
    Sep 2004
    Posts
    16
    Hi,
    You could go through this link to get an idea:

    http://asktom.oracle.com/pls/ask/f?p...:4712493580500

    --Vinita
    Last edited by vinitasinha; 10-07-04 at 07:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •