469,588 Members | 2,766 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,588 developers. It's quick & easy.

Calling an Oracle Procedure via a DTS

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
1 6042

"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.

Similar topics

1 post views Thread by David Pylatuk | last post: by
reply views Thread by stephane.traumat | last post: by
1 post views Thread by Sudhish K V | last post: by
1 post views Thread by burtonl | last post: by
1 post views Thread by Anthony Smith | last post: by
4 posts views Thread by raghuvendra | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.