Calling an Oracle Procedure via a DTS

[Jason Leiser]

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!

[Simon Hayes]

"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