| Pradip 2006-12-01, 7:13 pm |
| I am calling an Oracle procedure from SQL Server.
I am using Oracle 10g, SQL Server 2000, MDAC 2.81.1117.0.
I am getting the below error.
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Provider does not support PL/SQL stored
procedures/functions with RECORD or TABLE arguments.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Pre
pare
returned 0x80004005: ].
The sample syntax of the query at SQL Server end
---------------------------------------------------------------------
SELECT * FROM OPENQUERY(OracleLink
edSvr ,
'{CALL user.pack.OracleProc(
''text1'', ''text2'', ''text3'', {resultset 1, ReturnVal})}' )
--------------------------------------------------------------------
Sample syntax of procedure at oracle end
-------------------------------------------------------------------
CREATE PACKAGE Pack
AS
TYPE ReturnTbl IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
PROCEDURE OracleProc
(
ReturnVal OUT ReturnTbl
);
END Pack;
/
CREATE PACKAGE BODY Pack
AS
PROCEDURE OracleProc
(
Param1 IN varchar2,
Param2 IN varchar2,
Param3 IN varchar2,
Param4 IN varchar2,
ReturnVal OUT ReturnTbl
)
IS
Begin
SELECT * BULK COLLECT INTO ReturnVal FROM oracleTable;
End OracleProc;
End Pack;
---------------------------------------------------------------------
I really appreciate your help.
Thanks.
|