Home » SQL & PL/SQL » SQL & PL/SQL » dbms_scheduler and off-line reports
dbms_scheduler and off-line reports [message #300246] Thu, 14 February 2008 13:04 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #300252 is a reply to message #300250] Thu, 14 February 2008 14:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SYS_CONTEXT

Regards
Michel
Re: dbms_scheduler and off-line reports [message #300254 is a reply to message #300252] Thu, 14 February 2008 14:15 Go to previous messageGo to next message
Justin Schoeman
Messages: 8
Registered: February 2008
Junior Member
Michel Cadot wrote on Thu, 14 February 2008 14:08
SYS_CONTEXT

Regards
Michel



Cool. I wonder what SYS_CONTEXT('bg_job_id') will give me? If this is the job name directly, that would be even better!

Thanks for all the help everybody.

Justin
Re: dbms_scheduler and off-line reports [message #300255 is a reply to message #300246] Thu, 14 February 2008 14:59 Go to previous message
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;
}';
Previous Topic: daily count for last two years.
Next Topic: End of file communication
Goto Forum:
  


Current Time: Sat Nov 09 16:09:01 CST 2024