Re: Retrieving complex resultset from oracle

I do something very similar to this using MS Word. When I execute the vba from a custom menu created in MS Word, a dialog box pops up and I enter a date. That date is used to create an array of data request for Oracle (I build the array in vba so that I only have to ask Oracle once). When the vba executes the code it use Oracle Object for OLE. (Can download from otn. ) The vba passes the data to a stored procedure (acutally a package) that gets the data, builds an array and sends the array back to MS Word. Then I have vba build a report on the returned data.

Bryan Baker

"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
>
>
>
> "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 Mon Oct 14 2002 - 02:49:13 CEST