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.

Thanks

- Chris

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.

Code:

---


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?

Quote:

---

Originally Posted by tamilselvan

Is that are you looking for?

---

mmm....no :)

I am looking at a standard stored procedure, say:

Code:

---

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

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

Quote:

---

Originally Posted by PAVB

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.

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..

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