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

I finally found the problem...

In all the examples, they omitted the Cursor parameter when calling the procedure, wich was half truth, (for ODBC, at least). You have to replace the cursor parameter with ?, and it has to be a packaged procedure.

{call myPackage.getData (?)}

It finally worked, BUT I don't want to use ODBC, so I tried using MS OLE DB provider but I got the message "This provider doesn't support PL/SQL with RECORD or REF CURSOR" So I'm now downloading Oracle OLE Db provider and we'll see what happens

Frank Hubeny wrote in message <391B0F8F.91D650B1_at_ntsource.com>...
>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