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!"Jason Leiser" <jasonl22@.yahoo.com> wrote in message
news:403fa15f$0$202$75868355@.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