If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Returning procedure recordset to Access/Excel

I have recently been called in on a project to create a database abstraction layer. Moving all the SQL into packages for the applciation was easy, but now I've come to their hodge-podge of reporting methods. This includes Excel and Access. What I would like to do is move this SQL into packages as well. The problem is how to make these products recognize the resultset returned from a stored procedure without using code. I don't think it can be done, but I've been wrong once or twice before .

So, is there any way in, say, an Access Query (using only the front end - not in code) to create a pass-through query that calls a procedure with a Ref Cursor and have it somehow be recognized as returning a recordset?

This *used* to be possible using Microsoft's ODBC driver for Oracle and a bunch of array parameters, etc. But that was de-supported by MS back in 8i. (and was ugly-as-sin to boot) I need similar magic, but in 9i.

An object is a user defined type in 8i/9i/10g.
There are 2 types:
1 Object types - Analogous to a record or structure in a language such as PL/SQL or C, or a class in an object-oriented language such as C++ or Java. Consists of one or more attributes and a set of methods used to manipulate those attributes.

2 Collections - Analogous to arrays, sets, bags, etc., from other languages such as C/C++, Java, or Smalltalk. Collections contain zero or more elements of a given datatype.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.