running a PL/SQL program in the background
Gerhard Moeller
Hi,
at the moment I program an admin tool for my project including context
usable from webbrowsers. As some of the operations (e.g. optimize
indexes) are rather lengthy in execution, I would like to start PL/SQL
procedures in the background to give control instantely back to the
user. (in other words: those functions should be not executed modal)
The only idea I have at the moment is to create PL/SQL procedures on
the fly (with dbms_sql.parse) ans submit them as a job, executed
instantely.
The problem with this sulution is that I have to get rid of the jobs
and the procedures after execution. (There are just to many different
procedures to let them exist all the time.) Of course, this could be
done with some job that is executed regulary and checking what jobs
are done and can be safely destroyed. But this is rather a bad hack
than a clean solution.
So... ...is there any other way to let PL/SQL procedures be executed
in the background?
Thanks for any hint, Gerhard.
--
Dipl. Inform. Gerhard Möller -- Gerhard...@OFFIS.Uni-Oldenburg.DE
ICQ PIN: 2804938
OFFIS | | | | | | Tel.: 0441/9722-122
Escherweg 2 | | | | | | Sekr.: 0441/9722-113 oder -101
D-26121 Oldenburg |O|F|F|I|S| Fax: 0441/9722-102
Thomas Kyte
A copy of this was sent to Gerhard...@OFFIS.Uni-Oldenburg.de (Gerhard
Moeller)
(if that email address didn't require changing)
On Thu, 16 Apr 1998 09:15:24 GMT, you wrote:
>Hi,
>
>at the moment I program an admin tool for my project including context
>usable from webbrowsers. As some of the operations (e.g. optimize
>indexes) are rather lengthy in execution, I would like to start PL/SQL
>procedures in the background to give control instantely back to the
>user. (in other words: those functions should be not executed modal)
>
>The only idea I have at the moment is to create PL/SQL procedures on
>the fly (with dbms_sql.parse) ans submit them as a job, executed
>instantely.
>
>The problem with this sulution is that I have to get rid of the jobs
>and the procedures after execution. (There are just to many different
Why do you have to get rid of them? If you submit a job in this fashion:
declare
l_job number;
begin
dbms_job.submit( l_job, 'some_procedure_to_run_once;' );
commit;
end;
That will submit a job that runs once and will remove itself from the queue
after it successfully completes (since NEXT_DATE wasn't set). That sounds like
it is exactly what you want.
>procedures to let them exist all the time.) Of course, this could be
>done with some job that is executed regulary and checking what jobs
>are done and can be safely destroyed. But this is rather a bad hack
>than a clean solution.
>
>So... ...is there any other way to let PL/SQL procedures be executed
>in the background?
>
> Thanks for any hint, Gerhard.
Thomas Kyte
tk...@us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation
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.
Gerhard Moeller
tk...@us.oracle.com (Thomas Kyte) wrote:
[...]
>>The only idea I have at the moment is to create PL/SQL procedures on
>>the fly (with dbms_sql.parse) ans submit them as a job, executed
>>instantely.
>>
>>The problem with this sulution is that I have to get rid of the jobs
>>and the procedures after execution. (There are just to many different
>
>Why do you have to get rid of them? If you submit a job in this fashion:
>
>
>declare
> l_job number;
>begin
> dbms_job.submit( l_job, 'some_procedure_to_run_once;' );
> commit;
>end;
>
>
>That will submit a job that runs once and will remove itself from the queue
>after it successfully completes (since NEXT_DATE wasn't set). That sounds like
>it is exactly what you want.
It removes the job from the queue? I didn't know that! (I thought the
job will stay in the queue...) Great, that solves my problems indeed,
thank you very much, Gerhard.
Nicholas Whitehead
On Thu, 16 Apr 1998 09:15:24 GMT,
You can start a PL/SQL process(es) running in the background and have
them poll for jobs using DBMS_ALERT or DBMS_PIPE.
email me for more details.
Nicholas Whitehead
nwhit...@gt.com
>Hi,
>
>at the moment I program an admin tool for my project including context
>usable from webbrowsers. As some of the operations (e.g. optimize
>indexes) are rather lengthy in execution, I would like to start PL/SQL
>procedures in the background to give control instantely back to the
>user. (in other words: those functions should be not executed modal)
>
>The only idea I have at the moment is to create PL/SQL procedures on
>the fly (with dbms_sql.parse) ans submit them as a job, executed
>instantely.
>
>The problem with this sulution is that I have to get rid of the jobs
>and the procedures after execution. (There are just to many different
>procedures to let them exist all the time.) Of course, this could be
>done with some job that is executed regulary and checking what jobs
>are done and can be safely destroyed. But this is rather a bad hack
>than a clean solution.
>
>So... ...is there any other way to let PL/SQL procedures be executed
>in the background?
>
> Thanks for any hint, Gerhard.
Rod Corderey
Gerhard,
what platform are you running on?
If the server end is Unix, could you have database procedures executed
from a sqlplus session initiated on the Unix box using nohup ?
This would release the session straight away but leave the process
running in the background.
cheers
Rod
Lane Associates
Lane_As...@Compuserve.com
http://www.Lane-Associates.com
Rod Corderey
gerhard,
also, inspired by a message from Jim curry further up this list, you
could have a look at the DBMS_JOB database built-in available certainly
from 7.2 onwards [ I can't put my hands on the 7.1 manual at the
moment.]
cheers
Rod
Lane Associates
Lane_As...@compuserve.com