Re: Disconecting from Oracle without killing Oracle SPs

Thanks again. Got it working from VB and all tests are working perfectly. We are turning the procedure below into a more generic procedure so we can hit it from Cold Fusion, VB, Access, etc .

SQL for test procedure

create or replace procedure Xtest(data_ver varchar2, Session_id varchar2) is   jobid binary_integer := 0;
  test_string varchar2(500) := 'sp_get_thermal_data(' || '''' || data_ver || '''' || ',' || '''' || Session_id || '''' || ');';  begin
  dbms_job.submit (jobid, test_string, sysdate);  end Xtest;

'VB snippet Using DAO
Connect$ = "ODBC;DSN=blah-dev"
Set VXDB = Workspaces(0).OpenDatabase("", False, True, Connect$) nSQL$ = "{call xtest('" & data_ver & "','" & session_id & "')}" VXDB.Execute nSQL$, dbSQLPassThrough
VXDB.Close

Christopher Beck wrote:

> 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.
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Wed Dec 30 1998 - 12:37:45 CST