dbms_scheduler and off-line reports [message #300246] |
Thu, 14 February 2008 13:04 |
Justin Schoeman
Messages: 8 Registered: February 2008
|
Junior Member |
|
|
Hi all,
I am relatively new to Oracle, and am currently battling with a particularly irritating problem...
Basically, when generating reports, I need to 'background' the task. The report generator needs a grand total of one parameter (a key for a table).
This is seemingly rather easy to do.
1st option: create 'regular' job. Quick, and easy, but takes 0.5s(!) per call!
2nd option: use 'lightweight' job. A little more difficult, but still relatively easy. Creating a lightweight job takes a grand total of only 0.02 seconds (which is more what I am looking for). But when i pass a parameter, I run into some problems. In order to pass the parameter, I need to create the job in 'disabled' state, set the argument, then enable the job. And guess what - this takes 0.5s again!
Now, obviously, I much prefer the 0.02s option, to the 0.5s option, but how do I get the parameter to the job? The easiest solution, is to just use the name of the job as the key (since the name has to be unique anyway).
The question is, how do I get the name. i found this woderfull Oracle FAQ:
http://www.oracle.com/technology/products/database/scheduler/htdocs/scheduler_faq.html#A4361
"Can the name of the job be retrieved from within the job?
Yes.
Back to top"
A truly helpful FAQ that - tells me what I want to do can be done, but gives no clue as to how.
Any ideas? How can i get the job name? Or, is there a better way to get the parameters to the job?
Thanks!
Justin
|
|
|
Re: dbms_scheduler and off-line reports [message #300249 is a reply to message #300246] |
Thu, 14 February 2008 13:44 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
SQL> desc DBA_SCHEDULER_RUNNING_JOBS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
JOB_NAME VARCHAR2(30)
JOB_SUBNAME VARCHAR2(30)
JOB_STYLE VARCHAR2(11)
DETACHED VARCHAR2(5)
SESSION_ID NUMBER
SLAVE_PROCESS_ID NUMBER
SLAVE_OS_PROCESS_ID VARCHAR2(12)
RUNNING_INSTANCE NUMBER
RESOURCE_CONSUMER_GROUP VARCHAR2(32)
ELAPSED_TIME INTERVAL DAY(3) TO SECOND(2)
CPU_USED INTERVAL DAY(3) TO SECOND(2)
There are multiple ways for a session to obtain its own SID so,
SELECT JOB_NAME FROM USER_SCHEDULER_RUNNING_JOBS WHERE SESSION_ID = MYSID;
[Updated on: Thu, 14 February 2008 13:45] by Moderator Report message to a moderator
|
|
|
Re: dbms_scheduler and off-line reports [message #300250 is a reply to message #300246] |
Thu, 14 February 2008 14:01 |
Justin Schoeman
Messages: 8 Registered: February 2008
|
Junior Member |
|
|
I suppose the scheduler must guarantee that each job runs in it's own session, or else they wouldn't be able to run concurrently?
In that case, this is perfect, thanks! I don't suppose you have some pointers on the best ways to retrieve the session id?
Thanks,
Justin
|
|
|
|
|
Re: dbms_scheduler and off-line reports [message #300255 is a reply to message #300246] |
Thu, 14 February 2008 14:59 |
coleing
Messages: 213 Registered: February 2008
|
Senior Member |
|
|
note of warning on sys_context.
sys_context gives the parent process (eg parallel queries), wheras v$mystat where rownum = 1 will give your actual sid.
Also, checkout setting up a GLOBAL_CONTEXT for inter-session communications.
You can set the parameter in the global context, and then pick it up straight away in the job.
It is much faster than writing to a table, but slower than passing the parameter manually.
Global Contexts are available to all sessions, so are great for your monitoring software too.
One thing I have done with jobs in the past is to set up a generic job "wrapper" process, which you can embed the parameter into. Like a header and footer for the job.
eg:-
p_header:= q'{
declare
v_parameter = 1;
begin
}';
p_footer:= q'{
exception
when others then
generic_error_process;
update job_log_table;
anything else;
end;
}';
|
|
|