ADO - how to run proc returning a record

B

BEDE

I got to run queries and stored procedures on Oracle DB well-enough using ADO
How can I run a stored procedure that returns a recordset (ref cursor in Oracle parlance)
Could I find one so kind to give me a sample?
 
B

BEDE

OK. I got to check MSDN about the provider (MSDAORA.1) that comes with MSOffice, ant it specifically says that it can't handle procedures returning ref cursors. I read that Oracle OLEDB Provider can do that
Still, I'll stick to MSDAORA.1 for it works well for most of the things and it comes with MSOffice
 
S

Srao

You would have to create the procedure and package on the Oracle D
create or replace package Pack i
type Create_Sales_Lead_Record_type is ref cursor return create_sales_lead_v1%rowtype
--Selected_Sales_Lead Create_Sales_Lead_Record_type
-- Function and procedure implementation

procedure Proc_Get_Sales_Lead(Sales_Lead_ID in Number , Selected_Sales_Lead out Create_Sales_Lead_Record_Type)


end Pack

On .net side
oraTrane = new OracleConnection("user id=;data source=;password=")
Object My_DBNull
My_DBNull = Convert.DBNull
OracleDataReader SalesLeadDetails
OracleCommand GetSalesDetails = new OracleCommand()
GetSalesDetails.Connection = oraTrane
GetSalesDetails.CommandType = CommandType.StoredProcedure
GetSalesDetails.CommandText= "trane_pack.proc_get_sales_lead"
GetSalesDetails.Parameters.Add( new OracleParameter("sales_lead_id", OracleType.Number,0,ParameterDirection.Input,false,0,0," ", DataRowVersion.Default, My_DBNull))
GetSalesDetails.Parameters.Add( new OracleParameter("Selected_Sales_Lead" , OracleType.Cursor ,2000, ParameterDirection.Output ,false ,0,0," ",DataRowVersion.Default, My_DBNull))
GetSalesDetails.Parameters[0].Value = 1
oraTrane.Open()
SalesLeadDetails = GetSalesDetails.ExecuteReader()


----- BEDE wrote: ----

I got to run queries and stored procedures on Oracle DB well-enough using ADO
How can I run a stored procedure that returns a recordset (ref cursor in Oracle parlance)
Could I find one so kind to give me a sample?
 
Top