Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Posts
    36

    Unanswered: doubt in dbms_job

    hello

    i have a doubt in dbms_job procedure

    i created a job.
    a job must run with a select statement
    that is

    dbms_job.submit('jobno','proc;',<select qry>)

    can i pass a select query with in a job.the job should run by fetching the date and time
    from database and execute every time.and that select statement is join statement from two
    tables.


    if it is possible how can we do plz can any body explain me

    Thanks

  2. #2
    Join Date
    Dec 2002
    Posts
    11

    Re: doubt in dbms_job

    What is the purpose of the select query? DBMS_JOB is for executing blocks of PLSQL/DML statements. Where are you expecting the output of the select to appear?

    I use dbms_job extensively as part of our projects, but only to run plsql code which modifies data.

    Bob

  3. #3
    Join Date
    Oct 2002
    Posts
    36

    dbms_job doubt

    thanks for your reply but myrequirement iam clearly explaining here as follows plz go thorough thatand suggestme an appropriate solution


    select scp.VOLUME_COMM_CALCULATE_TIME,tlm.TIME_LAP_DESC
    from T_SYSTEM_PARAMS scp,T_TIME_MASTER tlm
    where scp.TIME_LAP_ID=tlm.TIME_LAP_ID;

    VOLume_comm TIME_LAP_DESC
    ----------- ---------------
    19:35 Half Yearly


    so i want my job to be executed with the above qry. the time_lap_id will be changed accordingly the interval also
    will be changed. now according to my time_lap_id the time is half yearly at 19:35 this can be changed dynamically.
    from the frontend application. so how could we incorporate the above qry with in dbms_job procedure.some times itcan
    be half yearly or weekly ordaily what ever option the user select from front end accordingly it should change in the query
    and the job should get executed.

    dbms_job.submit(:jobid,'procedure','i need selectqry here to be executedthe job dynamically')

  4. #4
    Join Date
    Dec 2002
    Posts
    11
    The point is, there is no point in putting a select in DBMS_JOB - you will never see the result set...

    You could write some plsql that did the select and wrote that out to a secondary table periodically by running that as a job. Better would be to write a procedure that wrote the data out and call that from dbms_job. Then the user selects from the secondary table.

    A better solution to this would probably be a view that the user could select at any time, or include that select in the application.

    Each job when it is run from dbms_job will start in a new session (one of a pool, the number in the pool being determined by the parameter job_queue_processes) which mean that that session will get the resultset of the query, then discard it as the job ends.


    Bob



    select * from v$parameter where name like '%job%'

    job_queue_processes should be > 0 - default is 0 I think, no jobs will ever run.

    job_queue_interval - Granularity of the job queue - default 60, shouldn't be a problem

  5. #5
    Join Date
    Oct 2002
    Posts
    36

    dbms_job doubt

    can u plz tell me how to write that procedure how could we call that in the dbms_job plz... it is very urgent

  6. #6
    Join Date
    Dec 2002
    Posts
    11

    Re: dbms_job doubt

    --The proc def
    CREATE OR REPLACE
    Procedure DoIt()
    is
    --declare any vars here
    begin
    insert into <your temp table name>
    (select scp.VOLUME_COMM_CALCULATE_TIME,tlm.TIME_LAP_DESC
    from T_SYSTEM_PARAMS scp,
    T_TIME_MASTER tlm
    where scp.TIME_LAP_ID=tlm.TIME_LAP_ID);


    end;
    /
    --then dbms_alert
    set serveroutput on
    declare
    job pls_integer;
    sInput varchar(300) := '';
    begin
    sInput := 'begin DoIt(); end;';
    sys.dbms_job.submit(job,
    what => sInput ,
    next_date => sysdate,
    interval => 'sysdate + 0.000694', -- just sysdate to run once, but reains in teh queue
    no_parse => false);

    -- output the results
    dbms_output.put_line('job = '||to_char(job));

    commit;
    exception
    when others then
    dbms_output.put_line('error '||to_char(sqlcode)||': '||sqlerrm);
    end;

    Not tested but should work ish!

    Bob

Posting Permissions

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