Executing remote Oracle Stored Procedure from a SQL 2000 sever?

[John White]

I am having trouble executing a remote oracle stored . Every time I attempt
to exec the statement using

DECLARE @param1 varchar(10)
EXECUTE wallace..LEO.DO_NOT_SHIP @param1 output

I get this error

Server: Msg 7212, Level 17, State 1, Line 2
Could not execute procedure 'DO_NOT_SHIP' on remote server 'wallace'.
[OLE/DB provider returned message: One or more errors occurred during
processing of command.]
[OLE/DB provider returned message: Syntax error in {call...} ODBC Escape.]

I need to be able to do this within a SQL2000 stored procedure using
Transact-SQL.

I have configured the link sever ok and can query using
openquery(wallace,'select ........') I have set RPC and RPC OUT options in
enterprise manager, and I can successfully run the procedure in VB using
ADO?!?

Can anyone shed some light?

[Tibor Karaszi]

John,

I have a feeling that you can only execute procs on another SLQ Server that way. Below
quote is from BOL:

"Remote stored procedures are a legacy feature of Microsoft® SQL ServerT 2000. Their
functionality in Transact-SQL is limited to executing a stored procedure on a remote
SQL Server installation. "

Perhaps you can trick SQL Server using OPENQUERY, somehow?

--
Tibor Karaszi, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com

"John White" <john....@nextra.co.uk> wrote in message
news:#Dl1H4R3AHA.592@tkmsftngp02...

[Umachandar Jayachandran]

You have to use OPENQUERY to execute the SP. However, OPENQUERY expects
a result set from the pass-through query. Here is one sample that I posted
last year.

>>
You can do it using a Package with PL/SQL tables & the {resultset}
escape syntax of ODBC / OLE-DB provider for Oracle. Here is one example:

/* Run in Oracle */
CONNECT SCOTT/TIGER
/
CREATE PACKAGE WrapPack
AS
TYPE ReturnTbl IS TABLE OF NUMBER
/* The index is important, otherwise {resultset} doesn't work. */
INDEX BY BINARY_INTEGER;
PROCEDURE WrapPackSP
(
ReturnVal OUT ReturnTbl
);
END WrapPack;
/
CREATE PACKAGE BODY WrapPack
AS
PROCEDURE WrapPackSP
(
ReturnVal OUT ReturnTbl
)
IS
Begin
/* Call some other SP, just for demo */
/* Test; */
/* This is just a dummy return value. */
/*
This method can be used to return result sets from
Oracle SPs to SQL Server via ODBC / OLE-DB driver's
{resultset} escape syntax.
*/
ReturnVal( 1 ) := 0;
End WrapPackSP;
End WrapPack;
/
/* End of Oracle Routines */

/* Do this in SQL Server */
SELECT * FROM OPENQUERY( Oracle_Srvr ,
'{Call SCOTT.WrapPack.WrapPackSP( {resultset 1 , ReturnVal} )'
)

So this way, you can return a result set to T-SQL. The Oracle cursor
approach will work fine from a host language like C, C++ or Java etc. For
linked server setup in SQL Server, see BOL topics.

>> Can I put something like select * from sometable where the
>> 'Test;' message is?

No, you can't do this in Oracle. SELECT statement by itself should have
an IN clause. Returning result sets imho is a pain in Oracle & it depends on
the language/api you are using. SQL Server makes it so easy. AFAIK, you can
only return results via cursors, pl/sql tables, arrays as output parameters.

--> How am I going to return resultset based on a select
--> statement at this point?

Please check the PL/SQL docs on PL/SQL tables. Here is one sample using
my example & this returns the list of employees, ID from SCOTT.EMP table.
Please check the syntax - it has been a while since I coded considerably in
Oracle.

CREATE PACKAGE WrapPack
AS
TYPE EmpID IS TABLE OF NUMBER
/* The index is important, otherwise {resultset} doesn't work. */
INDEX BY BINARY_INTEGER;
TYPE EmpName IS TABLE OF VARCHAR2( 30 )
INDEX BY BINARY_INTEGER;
PROCEDURE WrapPackSP
(
NameLike IN varchar2
EID OUT EmpID
EName OUT EmpName
);
END WrapPack;
/
CREATE PACKAGE BODY WrapPack
AS
PROCEDURE WrapPackSP
(
NameLike IN varchar2
EID OUT EmpID
EName OUT EmpName
)
IS
EmpCount NUMBER DEFAULT 1;
CURSOR EmpCur IS
SELECT EmpNo , EName FROM SCOTT.EMP
WHERE EName LIKE NameLike;
BEGIN
FOR Emp IN EmpCur
LOOP
EmpID( EmpCount ) := Emp.EmpNo;
EmpName( EmpCount ) := Emp.EName;
EmpCount := EmpCount + 1;
END LOOP;
END WrapPackSP;
END WrapPack;
/

The above is one standard way to use PL/SQL tables. There may be other
ways of doing this. You call this from T-SQL using OPENQUERY like:

SELECT * FROM OPENQUERY( Oracle_Srvr ,
'{Call Scott.WrapPack.WrapPackSP( ''J%'', {resultset 20, eid, ename})}' )

This returns all employees whose name start with 'J%'. To pass
parameters using variables from T-SQL, the entire statement has to be formed
dynamically & executed. There are several threads about this.

>>

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

[John White]

Thanks for ideas, the problem I have is that the Oracle database and
therefore procedures are out of my control. They return parameters only!

Thanks Again

[Umachandar Jayachandran]

Then you have to do this using an external process. There are several
options:

1) Install "Oracle Objects for OLE" on the SQL Server machine. You can then
invoke them from T-SQL using the OLE automation system sps
2) Write an extended stored procedure to make the calls to Oracle. You can
then call it from T-SQL
3) Write an OLE automation object in VB or VC++ that can call Oracle. You
can then control it from within T-SQL
4) Or use xp_cmdshell to execute the SP using SQLPLUS.EXE. This has the
least control since you can't do distributed transactions etc.

I would go with option #1 first. It is easy to use & should work for
simple tasks. Next you can try #3. If you are adventurous, write an extended
SP in VC++ or C.

[John White]

To complete one task I have 4 oracle stored procedures to execute,

My Plan

I will have one sql server stored procedure, it will call 4 oracle stored
procedures via OLE system sp's independently. If the sql server sp is rolled
back or the client is disconected before completion will it roll back the
oracle sp's using oracle objects for ole.

Many Thanks

"Umachandar Jayachandran" <umach...@yahoo.com> wrote in message
news:uWSwRCW3AHA.1560@tkmsftngp03...

[Umachandar Jayachandran]

I do not know about the capabilities of "Oracle Objects". It has been a
while since I looked at it. But doing distributed transactions between
different processes like this is going to be difficult. You cannot get a
token for distributed transaction from T-SQL - it is done at a lower level.
So you cannot say pass the token to "Oracle" & ask it to enlist itself in
the same distributed transaction. In any case, check the object model of
"Oracle Objects" for the various methods.
Another option that I forgot to mention is to write a single wrapper
package that can execute the SP dynamically in Oracle. There are security
issues with this method but should be easy to handle. With this approach,
you can just start a distributed transaction from T-SQL & the MSDTC handles
every thing for you.

[John White]

Ok Thanks, I shall see want I can do!