I need to return an array of records from a pl/sql stored procedure.  

Some thing like this:



Type UserRecs is record

(UserId number(4), UserName varchar2(30), 

UserType varchar2(30), Description varchar2(30),

Activeflag char(1),UpdatedBy varchar2(30), 

UpdateDate varchar2(30),CreatedBy varchar2(30), 

CreationDate varchar2(30));



Type TableList is table of UserRecs index by binary_integer;



PROCEDURE UserSearch

   (i_UName 		IN VARCHAR2,

    o_Users		OUT TableList);



How do I get o_Users into ADO?



I know how to return simple arrays and refCursors but in the case where 

these record sets get large that becomes cumbersome.  Refcursors are only 

good, if you do not intend to further manipulate the data before you 

return it to the calling program.



Has anyone out there done this?



Thanks,

-jason

Why don't you use .getRows()



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

----- Original Message -----

From: "Jason Lenardson" <jlenardson@h...>

To: "ASP Databases" <asp_databases@p...>

Sent: Thursday, June 21, 2001 4:00 PM

Subject: [asp_databases] Returning an array of records from Orale via ado

using OLEDB





: I need to return an array of records from a pl/sql stored procedure.

: Some thing like this:

:

: Type UserRecs is record

: (UserId number(4), UserName varchar2(30),

: UserType varchar2(30), Description varchar2(30),

: Activeflag char(1),UpdatedBy varchar2(30),

: UpdateDate varchar2(30),CreatedBy varchar2(30),

: CreationDate varchar2(30));

:

: Type TableList is table of UserRecs index by binary_integer;

:

: PROCEDURE UserSearch

:    (i_UName IN VARCHAR2,

:     o_Users OUT TableList);

:

: How do I get o_Users into ADO?

:

: I know how to return simple arrays and refCursors but in the case where

: these record sets get large that becomes cumbersome.  Refcursors are only

: good, if you do not intend to further manipulate the data before you

: return it to the calling program.

:

: Has anyone out there done this?

:

: Thanks,

: -jason

Ken:



I'm kind of new to ASP/ADO could you elaborate on .getRows()?



When using a RefCursor, I use the notation - 



varEmpNo = objRecordset.Fields("empno");



-  to assign the value from the record set(RefCursor) to a local variable.

Is this where I would use .getRows()?



I have found that returning a PL/SQL tables requires {Microsoft ODBC for 

Oracle} while RefCursors require OraOLEDB.Oracle - which of these drivers 

should I use?



I hate to ask for a road map, but this problem is getting under my 

skin...Could you post an example of using .getRows including the code you 

use to make the call to the database?



Thanks,

-jason