| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: getting job number of queued job
A copy of this was sent to parrisg_at_aol.com (ParrisG) (if that email address didn't require changing) On 14 Mar 1998 15:11:36 GMT, you wrote:
>The reason I don't use sessionid currently is because it's 0.
>
>My approach is this: From a developer 2000 forms application, I want to submit
>a database procedure for execution and immediately return control back to the
>application. I need to use the dbms_job package to do this. The dbms_job
>pacakge is not available from the client side, so I created a database
>procedure that I can call from the client. This procedure has access to the
>dbms_job package - because it's on the server. I pass the name of the job I
>want executed as a parameter to this procedure. The procedure, in turn, passes
>the procedure name as a parameter to the dbms_job.submit procedure. When the
>running process executes the userenv function on the sessionid, it returns 0 -
>I presume this is because there is no controlling user session. I really only
>have the name of the process - in user_jobs - for tracking purposes. If there
>is more than one instance of this procedure, I can't tell which is which.
>
>The running process writes to an execution status table, which a user can
>access to determine how far along the job has come. It would be nice to query
>this table by job number. Unfortunately the procedure that populates the status
>table was written some time ago and is embedded in many programs - I would like
>to change this procedure so that it can obtain the job number from the
>environment, and not from a parameter - in which case I would have to change a
>lot of code.
Ok, here is how to 'get the job id into the environment'.
We will use a pl/sql package to be the environment. We will just use a package specification and put variables in it to represet our environmental variables. In this case, I just have:
create or replace package job_globals
as
my_job_id number;
end;
/
So, my environment will consist of "my_job_id".
Next, I will create a small log table (your execution status table) and a job to run. In my example, the job to run will simply put a row into the table saying "I am job XX" where XX will be the job id:
create table test_tbl ( msg varchar2(2000) );
create or replace procedure job_to_run
as
begin
insert into test_tbl values ( 'My Job Id is ' || job_globals.my_job_id);
commit;
end;
/
Now, the routine that submits the job will be responsible for 'initializing' the environment. When I submit the job to run, dbms_job will tell me the job number. I will submit the job once without an environment (as long as I don't commit, there is no chance the job will run before I can 'set' the environment). Immediately after getting the job id, I change the job in the queue using dbms_job.change. This change not only runs the procedure I want to, but prior to doing so will set the global package variable we created above. After I call dbms_job.change, I commit and the job will run.
create or replace procedure submit_a_job( p_job_name in varchar2 ) is
l_job_no number;
begin
dbms_job.submit( l_job_no, p_job_name || ';' );
dbms_job.change( l_job_no,
'begin
job_globals.my_job_id := ' || l_job_no || ';
' || p_job_name || ';
end;',
NULL, NULL );
commit;
end;
/
Hope this helps. (yes it works in a multi-user situation as well, each user will get their own package state -- we will each see our own job_ids and never anyone elses...)
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Mar 14 1998 - 00:00:00 CST
 |
 |