Re: Retrieving complex resultset from oracle

"iulian" <iulian_ilies_at_hotmail.com> wrote in message news:<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

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 Received on Thu Oct 10 2002 - 15:09:20 CEST