Returning procedure recordset to Access/Excel

[chrisrlong]

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.

Thanks

- Chris

[tamilselvan]

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.



SQL>desc emp
Name Null? Type
-------------------------------- -------- ----------------
EMPLID NUMBER
NAME VARCHAR2(30)
DEPTID NUMBER
-- create user defined object type
create or replace type EmpRecordType as object
( emplid number, name varchar2(30) , deptid number
)
/
-- create object table
create or replace type EmpTableType as table of EmpRecordType;
/
-- create a function
create or replace function emp_function return EmpTableType
as I int := 1;
emp_data empTableType := empTabletype();
begin
for crec in ( select emplid, name, deptid from emp) loop
emp_data.extend;
emp_data(I) := EmpRecordType( crec.emplid,
crec.name,
crec.deptid ) ;
I := I+1;
end loop;
return Emp_data;
end;
/

select emplid, name, deptid
from TABLE ( cast( Emp_function() as EmpTableType ) )
/
EMPLID NAME DEPTID
--------- ------------------ ----------
1001 TAMIL 10
1002 VEERA 20
1003 RAJIV


The CAST instructs oracle to treat the return set of rows from the PL/SQL function (Emp_function() ) as a collection type.

Is that are you looking for?

[chrisrlong]

Is that are you looking for?

mmm....no :)

I am looking at a standard stored procedure, say:

PROCEDURE GetEmp
(
inout_RefCur IN OUT SYS_REFCURSOR
)
IS
BEGIN

OPEN
inout_RefCur
FOR
SELECT
EMPLID ,
NAME ,
DEPTID
FROM
EMP ;

END GetEmp;


What I want to know is how I can create a query in MS Access (through the front-end) that uses this proc and returns the resultset?

Thanks,

- Chris

[PAVB]

You might want to research MS-Access pass-through query feature.

[chrisrlong]

You might want to research MS-Access pass-through query feature.
Yeah, I can use pass-throughs for DML procs with no problem. The problem is with returning a resultset. I need to convert Oracle's goofy Ref Cursor into something MS accepts as a resultset.

[cjard]

we've never found a way to do it, 5 years into use of access as a frontend to oracle, we still use VBA ADODB code to do it (I think.. not my dept)

if youre reporting to excel, you can have someone who is .NET capable make a dll that will quite simply turn a stored procedure call into an Excel file..

[hrishy]

Hi

I havent realy used this but looks like it can do what you are asking for

http://www.orafaq.com/faqoo4o.htm

regards
Hrishy