Re: How to return a result set from a stored proc?

In article <391B0F8F.91D650B1_at_ntsource.com>, fhubeny_at_ntsource.com says...
> There is some information in the MSDN Library about calling Oracle stored
> procedures with ASP. You might try searching there for some examples. As I
> recall, these examples actually worked when I tried it some time ago.
>
> In brief, however, the way involves creating a plsql index-by table for each
> column that you want to send back. These tables are viewed as arrays in odbc
> and can be selected as you would rows in a sql query.
>
> The following might be an example of a packaged procedure call that will work
> with odbc:
>
> create or replace package testit is
> type char_array is table of varchar2(30) index by binary_integer;
> procedure getdata(tsp in varchar2,mydata out char_array);
> end;
> /
> show errors
>
> create or replace package body testit is
If you are using ODBC, then Oracle technet has a good example for it. You need Oracle's ODBC.

If you are using OleDB/ADO and you are using Oracle 8i, then try Oracle's OleDB for Oracle 8i. It is still in beta.

> procedure getdata(tsp in varchar2, mydata out char_array)
> is
> cursor c is
> select table_name from user_tables where tablespace_name = upper(tsp);
> begin
> open c;
> fetch c bulk collect into mydata;
> close c;
> end;
> end;
> /
> show errors
>
> You probably know better than I do how to process this in asp, but the details
> for this are also in the MSDN documents.
>
> Frank Hubeny
>
>
> Patrick Joyal wrote:
>
> > What Oracle or ODBC version are you using?
> >
> > I'm using Oracle 8.1.5 w/ the ODBC that came with it, and it says
> > wrong number or type of argument,
> >
> > so how the heck do you do that?
> >
> > >> Hi,
> > >>
> > >> The subject basically says it all. I switched to Oracle from SQL-
Received on Thu May 11 2000 - 00:00:00 CDT