Returning a recordset from Oracle stored procedure to MS SQL Server 2000

• Dave Tennanr

  SSC Veteran

  Points: 276

  More actions

  February 2, 2006 at 4:56 pm

  #90533

  Hi,

  We are trying to return a recordset from an Oracle stored procedure to SQL Server 2000.  The Oracle DB is 8.1.7.4.

   

  We have established a linked server using Microsoft OLE DB Provider for Oracle.  From the SQL Query Analyzer, we have successfully used OpenQuery to return data from a select statement:

  Select * from OpenQuery(ora_test,’select * from fnd_user where rownum < 10’)

   

  We have an Oracle stored procedure that uses no input parameters and returns a ref cursor and works in Excel using the MSDAORA provider.

   

  When we try to use this same stored procedure in the SQL Query Analyzer using the following syntax:

  Select * from OpenQuery(ora_test,’{call ora.getdata({resultset 0, ls_data})}’)

  It returns the following error:

  Server: Msg 7357, Level 16, State 2, Line 3

  Could not process object ’{call ora.getdata({resultset 0, ls_data})}’.  The OLE DB provider ‘MADAORA’ indicates that the object has no columns.

   

  I have searched for a solution and tried many thing a few of which follow:

  Changed the registry setting for Allow InProcess

  Used Select * from OpenQuery(ora_test,’execute ora.getdata({resultset 0, ls_data})’)

  Changed the stored procedure in Oracle to reference “type” TABLE.  We had limited success with this approach and were able to return 1 row and 1 column.  However, when attempting to return more then one column it gave us an error:

  Server: Msg 7399, Level 16 State 1, Line 6

  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]

   

  Also note: We are using MDAC 2.8

   

  Is it possible to return a recordset from an Oracle stored procedure??  I have seen several articles that say it is but have been unable to do so.

   

  Any help, suggestions or recommendations would be greatly appreciated

   

  Thanks,

  Dave

• Suz Olliver-301921

  Valued Member

  Points: 65

  More actions

  February 13, 2006 at 5:00 pm

  #620554

  You will need to do this via VBA rather than using Microsoft Query.

  Look at the following web page for an example (this will also show you how to pass in parameters)

  http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q306530&

  Make sure that you click on Microsoft ActiveX Data Objects 2.x under tools/references before trying to use ADO otherwise it wont work.

  http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/75e0ede766ebde78/e526c56bea07ec0a%23e526c56bea07ec0a?sa=X&oi=groupsr&start=2&num=3

  Also has a good example

  Good luck - there is a real lack of clear and concise information on how to set up a DB connection out there.

   

   

• Dave Tennanr

  SSC Veteran

  Points: 276

  More actions

  February 13, 2006 at 5:26 pm

  #620556

  Thank you Suz for the responds,

   

  I was finally able to construct a process that does satisfy our needs.

   

  It involved several user-defined datatypes, some additional oracle functions and the use of the cast function as follows:

  Select * from OpenQuery(ora_test,’select * from table(cast(rs_table() as rs_tab))’)

   

  Thanks again for taking the time to respond.  Out of two different forums I submitted this question to, yours was the only one.

   

  Dave