From: jr8110@yahoo.com (Josh)
Newsgroups: comp.databases.oracle
Subject: Re: Retrieving complex resultset from oracle
Date: 10 Oct 2002 06:09:20 -0700
Organization: http://groups.google.com/
Lines: 60
Message-ID: <a1c10eb3.0210100509.15ca0491@posting.google.com>
References: <a1c10eb3.0210071306.2f47ba7b@posting.google.com> <Jjqo9.155565$q41.71798@news02.bloor.is.net.cable.rogers.com> <a1c10eb3.0210080956.60bf15d0@posting.google.com> <b_So9.130$vX.107@news04.bloor.is.net.cable.rogers.com>
NNTP-Posting-Host: 165.250.90.213
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1034255360 16873 127.0.0.1 (10 Oct 2002 13:09:20 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 10 Oct 2002 13:09:20 GMT


"iulian" <iulian_ilies@hotmail.com> wrote in message news:<b_So9.130$vX.107@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


Thanks for the USEFUL feedback. Funny you should mention that website
www.vbip.com i just found it, and found the examples in it quite
usefull.  Thanks for your example, it turns out i will be writing the
stored procedures to work with Crystal Reports, and luckily they have
a PDF explaining how to get Stored Procedures to work w/ Crystal,
something along the lines of ure example.  I appreciate the feedback

Thanks