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

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: 2000/05/11
Message-ID: <391B0F8F.91D650B1@ntsource.com>#1/1

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

    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-
> >> Server. In SQL-Server I could return a result set to the calling
> >> program from a stored procedure just by executing a sql statement. The
> >> same doesn't work in Oracle though. Following is my guess, which is not
> >> working:
> >>
> >> create or replace procedure (id in number) as
> >> begin
> >> select a, b, c from test where testId = id;
> >> end;
> >>
> >> Any hint would be very much appreciated.
> >>
> >> --
> >> Alexander Jerusalem
> >> VKN
> >> ajeru_at_gmx.net
> >>
> >>
> >> Sent via Deja.com http://www.deja.com/
> >> Before you buy.
> >
> >
Received on Thu May 11 2000 - 00:00:00 CDT