execute Oracle SP
JASON
executing an Oracle Stored Procedure from sql server?
I have a SP that truncates a table in oracle
and inserts data from another table.
when I issue the
EXECUTE LINKED_DB..USERNAME.STOREDPROC
I get the following error:
Server: Msg 7212, Level 17, State 1, Line 1
Could not execute procedure 'TRUNCATENEWRXCLAIM' on remote
server 'FMH'.
[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.]
Any info would be great!
Thanks,
Jason
BP Margolin
Try called the Oracle procedure via OPENQUERY, and remember that OPENQUERY
is simply a pass-through mechanism, so you need to pass Oracle the Oracle
syntax to execute the procedure.
Documentation on OPENQUERY can be found in the SQL Server Books Online.
-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
"JASON" <jbr...@f-m-h.com> wrote in message
news:0cb001c2329f$a7a36f50$35ef2ecf@TKMSFTNGXA11...
Umachandar Jayachandran
the Oracle SP has to return some result set. Also getting the result set
from the driver to SQL Server is difficult. I have posted several messages
before on this topic. I will post one that shows how to do these. You can
search for more examples from me in the server or programming newsgroups.
-------
It will work as long. And you can call a procedure in the package using
the OPENQUERY pass-through mechanism. You have to however return some dummy
result from the Oracle SP. I posted this technique several times in the last
2 years here. Here is one example from my recent post:
>>
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. )