HELP....OleDb & Oracle StoredProcedure which return a CURSOR set.

[ʹÃûÑï]

MEMO:just use oledbcommand


//---Program-----
OleDbCommand myCMD = new
OleDbCommand("BIND_Person_LIST.BIND_Person",myConnection);
myCMD.CommandType = CommandType.StoredProcedure;
myCMD.Parameters.Add("pOffice_id",OleDbType.Integer).Value=1;
OleDbParameter result=myCMD.Parameters.Add("result",OleDbType.???[there is
no OleDbType.cursor]);//Oraclecommand can use cursor type and OledbType
use which type.
result.Direction=ParameterDirection.Output;
myConnection.Open();

OleDbDataReader myReader= myCMD.ExecuteReader();
DropDownList1.DataSource=myReader;
DropDownList1.DataTextField="PERSON_NAME";
DropDownList1.DataValueField="PERSON_ID";
DropDownList1.DataBind();
myConnection.Close();




//------this is my StoredProcedure
CREATE OR REPLACE PACKAGE "TZH"."BIND_PERSON_LIST" as
TYPE t_cursor IS REF CURSOR ;
Procedure BIND_Person (pOFFICE_ID IN NUMBER, io_cursor IN OUT t_cursor);
end BIND_Person_LIST;

CREATE OR REPLACE PACKAGE BODY "TZH"."BIND_PERSON_LIST" AS
Procedure BIND_Person (pOFFICE_ID IN NUMBER,io_cursor IN OUT t_cursor)
IS
v_cursor t_cursor;
BEGIN
IF pOFFICE_ID <> 0
THEN
OPEN v_cursor FOR
SELECT person_name,person_id
FROM person
WHERE office_id=pOFFICE_ID;
END IF;
io_cursor := v_cursor;
END BIND_Person;
END BIND_Person_LIST;



//reference
http://otn.oracle.com/global/cn/sample_code/tech/windows/ole_db/index.html
but i cannot understand Oracle'program.faint

http://support.microsoft.com/default.aspx?scid=kb;en-us;309361
OleDbCommand myCMD = new OleDbCommand
("{call curspkg_join.open_join_cursor1(?, {resultset 0, io_cursor})}",
//when i try it .wrong here

 

[David Browne]

MEMO:just use oledbcommand


//---Program-----
OleDbCommand myCMD = new
OleDbCommand("BIND_Person_LIST.BIND_Person",myConnection);
myCMD.CommandType = CommandType.StoredProcedure;
myCMD.Parameters.Add("pOffice_id",OleDbType.Integer).Value=1;
OleDbParameter result=myCMD.Parameters.Add("result",OleDbType.???[there is
no OleDbType.cursor]);//Oraclecommand can use cursor type and OledbType
use which type.
result.Direction=ParameterDirection.Output;
myConnection.Open();

You need to use a different provider. The OleDb provider doesn't support
REF CURSOR parameters.

Here's a link to Oracle's Data Provider for .NET (ODP.NET)

There are many examples there for using REF CURSOR parameters.
http://otn.oracle.com/tech/windows/odpnet/index.html

David