| 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: Disconecting from Oracle without killing Oracle SPs
On Mon, 28 Dec 1998 13:41:23 -0600, Default User <user_at_jsc.nasa.gov>
wrote:
>Looking for ideas. What I want to do is kick off Stored procedures, then
>disconnect.
>Should be simple except Oracle kills the process. Is there a way to tell
>Oracle to
>keep the process running even after disconnection?
Yes.
You could look at scheduling the procedure to run as a database job. This way your process could connect, initialize the job and disconnect. The database will then run the job sometime later.
You need to set up the database to handle jobs. In the init.ora file, the following parameters need to be set.
job_queue_interval = 60 job_queue_keep_connections = TRUE job_queue_processes = 1
This tells the database to use one process to handle the jobs scheduled and to check every 60 seconds to see if there is a job to run.
An example of how to schedule a job would look like...
create or replace
procedure my_proc_background(
p_param1 varchar2,
p_param2 varchar2 ) is
--
l_job number;
begin
dbms_job.submit(
job => l_job,
what => 'my_proc( ' || p_param1 || ', ' || p_param2 || ' );' );
end proc_background;
/
This will schedule a job and tell the database to run the procedure my_proc with the parameters p_param1 and p_param2. Since it did not specify when to run it, it will run it the next time the job_queue is checked ( within the next 60 seconds if your init.ora is set up the above parameters. )
Check chapter 7 of the Oracle8 Server Administrators Guide for more information on the dbms_job package.
hope this helps.
chris.
>
> I've been trying to develop a means for a currently running
>application to batch SP's
>against already created and populated tables. Problem is that as soon as
>the ODBC
>connection is broken all SP's spawned by that connection are killed.
>
> Platform is
>Client VB using ODBC (Oracle73 Ver 2.5) on Win 95 machines
>Server Oracle 7.3 on Solaris box.
>
>Current product connects to Oracle runs the SPs and then processes the
>data
>when it returns. This can take several hours on some of the longer
>queries. Instead
>client wants the ability to run proccess and turn off the machine. Then
>process
>the results the next day or lator that day or whenever it is done.
>
>This would be simple if breaking the ODBC connection didn't kill the SP.
>We
>tried running the SP from a CGI script instead of directly sending it to
>SQL but
>this is untolerably slow since it has to be run as a back ground
>proccess and Unix
>assigns it a very low priority. Next try was Nohup but Nohup and SQLPlus
>are
>not being agreeable.
>
>Any ideas?
>
>Thanks
>Dan
>daniel.p.smith1_at_jsc.nasa.gov
>dan.p.smith_at_usa.net
--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.
 |
 |