spawning/launching a stored procedure as a seperate process? fork?, thread?

[jobs]

Im writing an SSIS script task using VB.NET. I'd like to be able to
initiate an oracle stored procedure call for a process that will run
for 8 hours, but I don't want my process to wait for it finish. In
fact, I'd like the ssis job to terminate after it launches it.

How can I do this using vb.net/ado.net?

How can I later electronically locate and track that process?

Thanks for any help or information.

 

[aaron.kempf]

get a real database; there isn't a stored procedure in the WORLD that
should take 8 hours

maybe if you weren't using an obsolete database like Oracle then you
wouldn't have an issue like this in the first place

-Aaron

 

[jobs]

get a real database; there isn't a stored procedure in the WORLD that
should take 8 hours

It's not the database .. it's bad code.. but thats not my assignment
and moving a shop entrenched in Oracle to another platform takes a
massive commitment all the way from the top. I've heard of good shops
taking years and doubling developers to make that kind of move. In a
perfect world companies pick a single standard (any standard even a bad
one) and all decisions are made with that in mind. I'm yet to work in
such a dream shop.

Anyway to address my question? Lets pretend the SP is perfect and only
runs for 30 mintues.

 

[Tim Patrick]

You can use Oracle's DBMS_JOBS library to schedule jobs for immediate background
execution. SSIS sometimes frowns on multi-statement commands, so depending
on how you start up the command, you might need to wrap up your DBMS_JOBS
logic in a stored procedure. The DBMS_JOBS.SUBMIT() function returns a job
number value. You can monitor this job number by querying the DBA_JOBS, USER_JOBS,
or DBA_JOBS_RUNNING views.

 

[jobs]

You can use Oracle's DBMS_JOBS library to schedule jobs for immediate background
execution. SSIS sometimes frowns on multi-statement commands, so depending
on how you start up the command, you might need to wrap up your DBMS_JOBS
logic in a stored procedure. The DBMS_JOBS.SUBMIT() function returns a job
number value. You can monitor this job number by querying the DBA_JOBS, USER_JOBS,
or DBA_JOBS_RUNNING views.

Sounds promising. would you know, is it possible to schedule an SP
through a generic job, or turn it into a job on the fly? or do jobs
have to be configured up front?

Thank you!

 

[aaron.kempf]

its' easy to shell_no_wait; you should look at www.freevbcode.com

it should be about 2 minutes to figure out the API call and call it
from .NET

shell something to start the execution of the sproc on the oracle side

-Aaron

 

[Tim Patrick]

DBMS_JOBS.SUBMIT is just an ordinary function, accepting the command to run
and the start time as strings, and returning the job number as an out-parameter.
I guess you could just call it directly from your code. I'm just don't know
SSIS that well; that's why I suggested the stored procedure. If you're going
to call it directly from some .NET code, you can just use .NET's standard
ways of communicating with stored procedures/parameters.