Home » SQL & PL/SQL » SQL & PL/SQL » get job_id in running job (dbms_job)
get job_id in running job (dbms_job) [message #265794] Fri, 07 September 2007 07:30 Go to next message
cordess
Messages: 5
Registered: April 2005
Junior Member
I use the Job mechanism to start pl/sql procedures.
Now i want those procedures to make Outputs to some Tables. The output must contain the job_id of the running job.
I don't want the procedure make the output(with jobid), wich starts the job! I need a way to get the job_id in the running Job.

Do anybody know a way to get this job_id?

My english is not the best, i hope you understand, what I'm looking for.

Cordess
Re: get job_id in running job (dbms_job) [message #265803 is a reply to message #265794] Fri, 07 September 2007 07:58 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
When you think about the program parts here .....

  Calling Procedure
        |
        v
 Configured dbms_job
        |
        v
  "Job" Procedure


The parts that know the job ID are the calling procedure and the configured dbms_job. The "Job" Procedure could by called by itself, too, without a job being involved.

So one way to do what you want might be to add the job ID as IN parameter to the job procedure, and then hardcode the ID in the call in dbms_job to match the actual job id.
Re: get job_id in running job (dbms_job) [message #265815 is a reply to message #265794] Fri, 07 September 2007 08:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
SQL> create table t (job_id number default null, job_date date default sysdate);

Table created.

SQL> create or replace procedure p (job_id number)
  2  is
  3    pragma autonomous_transaction;
  4  begin
  5    insert into t (job_id) values (job_id);
  6    commit;
  7  end;
  8  /

Procedure created.

SQL> var jobno number;
SQL> exec dbms_job.submit(:jobno,'p(:job);')

PL/SQL procedure successfully completed.

SQL> print jobno
     JOBNO
----------
        21

SQL> exec dbms_job.run(:jobno);

PL/SQL procedure successfully completed.

SQL> select * from t;
    JOB_ID JOB_DATE
---------- -------------------
        21 07/09/2007 15:23:48

1 row selected.

Regards
Michel
Re: get job_id in running job (dbms_job) [message #265822 is a reply to message #265794] Fri, 07 September 2007 08:46 Go to previous messageGo to next message
cordess
Messages: 5
Registered: April 2005
Junior Member
I see you are german. So write the answer first in english and then in german.

Okay,...i'm not sure if i understand it right.

What you meant is to get the Job id in the what-part of job submit. Until now i thought this could not work. Smile
Very good idea, it works fine with following samplecode:

Miniprocedure starting the job:
declare
jobno binary_integer;
begin
dbms_job.submit(jobno,'job_test(job);');
myoutput.put_line('jobtest1','Erg:'||to_char(jobno));
commit;
end;

The minijobprocedure:
procedure job_test(job_id in binary_integer)
is
begin
myoutput.put_line('jobtest2',to_char(job_id));
end job_test;

But if you meant a other way than please tell it.


The same text in german:

Ich bin mir nicht Sicher ob ich sie richtig verstanden habe.
Sie meinen ich soll die Jobid im whatpart der submitprozedur einfach mitübergeben. Bis heute hab ich es für mich einfach ausgeschlossen, dachte das geht nicht(wie engstirnig von mir Smile ).
Tolle idee, läuft mit dem oben genannten Code.

Falls sie aber was anderes meinten, dann interessiert mich das trotzdem.

Thank you, you rescued my day Very Happy.
Das lass ich mal englisch stehen Smile.
Re: get job_id in running job (dbms_job) [message #265828 is a reply to message #265822] Fri, 07 September 2007 09:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What didn't you understand in the example I posted?

By the way, if you want to post in German, there is a forum for this.
Please keep this one in english.

Regards
Michel
Re: get job_id in running job (dbms_job) [message #265837 is a reply to message #265794] Fri, 07 September 2007 09:32 Go to previous messageGo to next message
cordess
Messages: 5
Registered: April 2005
Junior Member
Oh, please dont missunderstand me, i wrote my post before i read yours.
I see that your suggestion is very similar to my new code(thanks to the other user).
The german text contains complete the same meanings like in the english text.
This is the reason why i wrote it first in english.

My english is not the best. I saw the other user was a german, so i thought it is a goog idea to avoid missunderstandings.

Thats all,...thanks to all.

Cordess
Re: get job_id in running job (dbms_job) [message #265864 is a reply to message #265837] Fri, 07 September 2007 12:39 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hello,

Yes, the way you understood it was the way I meant it.

Glad to see it works. I didn't have time to actually do a test-case, since I had to run to catch a meeting. Wink

Regards, und viele Grüße

Thomas

Re: get job_id in running job (dbms_job) [message #265902 is a reply to message #265864] Fri, 07 September 2007 15:07 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
if sys_context('userenv', 'BG_JOB_ID') is not null then
  l_env := 'Procedure executed by DBMS_JOB id ='||sys_context('userenv', 'BG_JOB_ID');
else 
  l_env := 'Procedure executed in foreground mode (not DBMS_JOB).';
end if;
Re: get job_id in running job (dbms_job) [message #265904 is a reply to message #265902] Fri, 07 September 2007 15:11 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Good one!
Every day I learn something new.

Regards
Michel
Previous Topic: How to close cursor
Next Topic: Need oracle utilities and pl/sql packags for converting .CSV files as ZIP file and send as a mail.
Goto Forum:
  


Current Time: Sat Feb 15 18:08:14 CST 2025