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')
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.
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
--The proc def
CREATE OR REPLACE
--declare any vars here
insert into <your temp table name>
from T_SYSTEM_PARAMS scp,
set serveroutput on
sInput varchar(300) := '';
sInput := 'begin DoIt(); end;';
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));
when others then
dbms_output.put_line('error '||to_char(sqlcode)||': '||sqlerrm);