By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,089 Members | 1,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,089 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.

Jan 5 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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 wrote:
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.
Jan 5 '07 #2

P: n/a
aa*********@gmail.com wrote:
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. :)

Jan 5 '07 #3

P: n/a
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.

-----
Tim Patrick - www.timaki.com
Start-to-Finish Visual Basic 2005
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.

Jan 5 '07 #4

P: n/a
Tim Patrick wrote:
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!

Jan 5 '07 #5

P: n/a
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
jobs wrote:
Tim Patrick wrote:
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!
Jan 6 '07 #6

P: n/a
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.

-----
Tim Patrick - www.timaki.com
Start-to-Finish Visual Basic 2005
Tim Patrick wrote:
>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!

Jan 6 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.