Re: Retrieving complex resultset from oracle

From: iulian <iulian_ilies_at_hotmail.com>
Date: Wed, 09 Oct 2002 09:51:03 GMT
Message-ID: <b_So9.130$vX.107_at_news04.bloor.is.net.cable.rogers.com>

I don't think you can to base a MS Access report on a stored procedure in Oracle, even if it retuns a REF Cursor.
Instead, why don't you link to the tables in Oracle datasource and make the parameterize query in MS Access?

On the other hand you can use ref cursor returned by a stored procedure in code.
Somethink like connect with ADO to an Oracle data source, execute the stored procedure, scan the recordset and do whatever you want with the data.

Anyway here's an example for your situation. (I'm not sure how much do I have to charge you for it :))

You should run these scripts in schema where your tables resides (make sure you have enough privileges)

CREATE OR REPLACE PACKAGE Cur_Types AS

    TYPE aRecTyp IS RECORD (
        id           NUMBER(10),
        amount       NUMBER(10,2),
        description  VARCHAR2(50)

    );
    TYPE aCurTyp IS REF CURSOR RETURN aRecTyp; END;
/

CREATE OR REPLACE PROCEDURE Get_Cursor (

    cv OUT Cur_Types.aCurTyp, -- the ref cursor must be OUT parameter     dt IN NUMBER
)
AS
BEGIN

• here you open your parameterized cursor; OPEN cv FOR SELECT a.id, a.amount, b.description FROM table1 a, table2 b WHERE a.id = b.id AND a.date = dt END; /

Also take a look at this link
http://www.vbip.com/books/1861003927/chapter_contents.asp, it's about ASP, ADO and stored procedure with REF Cursors.

Good luck!

iulian

"Josh" <jr8110_at_yahoo.com> wrote in message news:a1c10eb3.0210080956.60bf15d0_at_posting.google.com...
> Its rather difficult for me to elaborate more, the best i can do is
> give some sorta pseudo code related to my problem, the query would be
> something along the lines of this
>
> Lets say i have 1 parameter, _at_report_date of type DATE, i'd like to do
> something along the following:
>
> SELECT a.id, a.amount, b.description
> FROM table1 a, table2 b
> WHERE a.id = b.id
> AND a.date = _at_report_date
>
> And whatever results from that is what i'd like to use in either MS
> Access or Crystal Reports. I just need some sorta skeletal example of
> how to get the resultset out, and how to grab it in my app.
>
> Thanks
Received on Wed Oct 09 2002 - 11:51:03 CEST