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.
The CAST instructs oracle to treat the return set of rows from the PL/SQL function (Emp_function() ) as a collection type.
Name Null? Type
-------------------------------- -------- ----------------
-- 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();
for crec in ( select emplid, name, deptid from emp) loop
emp_data(I) := EmpRecordType( crec.emplid,
crec.deptid ) ;
I := I+1;
select emplid, name, deptid
from TABLE ( cast( Emp_function() as EmpTableType ) )
EMPLID NAME DEPTID
--------- ------------------ ----------
1001 TAMIL 10
1002 VEERA 20
Is that are you looking for?
Originally Posted by tamilselvan
I am looking at a standard stored procedure, say:
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?
inout_RefCur IN OUT SYS_REFCURSOR
You might want to research MS-Access pass-through query feature.
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
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.
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.
Originally Posted by PAVB
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..
I havent realy used this but looks like it can do what you are asking for
Click Here to Expand Forum to Full Width