Re: ADO recordsets using a stored procedure

In article <7av62r$5an$1_at_hirame.wwa.com>, lkeeble_at_uswebcks.com says...
> I want to get an ADO recordset in an ASP script using a stored procedure.
> How do I do that with Oracle?
>
> With SQL Server/Transact-SQL I could write:
>
> create procedure myproc
> as
> select * from pubs
>
> and I could get the recordset using
>
> Set rs = DBCon.Execute(myproc)
>
> With Oracle it does not appear to be so simple
>
> Thanks,
>
> -Lou Keeble
>
>
>
>

I don't have a code listing handy but....

You can use PL/SQL Tables (basically an array in an Oracle package) in your stored procedure as OUT variables from the procedure.

What you do is declare in your package definition one such PL/SQL Table for each column that you want back. Use these PL/SQL Tables as OUT variables in the sp. You can use a cursor to move the SELECT results (within the stored procedure) into the PL/SQL Tables

  Table(i_row_count) := the_column_value_for_row_number_i_row_count;

You then call the sp from VB (you may need to use .CommandType= adCmdText type and not the adCmdStoredProc type to properly retrieve a resultset, even though you are calling a stored procedure. Check out Nick Snowdon's "Oracle Programming with Visual Basic" book, I believe he goes over the syntax.) The PL/SQL Tables become your resultset back in VB and you use your resultset.MoveNext,etc commands to move through the rows.

There are some caveats. You will probably need to pass a dimension size parameter to the sp to tell it what size to dimension the PL/SQL Tables. If this is too small, you won't have enough storage allocated for all the rows. I.e. the dimension must be big enough to store all the returned rows.

Hope this helps.

CMM Received on Fri Feb 26 1999 - 20:07:57 CST