How do you execute an Oracle Stored Procedure from a SQL Server DTS package?

[robin via SQLMonster.com]

I've tried several different way to execute a oracle stored
procedure from a DTS package but to no avail.
I have a Linked Server setup which does bring back Oracle tables from the
server when I click on the Tables icon.

Here's my DTS statement:

exec omsd..OMS_TECO.SP_Callback_Update_Pkg(116);

omsd is the linked server
oms_teco is the owner of the oracle stored procedure
SP_Callback_Update_Pkg is the oracle stored procedure
(116) is the parameter passed to the oracle stored procedure

I put the above exec statement in a DTS Execute SQL Task using a Connection
that I tried using several OLE and ODBC Data Sources. I can't seem to find
the right combination.

Please Help!!!!!!!!

--
Message posted via http://www.sqlmonster.com

[Simon Hayes]

"robin via SQLMonster.com" <fo***@SQLMonster.com> wrote in message
news:2b******************************@SQLMonster.c om...

I've tried several different way to execute a oracle stored
procedure from a DTS package but to no avail.
I have a Linked Server setup which does bring back Oracle tables from the
server when I click on the Tables icon.

Here's my DTS statement:

exec omsd..OMS_TECO.SP_Callback_Update_Pkg(116);

omsd is the linked server
oms_teco is the owner of the oracle stored procedure
SP_Callback_Update_Pkg is the oracle stored procedure
(116) is the parameter passed to the oracle stored procedure

I put the above exec statement in a DTS Execute SQL Task using a
Connection
that I tried using several OLE and ODBC Data Sources. I can't seem to
find
the right combination.

Please Help!!!!!!!!

--
Message posted via http://www.sqlmonster.com

Why not connect directly to the Oracle server using an Oracle connection
object in the DTS package? That might be easier than going through a linked
server.

I've never tried using Oracle linked servers myself, but check out this post
(and the rest of the thread):

http://groups.google.ch/groups?hl=en...%40tkmsftngp05

Simon