Result Set Function - Access 2000

From: Gerald Saurugg <g.saurugg_at_pvm.at>
Date: Fri, 29 Nov 2002 10:05:41 +0100
Message-ID: <Z5GF9.3$b46.25168@news.cwxpoint.at>

Hallo

I have written in Oracle 9i a result set function like described on http://osi.oracle.com/~tkyte/ResultSets/index.html. This function is working very well in SQL*Plus.

Now I want to use this function in Access 2000. When I use it as Pass Through query, Access makes nothing, not even an error message. I tried it in VBA with DAO, Access shut down! I did not tried in ADO.

Does anyone know what I am making wrong? If this way does not work in Access, is there something else in Oracle what I can use like an UDF on MS SQL Server.

This is the package
CREATE OR REPLACE PACKAGE "BULLDOGALL"."TYPES" as

    type cursorType is ref cursor;
end;

the Function
CREATE OR REPLACE FUNCTION "BULLDOGALL"."QSEL_OVERVIEWGRADE"     (EvalFrom IN DATE,

 EvalTo     IN DATE,
 EvalP      IN VARCHAR2,
 PPOCRU     IN VARCHAR2,
 PPOPRO     IN VARCHAR2,
 Grade      IN VARCHAR2,
 EvalC      IN VARCHAR2

) RETURN types.cursorType
AS
  l_cursor types.cursorType;
BEGIN
open l_cursor for
select Main.DealID from main, PortfolioDetails, tbl_Physical, tbl_GradeList

        where (Main.DealID=PortfolioDetails.DealID) and (MAIN.DealID = tbl_Physical.DealID) and

(tbl_Physical.Grade =tbl_GradeList.GradeName(+)) and
(Main.DealDate between EvalFrom and EvalTo) and
(PortfolioDetails.Portfolio=EvalP) and

(Main.Producttype in (PPOCru,PPOPro)) and
(Main.Statusnumber>0) and

(tbl_Physical.Grade like Grade) and (Main.Company like EvalC);

RETURN l_cursor;
END; and the call in SQL*Plus:
select
qsel_OverviewGrade(to_Date('20020209','yyyyddmm'),to_Date('20021309','yyyydd mm'),'FILTER_plan september','Crude','Product','%','%') from dual;

thanks
Gerald Received on Fri Nov 29 2002 - 03:05:41 CST