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

Calling an Oracle Procedure via a DTS

P: n/a
Is there a way to call an Oracle Procedure using the MS OLD DB Provider
for Oracle object in a SQL Server 2000 DTS package? If it can't be done
this way, is there another way to retrieve data from an Oracle database
using an Oracle procedure to a SQL Server table? Also, can parameters
be passed into Oracle from SQL Server via a procedure? Our Oracle DBA
does NOT want to create views to enable SQL Server to access the data.

Examples would be great if possible. Thanks.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"Jason Leiser" <ja******@yahoo.com> wrote in message
news:40*********************@news.frii.net...
Is there a way to call an Oracle Procedure using the MS OLD DB Provider
for Oracle object in a SQL Server 2000 DTS package? If it can't be done
this way, is there another way to retrieve data from an Oracle database
using an Oracle procedure to a SQL Server table? Also, can parameters
be passed into Oracle from SQL Server via a procedure? Our Oracle DBA
does NOT want to create views to enable SQL Server to access the data.

Examples would be great if possible. Thanks.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


You should be able to use an ExecuteSQL task using the Oracle connection,
and pass the parameter values from global variables, although I must admit
that I haven't tried this myself.

As for calling a procedure directly from Oracle, you can create a linked
server and use OPENQUERY() to pass the stored procedure call to Oracle.
Unfortunately, OPENQUERY() doesn't support variables, so you would need to
build your query dynamically:

http://www.sommarskog.se/dynamic_sql.html#OPENQUERY

Simon
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.