Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Development (2000)  Openquery to Oracle with an OUTPUT parameter

Author  Topic  malkouna Starting Member 1 Post Posted - 2007-11-26 : 22:40:38 Hi there, I am struggling to get the following to work. I have a linked server on SQL2000 that is talking just fine to an Oracle (10g maybe) server. I want to call a function on the Oracle server, but unfortunately am not able to create any packages on the Oracle server otherwise I would wrap the call in something that OPENQUERY recognises as other posts have shown. This works from SQL*Plus: declare v_error varchar2(1000); begin examiner_recruitment subject_allocations(v_error); end; I've tried a multitude of things on my SQL server but don't know how to define the parameter correctly. I have tried: SELECT * FROM OPENQUERY(IBIS, '{Call examiner_recruitment.subject_allocations({resultset 1, v_error})}') but get the error: [OLE/DB provider returned message: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'SUBJECT_ALLOCATIONS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored ] OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandText::Execute returned 0x80040e14]. Msg 7320, Level 16, State 2, Line 6 Could not execute query against OLE DB provider 'MSDAORA'. I've even tried the very naive: SELECT * FROM OPENQUERY(IBIS, '{declare v_error varchar2(1000); begin examiner_recruitment subject_allocations(v_error); end;}') and get the error: OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14]. Msg 7321, Level 16, State 2, Line 9 An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'. I don't even really care about the value of v_error as I'm going to throw it away anyway, but I'm stuck. Any help much appreciated. Regards, Anthony.