How to return a RefCursor from Oracle function?

I think you are missing the sqlCom.ExecuteNonQuery().

I think you are missing the sqlCom.ExecuteNonQuery(); also, instead of running the select func_test(7) from dual; lets switch it to run the function and pass in the param OracleConnection oracleCon = new OracleConnection(ConfigurationManager. ConnectionStrings"MyConnectionString". ConnectionString); // Set the command string anonymous_block = "begin " + " :refcursor1 := func_test(7) ;" + "end;"; //fill in your function and variables via the above example OracleCommand sqlCom= con.CreateCommand(); sqlCom.

CommandText = anonymous_block; // Bind sqlCom.Parameters. Add("refcursor1", OracleDbType. RefCursor); sqlCom.

Parameters0. Direction = ParameterDirection. ReturnValue; try { // Execute command; Have the parameters populated sqlCom.ExecuteNonQuery(); // Create the OracleDataAdapter OracleDataAdapter da = new OracleDataAdapter(sqlCom); // Populate a DataSet with refcursor1.

DataSet ds = new DataSet(); da. Fill(ds, "refcursor1", (OracleRefCursor)(sqlCom. Parameters"refcursor1".

Value)); // Print out the field count the REF Cursor Console. WriteLine("Field count: " + ds. Tables"refcursor1".Columns.

Count); } catch (Exception e) { Console. WriteLine("Error: {0}", e. Message); } finally { // Dispose OracleCommand object cmd.Dispose(); // Close and Dispose OracleConnection object con.Close(); con.Dispose();} this is based on the example ODP that can be found @ %ora_home%\Client_1\ODP.NET\samples\RefCursor\Sample5.

Csproj If you want to avoid (for better or worst! ) the custom built param collection for each proc/function call you can get around that by utilizing anonymous blocks in your code, I have ammended (once again untested!) the code above to reflect this technique. Here is a nice blog (from none other than Mark Williams) showing this technique.

http://oradim.blogspot.com/2007/04/odpnet-tip-anonymous-plsql-and.html.

This could work, but as this will be used in a RESTful web service, I'm trying to avoid the explicit adding of input parameters (i.e. SqlCom.Parameters. Add("id", OracleDbType.

Int32,ParameterDirection. Input). Value = 7;) so I can easily build a SQL statement on the fly.

– Guddie Nov 8 '10 at 20:09 @Guddie, you can use anonymous blocks to give it the 'generic' feel you are going for. I have changed the above example to reflect this – Harrison Nov 8 '10 at 20:40 YES! You win!

Exactly what I was looking for! – Guddie Nov 8 '10 at 20:43.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.

Related Questions