How do you call a stored procedure that returns a cursor?

[burak]

Hello,

 

I have an oracle stored procedue that returns a cursor

 

PROCEDURE GetNumTitleUniqueFamilies

( word in varchar, src in varchar,onetfam in varchar,

wordrows out types.word_cursor_type) as

begin

open wordrows for

select distinct substr(ONETCODE, 1, 2) from PROCESSEDWORDS

where WORD = word

and SOURCE = src

and ( TITLEMAXOCCURENCE is not null and TITLEMAXOCCURENCE > 0 )

and substr(ONETCODE,1,2) != onetfam;

end GetNumTitleUniqueFamilies;

 

I found a page that shows how to call a stored procedure that returns a cursor

 

http://support.microsoft.com/default.aspx?scid=kb%3BEN-US%3B308073

 

but I was wondering how I would add an output parameter if I want to call it in the following manner

 

strSelect = "GetNumTitleUniqueFamilies"

cmdselect = New OleDbCommand(strSelect, objConnSkills2)

cmdselect.CommandType = CommandType.StoredProcedure

cmdselect.Parameters.Add("word", OleDbType.VarChar).Value = strWord

cmdselect.Parameters("word").Direction = ParameterDirection.Input

cmdselect.Parameters.Add("src", OleDbType.VarChar).Value = strSource

cmdselect.Parameters("src").Direction = ParameterDirection.Input

cmdselect.Parameters.Add("onetfam", OleDbType.VarChar).Value = strOnetFamily

cmdselect.Parameters("onetfam").Direction = ParameterDirection.Input

 

since there is no OleDbType.Cursor type.

 

Thank you,

 

Burak

[Nerseus]

I don't think you can do what you're trying. The data returned from Oracle is meant to be read through a datareader because it's a readonly/forward only type of return, often called a firehose query. That type of query wouldn't work with a command object as an output param, to the best of my knowledge.

 

Why do you want the data as an output param? What's your endgoal for using the data you're getting back?

 

-ner